ODBC Actions Plugin Help

ODBC is an action plugin that can be used to extract information and perform operations from databases with correspondent ODBC drivers, through DSN-based or DSN-less connections.

Actions

ODBC.OpenDSNConnection(string DSN, string UID, string PWD);

Description:

Connects to a database, using the DSN, with username and password.

DSN:

(string) The data source name identifying the connection in the ODBC Administration.

UID:

(string) The username to connect to the database. Can be a blank string ("") if not required to complete the connection.

PWD:

(string) The password to connect to the database. Can be a blank string ("") if not required to complete the connection.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- Connect to the database using a DSN entry from the ODBC Administrator panel
result = ODBC.OpenDSNConnection("IR_Forum", "username", "mypassword");

ODBC.OpenDirectConnection(string ConnectionString);

Description:

Connects to a database, DSN-less, using a direct connection string.

ConnectionString:

(string) The string with all parameters required to establish a connection to the database. It may contain a DSN as well.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Examples:

-- Connect to a MS Access database
result = ODBC.OpenDirectConnection("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\folder\\mytable.mdb;");

-- Connect to a SQL Server
result = ODBC.OpenDirectConnection("Driver={SQL Server};Server=MyServerName;Database=MyDatabaseName;"
    .. "Uid=TheUsername;Pwd=TheSecretPassword");

-- Connect to a SQL Server with trusted connection security
result = ODBC.OpenDirectConnection("Driver={SQL Server};Server=MyServerName;Database=MyDatabaseName;"
    .. "Uid=TheUsername;Pwd=TheSecretPassword;Trusted_Connection=yes");

-- Connect to a SQL Server on a remote computer via IP address
result = ODBC.OpenDirectConnection("Driver={SQL Server};Server=10.0.193.206,1433;Network=DBMSSOCN;"
    .. "Database=MyDatabaseName;Uid=TheUsername;Pwd=TheSecretPassword");

-- Connect to a Visual FoxPro database without container (Free Table Directory)
result = ODBC.OpenDirectConnection("Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;"
    .. "SourceDB=C:\\folder\\MySourceDbFolder;Exclusive=No");

Other examples can be found on the web (like here), but you will have to modify these to fit the Lua syntax.

ODBC.ExecuteSQL(string Query);

Description:

Sends a query to a connected database. No resultset will be created, use this only for functions that don't require further processing, like ALTER, DROP, CREATE, INSERT, UPDATE, etc.

Query:

(string) The query to be run on the database.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- perform an operation without resultset
result = ODBC.ExecuteSQL("UPDATE tablename SET column=value WHERE field=criteria");

ODBC.ExecuteDirect(string Command); [Available in full version only]

Description:

Sends a command to a connected database. This can be used to execute stored procedures on the database. The exact syntax depends on which database manager is used.

Command:

(string) The command to be executed.

Returns:

(table) A table containing the result, or nil if no records where returned. You can use Application.GetLastError to determine whether this action failed, and why.

Examples:

-- Call a stored procedure in MS SQL Server
rsTable = ODBC.ExecuteDirect("{ CALL \"master.dbo.sp_who\" }");

-- Another way to call a stored procedure in MS SQL Server
rsTable = ODBC.ExecuteDirect("{ CALL [MyDB].[MyOwner].[My.Table] }");

-- And yet another way
rsTable = ODBC.ExecuteDirect("EXEC sp_who");

-- This syntax is used with InterBase or Firebird SQL
rsTable = ODBC.ExecuteDirect("EXECUTE PROCEDURE \"Procedure01\"");

-- 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: (".. _tblErrorMessages[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 (".. _tblErrorMessages[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 (".. _tblErrorMessages[error] .. ")", MB_OK, MB_ICONSTOP);
   end
end

ODBC.OpenQuery(string Query);

Description:

Sends a query to a connected database. A resultset will be created, which can be retrieved with GetColumn, etc.

Query:

(string) The query to be run on the database.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- perform a query allowing to retrieve records
result = ODBC.OpenQuery("SELECT * FROM client");

ODBC.GetNumCols();

Description:

Retrieves the number of columns in the response to your query. You would use this only after a SELECT statement.

Returns:

(value) The number of columns. If the value returned is zero unexpectedly, you can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- get the number of columns in the result set
numColums = ODBC.GetNumCols();

ODBC.GetColumnName(value Column);

Description:

Retrieves the name of the a single column, normally used to fill header cells. The allowed values for Column are 0 to ODBC.GetNumCols()-1.

Returns:

(string) The data of the field requested if successful, or an empty string if failure. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- get the 1st column name
field = ODBC.GetColumnName(0);

ODBC.GetBlob(value Column); [Available in full version only]

Description:

Retrieves one column from the current row of data, containing binary data (BLOB), as a Base64-encoded string. The allowed values for Column are 0 to ODBC.GetNumCols()-1.

Returns:

(string) The binary data of the blob as a Base64-encoded string if successful, or an empty string if failure. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

data = ODBC.GetBlob(column);
error = Application.GetLastError();
if (error ~= 0) then
    Dialog.Message("Error", _tblErrorMessages[error], MB_OK, MB_ICONEXCLAMATION);
else
    -- must remove line breaks from Base64-encoded string (workaround for bug, ref. #18989)
    data = String.Replace(data, "\r\n", "", true);
    -- process modified data without line breaks
    TextFile.WriteFromString(_TempFolder .. "\\tempdata.b64", data, false);
    Crypto.Base64DecodeFromFile(_TempFolder .. "\\tempdata.b64", _TempFolder .. "\\image.png");
    Image.Load("Image1",_TempFolder .. "\\image.png");
    File.Delete(_TempFolder .. "\\image.png");
end

ODBC.GetColumn(value Column);

Description:

Retrieves one column from the current row of data. The allowed values for Column are 0 to ODBC.GetNumCols()-1.

Returns:

(string) The data of the field requested if successful, or an empty string if failure. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- get the 5th column of data in the current row
field = ODBC.GetColumn(4);

ODBC.NextRow();

Description:

Advances the cursor to the next row of result data.

Returns:

(value) 0 if success (data is available to be fetched), 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- prepare to fetch data
result = ODBC.GetRow();

ODBC.PrevRow();

Description:

Advances the cursor to the former row of result data.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- return to previous record
result = ODBC.PrevRow();

ODBC.FirstRow();

Description:

Advances the cursor to the first row of result data.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- return to the top of the resultset
result = ODBC.FirstRow();

ODBC.LastRow();

Description:

Advances the cursor to the last row of result data.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- return to the last record
result = ODBC.LastRow();

ODBC.IsEOF();

Description:

Checks if there is (still) data available to be fetched.

Returns:

(boolean) true if there is no more data, or false if the end-of-file was yet not reached. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

while (not ODBC.IsEOF()) do
    -- process data
    Grid.SetRowCount("Grid1", row);
    for column = 0, numCols-1 do
        data = ODBC.GetColumn(column);
        Grid.SetCellText("Grid1", row, column, data, true);
    end
    row = row + 1;
    -- fetch next row of data
    ODBC.NextRow();
end

ODBC.IsBOF();

Description:

Checks if the cursor is at the start of the resultset.

Returns:

(boolean) true if the cursor is at the top, or false if it is located somewhere else in the current recordset. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

while (not ODBC.IsBOF()) do
    -- return one record
    ODBC.PrevRow();
end

ODBC.IsDeleted();

Description:

Checks if the cursor is pointing at a deleted record in the resultset.

Returns:

(boolean) true if the current record is marked as deleted. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

while (not ODBC.IsEOF()) do
    if (not ODBC.IsDeleted()) then
        field = ODBC.GetColumn(1);
        Debug.Print("retrieved " .. field .. "\r\n");
    end
    ODBC.NextRow();
end

ODBC.CloseQuery();

Description:

Terminates the query and frees up resources after all the data was retrieved. After this, you may open the next query over the same connection.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- end the current query
result = ODBC.CloseQuery();

ODBC.CloseConnection();

Description:

Terminates the connection and frees up resources. You can't run any queries once the connection to the database is closed.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- close the connection to the database
result = ODBC.CloseConnection();

ODBC.QueryToTable(string Query); [Available in full version only]

Description:

Sends a query to a connected database. The whole resultset will be returned completely, as a table. The first row will contain the column names, which may be used as headers for the grid.

Query:

(string) The query to be run on the database.

Returns:

(table) A table containing the result, or nil if no records where returned. Each element of the table is a single record, and each element is a table by itself, containing the columns. A resultset containing two records with five columns each would create a table[2][5]. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

result = ODBC.OpenDSNConnection("mydsn", "username", "password");
if (result ~= 0) then
   error = Application.GetLastError();
   Dialog.Message("ODBC Actions Plugin", "Connection failed: (".. _tblErrorMessages[error] .. ")", MB_OK, MB_ICONSTOP);
else
   resultset = ODBC.QueryToTable("SELECT * FROM TABLE");
   if (resultset == nil) then
      error = Application.GetLastError();
      Dialog.Message("ODBC Actions Plugin", "Query failed (".. _tblErrorMessages[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
                  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 (".. _tblErrorMessages[error] .. ")", MB_OK, MB_ICONSTOP);
   end
end