ODBC Action Plugin for AutoPlay Media Studio

[Ulrich]

David,

you did not show your code here. I know from the PM you sent me that you are trying to use ODBC.ExecuteSQL() to execute not a single statement, but the whole script at once. As I wrote, I am suspecting that this is where your mistake is, because the expected parameter is a single statement, see MSDN reference for ExecuteSQL(). If somebody knows otherwise, please join the discussion.

In my opinion, you may prefer to use the command line interface of SQL Server to run the script, instead of feeding it command-by-command through the ODBC interface. Normally you would use ODBC to perform queries and updates... Can't you use File.Run("sqlcmd.exe", ...) to run the script?

Ulrich

[boku]

Having difficulties! EMERGENCY

I have successfully managed to connect to the database in question. However when I run the .ExecuteSQL() with the following code, I get an error. Can anyone shed any light? This does work in SQL server, tested 100%.

CREATE DATABASE [ihaccts] ON  PRIMARY 
( NAME = N'ihaccts', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ihaccts.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'ihaccts_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ihaccts_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ihaccts', @new_cmptlevel=90
GO
ALTER DATABASE [ihaccts] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [ihaccts] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [ihaccts] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [ihaccts] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [ihaccts] SET ARITHABORT OFF 
GO
ALTER DATABASE [ihaccts] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [ihaccts] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [ihaccts] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [ihaccts] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [ihaccts] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [ihaccts] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [ihaccts] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [ihaccts] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [ihaccts] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [ihaccts] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [ihaccts] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [ihaccts] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [ihaccts] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [ihaccts] SET  READ_WRITE 
GO
ALTER DATABASE [ihaccts] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [ihaccts] SET  MULTI_USER 
GO
ALTER DATABASE [ihaccts] SET PAGE_VERIFY CHECKSUM  
GO
USE [ihaccts]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [ihaccts] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

USE [ihaccts]
GO
/****** Object:  Table [dbo].[pl_accounts]    Script Date: 04/01/2009 16:09:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pl_accounts](
	[SUCODE] [varchar](10) NOT NULL,
	[SUNAME] [varchar](30) NULL,
	[SUBALANCE] [float] NULL,
	[SU_CREDIT_LIMIT] [float] NULL,
	[SUSORT] [varchar](8) NULL,
	[SU_TAX_CODE] [smallint] NULL,
	[SUADDRESS] [varchar](120) NULL,
	[SU_ADDRESS_USER1] [varchar](60) NULL,
	[SU_ADDRESS_USER2] [varchar](60) NULL,
	[SUPOSTCODE] [varchar](20) NULL,
	[SUCONTACT] [varchar](30) NULL,
	[SUPHONE] [varchar](20) NULL,
	[SUFAX] [varchar](20) NULL,
	[SU_ON_STOP] [smallint] NULL,
	[SU_DUE_DAYS] [char](1) NULL,
	[SUCOUNTRY] [varchar](4) NULL,
	[SUCURRENCYCODE] [varchar](4) NULL,
	[INTERCOMPANY] [varchar](10) NULL,
 CONSTRAINT [PK_pl_accounts] PRIMARY KEY CLUSTERED 
(
	[SUCODE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[nl_accounts]    Script Date: 04/01/2009 16:09:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[nl_accounts](
	[NCOPY_ACCCODE] [char](16) NOT NULL,
	[NCC] [char](4) NULL,
	[NDEPT] [char](3) NULL,
	[NNAME] [char](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[sl_accounts]    Script Date: 04/01/2009 16:09:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sl_accounts](
	[CUCODE] [varchar](10) NOT NULL,
	[CUNAME] [varchar](30) NULL,
	[CUBALANCE] [float] NULL,
	[CU_CREDIT_LIMIT] [float] NULL,
	[CUSORT] [varchar](8) NULL,
	[CU_TAX_CODE] [smallint] NULL,
	[CUADDRESS] [varchar](120) NULL,
	[CU_ADDRESS_USER1] [varchar](60) NULL,
	[CU_ADDRESS_USER2] [varchar](60) NULL,
	[CUPOSTCODE] [varchar](20) NULL,
	[CUCONTACT] [varchar](30) NULL,
	[CUPHONE] [varchar](20) NULL,
	[CUFAX] [varchar](20) NULL,
	[CU_EMAIL] [varchar](60) NULL,
	[CU_ON_STOP] [smallint] NULL,
	[CU_DUE_DAYS] [char](1) NULL,
	[CUPHONE2] [varchar](20) NULL,
	[CU_VAT_REG_NO] [char](10) NULL,
	[PAYMENT_TERMS] [char](10) NULL,
	[Intercompany] [varchar](10) NULL,
	[CUCURRENCYCODE] [varchar](4) NULL,
	[CUCOUNTRY] [varchar](4) NULL,
 CONSTRAINT [PK_sl_accounts] PRIMARY KEY CLUSTERED 
(
	[CUCODE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

insert into [ihaccts].[dbo].[sl_accounts] (cucode) values('CASH')

[dhart]

figured it out.. 2 dim array.. I wasn't checking each dim count. Table.Count(Query.Data) will return the correct count.

[dhart]

Table.Count is always 1

I tried ShadowUK's code and it does fill the array/table with data from the query. I tested it with this code knowing that I had at least 2 rows and 2 columns returned from the query:

Dialog.Message("debug", Query.Data[1][ODBC.GetColumnName(1)].. " " ..Query.Data[1][ODBC.GetColumnName(2)]);

Dialog.Message("debug", Query.Data[2][ODBC.GetColumnName(1)].. " " ..Query.Data[2][ODBC.GetColumnName(2)]);

However, immediately after I test the count of the array/table and I get 1 returned every time. I guess I am missing something here. Why can I reference the 2nd row of the array/table but only have a count = 1?

Dialog.Message("debug",Table.Count(Query))

Again, I am new to AutoPlay. Sorry if this is newbie.

[RizlaUK]

did i miss something ?

[ShadowUK]

Please don't link to that thread. I want to delete it, But I'll post the code I used anyway.

MySQL.Server = "";
MySQL.Database = "";
MySQL.Username = "";
MySQL.Password = ""; -- not
MySQL.Port = "";

function MySQL.Query(sQuery)
	local result = ODBC.OpenDirectConnection("Driver={MySQL ODBC 3.51 Driver};Server="..MySQL.Server..";Port="..MySQL.Port..";Option=131072;Stmt=;Database="..MySQL.Database..";Uid="..MySQL.Username..";Pwd="..MySQL.Password);
	if (result ~= 0) then
		error = Application.GetLastError();
		if (Dialog.Message("MySQL ODBC Plugin", "It appears that you are missing the MySQL ODBC Connector Driver, This is required to connect to the  server.\r\n\r\nPress Yes to download and install the MySQL ODBC Driver.", MB_YESNO, MB_ICONSTOP, MB_DEFBUTTON1) == IDYES) then
			StatusDlg.Show(0, false);
			StatusDlg.SetTitle("Installing dependencies..");
			HTTP.Download("http://admin.jokerice.co.uk/box/mysql-connector-odbc-3.51.27-win32.msi", _TempFolder.."\\mysql.msi", MODE_BINARY, 20, 80, nil, nil, nil);
			File.Run("msiexec", "/package \"".._TempFolder.."\\mysql.msi\" /passive", "", SW_SHOWNORMAL, true);
			Dialog.Message("MySQL", "The driver has been successfully installed.\r\n\r\nWhen you restart GMan you will be able to login.", MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1)
			Window.Close(Application.GetWndHandle(), CLOSEWND_TERMINATE);
		end
		Window.Close(Application.GetWndHandle(), CLOSEWND_TERMINATE);
	else
		ODBC.ExecuteSQL(sQuery);
		ODBC.CloseQuery();
		ODBC.CloseConnection();
		return 0;
	end
end

function MySQL.QueryToTable(sQuery, bSilent)
	local Query = {Data = {}};
	local result = ODBC.OpenDirectConnection("Driver={MySQL ODBC 3.51 Driver};Server="..MySQL.Server..";Port="..MySQL.Port..";Option=131072;Stmt=;Database="..MySQL.Database..";Uid="..MySQL.Username..";Pwd="..MySQL.Password);
	if (result ~= 0) then
		error = Application.GetLastError();
		if (Dialog.Message("MySQL ODBC Plugin", "It appears that you are missing the MySQL ODBC Connector Driver, This is required to connect to the  server.\r\n\r\nPress Yes to download and install the MySQL ODBC Driver.", MB_YESNO, MB_ICONSTOP, MB_DEFBUTTON1) == IDYES) then
			StatusDlg.Show(0, false);
			StatusDlg.SetTitle("Installing dependencies..");
			HTTP.Download("http://admin.jokerice.co.uk/box/mysql-connector-odbc-3.51.27-win32.msi", _TempFolder.."\\mysql.msi", MODE_BINARY, 20, 80, nil, nil, nil);
			File.Run("msiexec", "/package \"".._TempFolder.."\\mysql.msi\" /passive", "", SW_SHOWNORMAL, true);
			Window.Close(Application.GetWndHandle(), CLOSEWND_TERMINATE);
		end
		Window.Close(Application.GetWndHandle(), CLOSEWND_TERMINATE);
	else
		result = ODBC.OpenQuery(sQuery);
		if (result ~= 0) then
			error = Application.GetLastError();
			Dialog.Message("ODBC Plugin", "Query failed (" .. error .. ")", MB_OK, MB_ICONSTOP);
			Application.Exit(0);
			result = ODBC.CloseConnection();
			if (result ~= 0) then
				error = Application.GetLastError();
				Dialog.Message("ODBC Plugin", "Could not close connection (" .. error ..")", MB_OK, MB_ICONSTOP);	
			end
		else
			numCols = ODBC.GetNumCols();
			if (numCols > 0) then
				local row = 1;
				
				while (not ODBC.IsEOF()) do
					for column = 0, numCols-1 do
						data = ODBC.GetColumn(column);
						if not Query.Data[row] then Query.Data[row] = {}; end
						Query.Data[row][ODBC.GetColumnName(column)] = data;
					end
					row = row + 1;
					ODBC.NextRow();
				end
			end
			
			ODBC.CloseQuery();
			ODBC.CloseConnection();
		end
	end
	
	return Query;
end

[RizlaUK]

yup, here you go

http://www.indigorose.com/forums/sho...ysql+functions

[dhart]

QueryToTable

I am new to AutoPlay and I have been asked to convert a SQLite project to SQLServer. The code uses SQLite.QueryToTable a lot. Has someone already written the code for the ODBC plugin to convert a recordset to table(array)? Please let me know.

[rexzooly]

also, while useing ODBC from AMS, you might find this usefull

http://www.indigorose.com/forums/sho...highlight=ODBC

Yes i looked at this also but problem is i am lost lol i am able to get date and i
now been able to split the data out of the tables in to the own fields
But i think i have gone really half arsed way of doing it lol

anyone got just a simple demo that could kick my way cos my brain hurts i
really should stick to to simple things lol i hate been dislexic but @ the same time its whom i am lol.

I was lost with the ODBC_EX also cos i am just 2 simple lol



Thanks for the replys.

[RizlaUK]

also, while useing ODBC from AMS, you might find this usefull

http://www.indigorose.com/forums/sho...highlight=ODBC

[RizlaUK]

the plugin is only a gateway to your database, you will need to learm some SQL to add/remove/edit database fields

INSERT WHERE SOMEVALUE=VALUE
INSERT OR REPLACE WHERE SOMEVALUE=VALUE
DELETE WHERE SOMEVALUE=VALUE

Tip: search the forum for TIGG TV and follow the SQLite lessions found on tigg's site, its where i started!

[rexzooly]

i been looking @ this and i can easly make a demo to view one thing from my data base but i don't know how to add, remove or edit can anyone help me please i am bloody lost on this lol if this can do what i want then the site i am
doing some work for will happy get the it i have to show them it working and i
have bit off more then i can.

[ShadowUK]

Bump because this is a very good plugin, I recently downloaded the trial for this. And it is defiently the best option for databases. I've ported box to MySQL and this was exactly what I needed after finding out that LuaSQL had some Vista compatability problems.

Thumbs up, You've got my purchase. :yes

[RizlaUK]

Perfect!

i dont know where i was when this post was made, i missed it, but this is just what i need :yes

[bule]

Yes, really nice. Now, if we only had some database aware
components in APMS that would really rock.

With the current situation. how can we compete with something
like this (database-aware components ready to link with
tables or datasets linked to database connections)...