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 (http://www.mindquake.com.br/ams-odbc.php), 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 (http://www.mindquake.com.br/files/ams/ODBCSetup.exe).

Plugin documentation is included as HTML in the archive and accessible inside the development environment, but it can also be seen here (http://www.mindquake.com.br/files/ams/ODBC_Help.htm).

A demonstration project is available (http://www.mindquake.com.br/files/ams/ODBC_Connector.apz) 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) (http://www.devexpress.com/Products/VCL/ExQuantumGrid/)...

[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/showthread.php?t=25426&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

:huh:huh

Thanks for the replys.

[dhart]

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]

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.

[boku]

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')

[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 (http://msdn.microsoft.com/en-us/library/67cze9b7(VS.80).aspx) 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]

For those interested, my whole code is as follows. Please help me, I'm desperate. Ulrich, I'm not sure how use the cmd line function correctly. I've also uploaded the SQL script I was using at work.

BlankPassword = ""
ServerName = Registry.GetValue(HKEY_LOCAL_MACHINE, "\\SYSTEM\\ControlSet001\\Control\\ComputerName\\Ac tiveComputerName", "Computername", true);

SQLServerDB = ODBC.OpenDirectConnection("Driver={SQL Server};" ..
"Server=" .. ServerName .. "\\SQLEXPRESS" ..
";Database=" .. "master" ..
";Uid=sa;Pwd=");

Dialog.Message("Notice", SQLServerDB, MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1);

if (SQLServerDB == 0) then

success = ODBC.ExecuteSQL("CREATE DATABASE [ihaccts] ON PRIMARY \r\n" ..
"( NAME = N'ihaccts', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\ihaccts.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) \r\n" ..
"LOG ON \r\n" ..
"( NAME = N'ihaccts_log', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\ihaccts_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) \r\n" ..
"GO \r\n" ..
"EXEC dbo.sp_dbcmptlevel @dbname=N'ihaccts', @new_cmptlevel=90 \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET ANSI_NULL_DEFAULT OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET ANSI_NULLS OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET ANSI_PADDING OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET ANSI_WARNINGS OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET ARITHABORT OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET AUTO_CLOSE OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET AUTO_CREATE_STATISTICS ON \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET AUTO_SHRINK OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET AUTO_UPDATE_STATISTICS ON \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET CURSOR_CLOSE_ON_COMMIT OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET CURSOR_DEFAULT GLOBAL \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET CONCAT_NULL_YIELDS_NULL OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET NUMERIC_ROUNDABORT OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET QUOTED_IDENTIFIER OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET RECURSIVE_TRIGGERS OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET AUTO_UPDATE_STATISTICS_ASYNC OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET DATE_CORRELATION_OPTIMIZATION OFF \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET PARAMETERIZATION SIMPLE \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET READ_WRITE \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET RECOVERY SIMPLE \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET MULTI_USER \r\n" ..
"GO \r\n" ..
"ALTER DATABASE [ihaccts] SET PAGE_VERIFY CHECKSUM \r\n" ..
"GO \r\n" ..
"USE [ihaccts] \r\n" ..
"GO \r\n" ..
"IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [ihaccts] MODIFY FILEGROUP [PRIMARY] DEFAULT \r\n" ..
"GO \r\n" ..
"USE [ihaccts] \r\n" ..
"GO \r\n" ..
"SET ANSI_NULLS ON \r\n" ..
"GO \r\n" ..
"SET QUOTED_IDENTIFIER ON \r\n" ..
"GO \r\n" ..
"SET ANSI_PADDING ON \r\n" ..
"GO \r\n" ..
"CREATE TABLE [dbo].[pl_accounts]( \r\n" ..
"[SUCODE] [varchar](10) NOT NULL, \r\n" ..
"[SUNAME] [varchar](30) NULL, \r\n" ..
"[SUBALANCE] [float] NULL, \r\n" ..
"[SU_CREDIT_LIMIT] [float] NULL, \r\n" ..
"[SUSORT] [varchar](8) NULL, \r\n" ..
"[SU_TAX_CODE] [smallint] NULL, \r\n" ..
"[SUADDRESS] [varchar](120) NULL, \r\n" ..
"[SU_ADDRESS_USER1] [varchar](60) NULL, \r\n" ..
"[SU_ADDRESS_USER2] [varchar](60) NULL, \r\n" ..
"[SUPOSTCODE] [varchar](20) NULL, \r\n" ..
"[SUCONTACT] [varchar](30) NULL, \r\n" ..
"[SUPHONE] [varchar](20) NULL, \r\n" ..
"[SUFAX] [varchar](20) NULL, \r\n" ..
"[SU_ON_STOP] [smallint] NULL, \r\n" ..
"[SU_DUE_DAYS] [char](1) NULL, \r\n" ..
"[SUCOUNTRY] [varchar](4) NULL, \r\n" ..
"[SUCURRENCYCODE] [varchar](4) NULL, \r\n" ..
"[INTERCOMPANY] [varchar](10) NULL, \r\n" ..
"CONSTRAINT [PK_pl_accounts] PRIMARY KEY CLUSTERED \r\n" ..
"( \r\n" ..
"[SUCODE] ASC \r\n" ..
")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] \r\n" ..
") ON [PRIMARY] \r\n" ..
"GO \r\n" ..
"SET ANSI_PADDING OFF \r\n" ..
"GO \r\n" ..
"SET ANSI_NULLS ON \r\n" ..
"GO \r\n" ..
"SET QUOTED_IDENTIFIER ON \r\n" ..
"GO \r\n" ..
"SET ANSI_PADDING ON \r\n" ..
"GO \r\n" ..
"CREATE TABLE [dbo].[nl_accounts]( \r\n" ..
"[NCOPY_ACCCODE] [char](16) NOT NULL, \r\n" ..
"[NCC] [char](4) NULL, \r\n" ..
"[NDEPT] [char](3) NULL, \r\n" ..
"[NNAME] [char](30) NULL \r\n" ..
") ON [PRIMARY] \r\n" ..
"GO \r\n" ..
"SET ANSI_PADDING OFF \r\n" ..
"GO \r\n" ..
"SET ANSI_NULLS ON \r\n" ..
"GO \r\n" ..
"SET QUOTED_IDENTIFIER ON \r\n" ..
"GO \r\n" ..
"SET ANSI_PADDING ON \r\n" ..
"GO \r\n" ..
"CREATE TABLE [dbo].[sl_accounts]( \r\n" ..
"[CUCODE] [varchar](10) NOT NULL, \r\n" ..
"[CUNAME] [varchar](30) NULL, \r\n" ..
"[CUBALANCE] [float] NULL, \r\n" ..
"[CU_CREDIT_LIMIT] [float] NULL, \r\n" ..
"[CUSORT] [varchar](8) NULL, \r\n" ..
"[CU_TAX_CODE] [smallint] NULL, \r\n" ..
"[CUADDRESS] [varchar](120) NULL, \r\n" ..
"[CU_ADDRESS_USER1] [varchar](60) NULL, \r\n" ..
"[CU_ADDRESS_USER2] [varchar](60) NULL, \r\n" ..
"[CUPOSTCODE] [varchar](20) NULL, \r\n" ..
"[CUCONTACT] [varchar](30) NULL, \r\n" ..
"[CUPHONE] [varchar](20) NULL, \r\n" ..
"[CUFAX] [varchar](20) NULL, \r\n" ..
"[CU_EMAIL] [varchar](60) NULL, \r\n" ..
"[CU_ON_STOP] [smallint] NULL, \r\n" ..
"[CU_DUE_DAYS] [char](1) NULL, \r\n" ..
"[CUPHONE2] [varchar](20) NULL, \r\n" ..
"[CU_VAT_REG_NO] [char](10) NULL, \r\n" ..
"[PAYMENT_TERMS] [char](10) NULL, \r\n" ..
"[Intercompany] [varchar](10) NULL, \r\n" ..
"[CUCURRENCYCODE] [varchar](4) NULL, \r\n" ..
"[CUCOUNTRY] [varchar](4) NULL, \r\n" ..
" CONSTRAINT [PK_sl_accounts] PRIMARY KEY CLUSTERED \r\n" ..
"( \r\n" ..
"[CUCODE] ASC \r\n" ..
")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] \r\n" ..
") ON [PRIMARY] \r\n" ..
"GO \r\n" ..
"SET ANSI_PADDING OFF \r\n" ..
"GO \r\n" ..
"insert into [ihaccts].[dbo].[sl_accounts] (cucode) values('CASH')");
end

Dialog.Message("Notice", success, MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1);

[Ulrich]

Hello,

if you perform a search in these forums, you will find mentions to sqlcmd.exe, which might help you. I personally have never used SQL Server, so I am able to help very little here. But the command line syntax does not look too complicated (http://msdn.microsoft.com/en-us/library/ms162773.aspx). Looks like you have to run sqlcmd.exe -i filename.sql ...

Ulrich

[boku]

Excellent, thank you! worked a treat. Managed to get project finished in time!

[Stefan_M]

It would be nice if the 'EXEC' command will work too.

e.g.
To get all databases from an installed SQL-server instance
result = ODBC.OpenQuery('EXEC sp_helpdb');


p.s. I know, I can use
result = ODBC.OpenQuery('SELECT * FROM dbo.sysdatabases');
for this query.

Stefan

[boku]

It would be nice if the 'EXEC' command will work too.

e.g.
To get all databases from an installed SQL-server instance
result = ODBC.OpenQuery('EXEC sp_helpdb');


p.s. I know, I can use
result = ODBC.OpenQuery('SELECT * FROM dbo.sysdatabases');
for this query.

Stefan

I totally agree, it would extremely useful to be able to execute stored procedures!

Lets hope for an update soon

[Ulrich]

Hello,

support for stored procedures is now available for registered users of this plugin. The current build at this moment (1.0.3.1) features two new actions:

ODBC.ExecuteDirect() - support for the execution of stored procedures, while the results are returned as a table;

ODBC.QueryToTable() - support for the execution of queries, while the whole resultset is returned as a table.


Here is a sample how this works:

-- Execute a stored procedure and display the resultset in a grid
result = ODBC.OpenDSNConnection("mydsn", "username", "password");
if (result ~= 0) then
error = Application.GetLastError();
Dialog.Message("ODBC Actions Plugin", "Connection failed: (" .. error .. ")", MB_OK, MB_ICONSTOP);
else
resultset = ODBC.ExecuteDirect("{ CALL \"dbo.test\" }");
if (resultset == nil) then
error = Application.GetLastError();
Dialog.Message("ODBC Actions Plugin", "Query failed (" .. error .. ")", MB_OK, MB_ICONSTOP);
else
numRows = Table.Count(resultset);
for row, record in resultset do
for col, field in record do
if (col == 1) then
if (row == 1) then
-- set the column count of the grid only once
numCols = Table.Count(record);
Grid.SetColumnCount("Grid1", numCols);
else
-- new record starting
Grid.InsertRow("Grid1", -1, false);
end
end
Grid.SetCellText("Grid1", row-1, col-1, field, false);
end
end
Grid.Refresh("Grid1");
end
result = ODBC.CloseConnection();
if (result ~= 0) then
error = Application.GetLastError();
Dialog.Message("ODBC Plugin", "Could not close connection (" .. error ..")", MB_OK, MB_ICONSTOP);
end
end


The exact syntax for the call to the stored procedure depends on the database that is used. Using SQL Server, I was able to get perfect results with

rsTable = ODBC.ExecuteDirect("{ CALL \"master.dbo.sp_who\" }"); and
rsTable = ODBC.ExecuteDirect("EXEC sp_who");

while an instance of Firebird SQL expected a command in this format:

rsTable = ODBC.ExecuteDirect("EXECUTE PROCEDURE \"Procedure01\"");

These new functions are not fully implemented in the evaluation version.

Ulrich

[boku]

Hi Ulrich,

I have run into a snag (at last :p)

I cannot get this SELECT statement to work, I think it is due to joins.

ODBC.OpenQuery("SELECT \"contracts\".\"CONTNO\", \"contitems\".\"ITEMNO\", \"contitems\".\"ITEMDESC\", \"contitems\".\"QTY\", \"contracts\".\"ACCT\", \"contracts\".\"ACCTNAME\", \"contracts\".\"DELNAME\", \"contitems\".\"TYPE\", \"contitems\".\"STATUS\", "
.."\"contitems\".\"DELDATE\", \"contitems\".\"HIREDATE\", \"stock\".\"ITEMNO\" FROM (\""..DataDB.."\".\"dbo\".\"Contracts\" \"contracts\" LEFT OUTER JOIN \""..DataDB.."\".\"dbo\".\"ContItems\" \"contitems\" ON "
.."\"contracts\".\"CONTNO\"=\"contitems\".\"CONTNO\") LEFT OUTER JOIN \""..DataDB.."\".\"dbo\".\"Stock\" \"stock\" ON \"contitems\".\"ITEMNO\"=\"stock\".\"ITEMNO\" WHERE (\"contitems\".\"DELDATE\"={ts '"..Today.." 00:00:00'}) ORDER BY \"contracts\".\"CONTNO\"");


Can you confirm whether or not the presence of joins are an issue?

Kind regards.

[Ulrich]

The command used as argument is passed as received by the plugin to the ODBC driver. And the ODBC driver by itself probably does not parse the command in any way, so a SELECT with JOINs should work.
If you get an error 12004 response, this would mean that the query did execute, but the server returned an (syntax?) error. On the other hand, an error 12002 would mean that a database exception occurred, and the query probably was not run.
I would log the query string you composed, and run the same command manually against the database, perhaps using MS Query, with the same ODBC connection, as this might give a hint about what is happening.

Ulrich

[mwreyf1]

What is wrong here?

In the documentation you state:

Change Log: 1.0.3.2
QueryToTable() now returns one full record of the recordset in the evaluation build. Full results will be returned in the registered version only.

I just downloaded 1.0.4.2 (as shown when compiling AMS7 for testing) and it is telling me that that feature is not available in the Eval.

Can you please explain what I am doing wrong.

Also on your website you state that "The plugin will work FULLY for 7 days.

Also the BLOB function is not working in the EVAL.

There is conflicting information on your site.

Thanks.

[Dermot]

Don't klnow about the QueryToTable but it cleary states that the BLOB feature is only in the full version.

New function GetBlob(). [Available in full version only]

[Ulrich]

I removed the word to avoid confusion. I thought that the web page makes it somewhat clear what the differences are between the full version and the evaluation build.

Ulrich

[mwreyf1]

So the QueryToTable should pull at least the first record?

[Ulrich]

Yes - the function shows the alert, but the first record of the resultset is returned.

[rexzooly]

If you not wanting to get ODBC i would say try his MySQL plugin its is great it does eveything i needed the ODBC to do, just wish i could make it more safe now i have MySQL called from the app not via php :wow

Good Plugins :yes

[mwreyf1]

Thanks for your quick response.

We saw the error and just closed the app not noticing that the first field had been populated.

Looks like a great plugin that will come in handy.

I will be purchasing at least 2 licenses.