ODBC Action Plugin for AutoPlay Media Studio

[Ulrich]

Hello all,

the ODBC action plugin I was developing the last days is finally ready. I have set up a web page for it here, I ask you to check it for future updates.

Here is the current announcement:

“We developed an actions plugin compatible with Indigo Rose's AutoPlay Media Studio, which will allow the developer to connect to any database with a properly working ODBC driver. These connections can be made through DSNs in the ODBC Administration panel, or may be DSN-less, allowing the developer to establish connections to databases without modifying the user's system, just by passing the driver name and connection parameters manually.

Once successfully connected to the database of his choice (which may be Firebird SQL, Microsoft Access, Oracle, Sybase, SQL Server, or MySQL, just to name a few), the developer is able to perform any queries, and extract the resulting data (recordsets and column names), to be filled into tables, data grids, etc.

You can download the ODBC Actions Plugin from this address.

Plugin documentation is included as HTML in the archive and accessible inside the development environment, but it can also be seen here.

A demonstration project is available as well to help get you started. There is a lot of error checking included in the sample, but once you understood how to use the plugin you should be able to slim down the code substantially.

The plugin should work in trial mode for 7 days, after this it will need a unlock code to continue to work in the development environment.”

On the web page you'll find a button to make your payment. I'll need some info to be able to create your personal unlock code, but it is very straigthforward and shouldn't give any trouble.

Ulrich

[Dermot]

This is a welcome addition to AMS. Great job.:yes: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)...

[RizlaUK]

Perfect!

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

[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

[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.

[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!

[RizlaUK]

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

http://www.indigorose.com/forums/showthread.php?t=25426&highlight=ODBC

[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.

[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.

[RizlaUK]

yup, here you go

http://www.indigorose.com/forums/showthread.php?t=25755&highlight=odbc+mysql+functions

[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]

did i miss something ?

[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.

[dhart]

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