2013-06-18 23:35

/* ODBC.rex
 *
 * The REXX script shows how to use the Reginald REXX interpreter
 * to connect to the ODBC Server DataBase and retrieve information
 * from tables in a database using SQL statements. This requires
 * the DLL support file odbc32.dll.
 */

/* We don't need the source */
OPTIONS "NOSOURCE"

/* So that we don't need to use the CALL keyword */
ADDRESS null

/* Allow 32-bit addresses to be stored without exponential notation.
 * Some add-on DLLs not designed for REXX may cause Reginald to
 * deal with storing 32-bit addresses, and we don't want those
 * stored in exponential form.
 */
NUMERIC DIGITS 10







/* ============ FUNCDEF the functions in odbc32.dll ============ */

/* Make things easy for us with WINFUNC option */
OPTIONS "WINFUNC NOSOURCE"

/* Let's trap any ERROR of the following FUNCDEF statements, so we
 * don't have to test if each one succeeded
 */
SIGNAL ON ERROR
SIGNAL ON SYNTAX

/* ODBC Declarations */
FUNCDEF("SQLAllocEnv", "16, void * stor", "odbc32.dll")
FUNCDEF("SQLFreeEnv", "16, void", "odbc32.dll")
FUNCDEF("SQLAllocConnect", "16, void, void * stor", "odbc32.dll")
FUNCDEF("SQLFreeConnect", "16, void", "odbc32.dll")
FUNCDEF("SQLDriverConnect", "16, void, void, char *, 32, char[512] stor, 32, 16 * stor, 32u", "odbc32.dll")
FUNCDEF("SQLDisconnect", "16, void", "odbc32.dll")
FUNCDEF("SQLAllocStmt", "16, void, void * stor", "odbc32.dll")
FUNCDEF("SQLFreeStmt", "16, void, 32u", "odbc32.dll")
FUNCDEF("SQLDrivers", "16, void, 32u, char[513] stor, 32, 16 * stor, char[513] stor, 32, 16 * stor", "odbc32.dll")
FUNCDEF("SQLTables", "16, void, char *, 32, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLExecDirect", "16, void, char *, 32", "odbc32.dll")
FUNCDEF("SQLNumResultCols", "16, void, 16 * stor", "odbc32.dll")
/* We define a buffer of 256 bytes to fetch data. If more is needed redefine this */
FUNCDEF("SQLDescribeCol", "16, void, 32u, char[256] stor, 32, 16 * stor, 16 * stor, 32u * stor, 16 * stor, 16 * stor", "odbc32.dll")
FUNCDEF("SQLFetch", "16, void", "odbc32.dll")
/* We define a buffer of 256 bytes to fetch data. If more is needed redefine this */
FUNCDEF("SQLGetData", "16, void, 32u, 32, char[256] stor, 32, 32 * stor", "odbc32.dll")
FUNCDEF("SQLError", "16, void, void, void, char[6] stor, 32 * stor, char[512] stor, 32, 16 * stor",  "odbc32.dll")
FUNCDEF("SQLSetConnectOptionInt", "16, void, 32u, 32u", "odbc32.dll", "SQLSetConnectOption")
FUNCDEF("SQLGetInfoChar", "16, void, 32u, char[256] stor, 32, 16 * stor", "odbc32.dll", "SQLGetInfo")
FUNCDEF("SQLGetInfoInt", "16, void, 32u, 16 * stor, 32, 16 * stor", "odbc32.dll", "SQLGetInfo")
FUNCDEF("SQLGetFunctions", "16, void, 32u, 16u * stor", "odbc32.dll")
FUNCDEF("SQLGetAllFunctions", "16, void, 32u, 16u[100] * stor", "odbc32.dll", "SQLGetFunctions")
FUNCDEF("SQLDataSources", "16, void, 32u, char[513] stor, 32, 16 * stor, char[513] stor, 32, 16 * stor", "odbc32.dll")

/*
FUNCDEF("SQLConnect", "16, void *, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLBindCol", "16, void, 32u, 32, void, 32, 32 *", "odbc32.dll")
FUNCDEF("SQLBindParameter", "16, void, 32u, 32, 32, 32, 32u, 32, void, 32, void", "odbc32.dll")
FUNCDEF("SQLBrowseConnect", "16, void, char *, 32, char[256] stor, 32, 16 * stor", "odbc32.dll")
FUNCDEF("SQLCancel", "16, void", "odbc32.dll")
FUNCDEF("SQLColAttributes", "16, void, 32u, 32u, char[256] stor, 32, 16 * stor, 32u * stor", "odbc32.dll")
FUNCDEF("SQLColumnPrivileges", "16, void, char *, 32, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLColumns", "16, void, char *, 32, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLDescribeParam", "16, void, 32u, 16 * stor, 32U * stor, 16 * stor, 16 * stor", "odbc32.dll")
FUNCDEF("SQLExecute", "16, void", "odbc32.dll")
FUNCDEF("SQLExtendedFetch", "16, void, 32u, 32, 32u * stor, 16u * stor", "odbc32.dll")
FUNCDEF("SQLForeignKeys", "16, void, char *, 32, char *, 32, char *, 32, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLGetConnectOptionChar", "16, void, 32u, char[256] stor", "odbc32.dll", "SQLGetConnectOption")
FUNCDEF("SQLGetConnectOptionInt", "16, void, 32u, 32 * stor", "odbc32.dll", "SQLGetConnectOption")
FUNCDEF("SQLGetCursorName", "16, void, char[256] stor, 32, 16 * stor", "odbc32.dll")
FUNCDEF("SQLGetInfoDword", "16, void, 32u, 32 * stor, 32, 16 * stor", "odbc32.dll", "SQLGetInfo")
FUNCDEF("SQLGetStmtOptChar", "16, void, 32u, char[256] stor", "odbc32.dll", "SQLGetStmtOption")
FUNCDEF("SQLGetStmtOptionInt", "16, void, 32u, 32 * stor", "odbc32.dll", "SQLGetStmtOption")
FUNCDEF("SQLGetTypeInfo", "16, void, 32", "odbc32.dll")
FUNCDEF("SQLMoreResults", "16, void", "odbc32.dll")
FUNCDEF("SQLNativeSql", "16, void, char *, 32, char[256] stor, 32, 32 * stor", "odbc32.dll")
FUNCDEF("SQLNumParams", "16, void, 16 * stor", "odbc32.dll")
FUNCDEF("SQLParamData", "16, void, void * stor", "odbc32.dll")
FUNCDEF("SQLParamOptions", "16, void, 32u, void", "odbc32.dll")
FUNCDEF("SQLPutData", "16, void, void, 32", "odbc32.dll")
FUNCDEF("SQLPrepare", "16, void, char *, 32", "odbc32.dll")
FUNCDEF("SQLPrimaryKeys", "16, void, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLProcedureColumns", "16, void, char *, 32, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLProcedures", "16, void, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLRowCount", "16, void, 32 * stor", "odbc32.dll")
FUNCDEF("SQLSetConnectOptionChar", "16, void, 32u, str *", "odbc32.dll", "SQLSetConnectOption")
FUNCDEF("SQLSetCursorName", "16, void, char *, 32", "odbc32.dll")
FUNCDEF("SQLSetPos", "16, void, 32u, 32u, 32u", "odbc32.dll")
FUNCDEF("SQLSetScrollOptions", "16, void, 32u, 32, 32u", "odbc32.dll")
FUNCDEF("SQLSetStmtOptionInt", "16, void, 32u, 32u", "odbc32.dll", "SQLSetStmtOption")
FUNCDEF("SQLSetStmtOptionChar", "16, void, 32u, str *", "odbc32.dll", "SQLSetStmtOption")
FUNCDEF("SQLSpecialColumns", "16, void, 32u, char *, 32, char *, 32, char *, 32, 32u, 32u", "odbc32.dll")
FUNCDEF("SQLStatistics", "16, void, char *, 32, char *, 32, char *, 32, 32u, 32u", "odbc32.dll")
FUNCDEF("SQLTablePrivileges", "16, void, char *, 32, char *, 32, char *, 32", "odbc32.dll")
FUNCDEF("SQLTransact", "16, void, void, 32u", "odbc32.dll")
*/

FUNCDEF("RegEnumStrValue", "32, void, 32u, str[260] stor, 32u * dual, void, 32u *, str[260] stor, 32u * dual", "advapi32.dll", "RegEnumValue")
FUNCDEF("RegOpenKey", "32, void, str, 32u * stor", "advapi32.dll")
FUNCDEF("RegCloseKey", "32, void", "advapi32.dll")

SIGNAL OFF ERROR







/* An example of using the helper functions below */


/* Initialize the ODBC interface. */
ERROR = odbcinit()
IF ERROR \== "" THEN DO
   SAY ERROR
   RETURN
END

/* Get the names/descriptions of the installed data sources */
ERROR = odbcgetdsnlist()
IF ERROR \== "" | odbc.!dsncount = 0 THEN DO
   IF ERROR \== "" THEN SAY ERROR
   ELSE SAY "No database drivers available"
END
again:
/* Display the names/descriptions */
DO i = 1 TO odbc.!dsncount
   SAY i "=" odbc.!dsn.i "(" || odbc.!dsndesc.i || ")"
END

/* Get user's chosen driver */
SAY
SAY "Enter number of the desired driver"
PULL i
IF DATATYPE(i, 'W') \== 1 THEN SIGNAL again
IF i > odbc.!dsncount THEN SIGNAL again

/* Open/connect to a database. Let the user enter his
 * user name and password via a dialog.
 */
SAY "Connecting to" odbc.!dsn.i || "..."
ERROR = odbcconnect("DSN=" || odbc.!dsn.i || ";")
IF ERROR \== "" THEN SAY ERROR
ELSE DO

   /* Ok, now print out the full connection string that can be used
    * with ODBCConnect() to bypass the dialog.
    */
   SAY "To bypass the connection dialog, pass this connection string to ODBCConnect():"
   SAY '"' || odbc.!connected || '"'
   SAY

   /* Print some info about the driver */
   CALL odbcprintdriverinfo

   /* Execute an SQL command */
   ERROR = odbcquery("INSERT INTO demotable(demodata) VALUES('Something')")
   IF ERROR \== "" THEN SAY ERROR
   ELSE DO

      /* Now display the results */
      ERROR = odbcgetrecord()
      IF ERROR \== "" THEN SAY ERROR
      ELSE
         DO column = 1 TO odbc.!fieldcount
            SAY odbc.!fielddata.column
         END

   END /* ODBCQuery() */

END /* ODBCConnect() */

/* Free up the ODBC interface. */
odbcexit()

/* All done */
RETURN








/* ========================== SOME ODBC HELPER FUNCTIONS ====================== */

/* ======================= Error Handling ======================= */
syntax:
error:
    /* NOTE: CONDITION('D') fetches error message. CONDITION('E') fetches the
     * error number. SIGL is the line number where the error occurred
     */
    CONDITION('M')

halt:

   /* Free up the ODBC interface if open */
   odbcexit()

   RETURN







/* ****************** ODBCProcessError() *******************
 * This is called only by the other ODBC functions to further
 * process the error message that has been stored in the variables
 * "err" and "errLen". Sometimes the ODBC driver will return a
 * blank message, in which case, we need to return some default
 * message.
 *
 * SYNTAX:
 * error = ODBCProcessError(defaultMessage)
 *
 * RETURNS: An error message.
 *
 * NOTES:
 * Must not use a PROCEDURE statement.
 */

odbcprocesserror:
   IF errlen == 0 THEN RETURN "ODBC API ERROR #" || ret ARG(1)
   RETURN "ODBC ERROR #" || STATE LEFT(err, errlen)





/* ******************** ODBCInit() ********************
 * Allocates/Initializes the ODBC interface. Must be called
 * successfully once before any other ODBC functions are
 * called.
 *
 * SYNTAX:
 * error = ODBCInit()
 *
 * RETURNS: An empty string if success, or an error message
 * if an error.
 *
 * Also sets ODBC.!glEnv as a handle to the ODBC Environment,
 * ODBC.!glDbc to a handle to the ODBC Database Manager.
 *
 * NOTES:
 * If ODBCInit() is successfully called, then you must eventually
 * call ODBCExit() when done using the ODBC interface.
 */

odbcinit: PROCEDURE EXPOSE odbc.
   
   /* Allocate ODBC Environment Handle and check for error */
   ret = sqlallocenv(odbc.!glenv)
   IF ret \== 0 /* SQL_SUCCESS */ THEN DO
      errlen = 0
      CALL sqlerror 0, 0, 0, STATE, statelen, err, 511 /* SQL_MAX_MESSAGE_LENGTH – 1 */, errlen
      RETURN odbcprocesserror("Can't get environment")
   END

   /* Allocate ODBC Database Handle and check for error */
   ret = sqlallocconnect(odbc.!glenv, odbc.!gldbc)
   IF ret \== 0 THEN DO
      errlen = 0
      CALL sqlerror odbc.!glenv, 0, 0, STATE, statelen, err, 511, errlen
      CALL sqlfreeenv odbc.!glenv
      DROP odbc.!glenv
      RETURN odbcprocesserror("Can't get database manager")
   END

   /* Set the login timeout to 15 seconds */
   CALL sqlsetconnectoptionint odbc.!gldbc, 103 /* SQL_LOGIN_TIMEOUT */, 15

   /* Success */
   RETURN ""




/* ******************** ODBCConnect() ********************
 * Opens/Connects to an ODC database. After calling this
 * function successfully, then calls can be made to
 * ODBCQuery() to execute SQL statements on that database.
 *
 * SYNTAX:
 * error = ODBCConnect(connect, window)
 *
 * ARGS:
 * connect = The connection string, for example
 *           "DSN=mydatabase;UID=myUserName;PWD=mypassword;".
 *
 * window = The handle of some window into which the connection
 *          dialog opens (if the dialog needs to be presented).
 *          If omitted, then the dialog opens on the desktop.
 *
 * RETURNS: An empty string if success, or an error message
 * if an error.
 *
 * Also sets ODBC.!glStmt as a handle to the ODBC statement
 * manager, and ODBC.!Connected is set to the full connection
 * string.
 *
 * NOTES: ODBCInit() must have been called once prior to
 * calling this function.
 *
 * If ODBCConnect() succeeds, you must eventually call
 * ODBCDisconnect() when done with the database.
 *
 * The connection string should be as so:
 *
 * If you know the name of the data source, then you can
 * append "DSN=" to the connection string, and then append
 * the data source name followed by a colon. For example,
 * to select the datasource named "My Data Source", your
 * connection string is "ODBC;DSN=My Data Source;". A
 * dialog will be presented to get the user name/password.
 * NOTE: If you don't know the name of the data source, then
 * first call ODBCGetDSNList() and either present the list
 * to the user to get his choice, or use the first source
 * in the list, as so:
 *
 * error = ODBCGetDSNList()
 * IF error \== "" THEN SAY error
 * ELSE IF ODBC.!DsnCount == 0 THEN SAY "No data sources available"
 * ELSE DO
 *    error = ODBCConnect("DSN=" || ODBC.!Dsn.1 || ";")
 *    IF error \== "" THEN SAY error
 *    ELSE DO
 *       /* Here you can go on to do SQLQuery() */
 *    END
 * END
 *
 * If you know the user name, then you can append "UID=" to
 * the connection string, and then append the user name
 * followed by a colon. For example, to choose the user name
 * of "jeff", your connection string is
 * "DSN=My Data Source;UID=jeff;". A dialog will be presented
 * to get the password.
 *
 * If you know the password, then you can append "PWD=" to
 * the connection string, and then append the password
 * followed by a colon. For example, to choose the password
 * of "blort", your connection string is
 * "DSN=My Data Source;PWD=blort;". A dialog will be presented
 * to get the user name.
 *
 * You can combine any of the above items, for example, to
 * specify both the above user name and password, your
 * connection string would be
 * "DSN=My Data Source;UID=jeff;PWD=blort;". In this case,
 * the enduser will not be presented with a dialog to enter
 * any of this information. This is known as supplying a
 * complete connection string.
 *
 * If you don't supply a full connection string, then after
 * the enduser enters his information in the dialog,
 * ODBCConnect() returns the complete connection string you
 * can use in subsequent calls to bypass the dialog. This is
 * stored in ODBC.!Connected.
 */

odbcconnect: PROCEDURE EXPOSE odbc.

   IF ARG(2, 'E') THEN window = ARG(2)
   ELSE window = 0

   /* Connect to the specified database.
    * 
    * The first arg is the Database Manager gotten via SQLAllocConnect().
    *
    * The second arg is the handle to some window you created, or 0 if none.
    *
    * The third arg is your connection string.
    *
    * The fourth arg is the length of the connection string.
    *
    * The fifth arg is the name of the variable where the Database Manager
    * returns the full connection string. Omit this arg if you don't need this
    * returned information.
    *
    * The sixth arg is the size of the fifth arg. We FUNCDEF'ed
    * SQLDriverConnect() to specify a 512 char buffer size, so we
    * pass 512 here. If you change the FUNCDEF to alter the size,
    * then change what you pass below too. If you omit the fifth arg,
    * then omit this too.
    *
    * The seventh arg is the name of a variable that SQLDriverConnect()
    * sets to the LENGTH() of the returned full connection string.
    *
    * The eighth arg is one of the following:
    *
    * SQL_DRIVER_NOPROMPT             0
    * SQL_DRIVER_COMPLETE             1
    * SQL_DRIVER_PROMPT               2
    * SQL_DRIVER_COMPLETE_REQUIRED    3
    */
   errlen = 0
   ret = sqldriverconnect(odbc.!gldbc, window, ARG(1), LENGTH(ARG(1)), STATE, 512, statelen, 1)
   IF ret \== 0 /* SQL_SUCCESS */ & ret \== 1 /* SQL_SUCCESS_WITH_INFO */ THEN DO
      errlen = 0
      CALL sqlerror odbc.!glenv, odbc.!gldbc, 0, STATE, statelen, err, 511, errlen
      RETURN odbcprocesserror("Can't connect to database")
   END

   /* Save the full connection string for next time ODBCConnect is called.
    * By passing this full string, the user will not be prompted with a
    * dialog to enter any information to connect to the same database.
    */
   odbc.!connected = LEFT(STATE, statelen)

   /* Allocate a ODBC Statement Handle */
   ret = sqlallocstmt(odbc.!gldbc, odbc.!glstmt)
   IF ret \== 0 THEN DO
      errlen = 0
      CALL sqlerror odbc.!glenv, odbc.!gldbc, 0, STATE, statelen, err, 511, errlen
      CALL sqldisconnect odbc.!gldbc
      DROP odbc.!connected
      RETURN odbcprocesserror("Can't get statement handle")
   END

   /* Success */
   RETURN ""





/* ********************** ODBCGetTables() *********************
 * Gets the labels of the tables on the currently open database.
 *
 * SYNTAX:
 * error = ODBCGetTables()
 *
 * RETURNS: An empty string if success, or an error message.
 *
 * Also sets ODBC.!Table.0 to a count of how many tables (ie,
 * rows) there are in the database, and ODBC.!Table.1 to
 * ODBC.!Table.xxx to the labels in those rows.
 *
 * NOTES: ODCConnect() must have been called once prior to
 * calling this function.
 */
 
odbcgettables: PROCEDURE EXPOSE odbc.

   /* Make sure SQLTables is supported in this driver */
   ret = odbcfuncissupported("SQLTables")
   IF ret \== "" THEN RETURN ret
 
   /* Get the tables */
   ret = sqltables(odbc.!glstmt, 0, 0, 0, 0, 0, 0, "TABLE", 5 /* LENGTH("TABLE") */ )
   IF ret \== 0 THEN DO
      errlen = 0
      CALL sqlerror odbc.!glenv, odbc.!gldbc, odbc.!glstmt, STATE, statelen, err, 511, errlen
      RETURN odbcprocesserror("Can't get table labels")
   END

   /* Stuff the labels in the ODBC. REXX variable */
   odbc.!table.0 = 0
   DO UNTIL ret \== 0

      /* Get the next row of data */
      ret = sqlfetch(odbc.!glstmt)
      IF ret = -1 /* SQL_ERROR */ THEN LEAVE

      IF ret == 0 /* SQL_SUCCESS */ | ret == 1 /* SQL_SUCCESS_WITH_INFO */ THEN DO

         odbc.!table.0 = odbc.!table.0 + 1 /* Inc row count */

         DO table = 1 TO 3
            ret = sqlgetdata(odbc.!glstmt, table, 1 /* SQL_C_CHAR */, data, 255 /* If you redefine the buffer size, change this */, outlen)
         END

         /* SQLGetData returns outlen = -1 if no data or Null data */
         IF outlen \== -1 & outlen \== 0 THEN odbc.table.[odbc.!table.0] = LEFT(data, outlen) 
         ELSE odbc.table.[odbc.!table.0] = ""

      END

   END

   /* Success */
   RETURN ""




/* ********************** ODBCQuery() *********************
 * Executes an ODBC statement on the currently open database.
 *
 * SYNTAX:
 * error = ODBCQuery(statement)
 *
 * ARGS:
 * statement = The ODBC statement to execute.
 *
 * RETURNS: An empty string if success, or an error message.
 *
 * Also sets ODBC.!FieldCount to a count of how many rows
 * of results there are, and various other stem variables
 * are set as so:
 *
 * ODBC.!FieldName.1 = The label for the first result.
 * ODBC.!FieldTypeNum.1 = The datatype for the first result
 *                         (ie, a number representing the type).
 * ODBC.!FieldTypeStr.1 = The datatype name for the first result
 *                         (ie, "CHAR", "INTEGER", DATE", etc).
 * ODBC.!FieldSize.1 = The column size for the first result.
 * ODBC.!FieldDecDigits.1 = The DecDigits setting for the first
 *                           result.
 * ODBC.!FieldNullav.1 = The Navav setting for the first result.
 * ODBC.!FieldName.2 = The label for the second result.
 * ODBC.!FieldTypeNum.2 = The datatype for the second result.
 * etc.
 *
 * NOTES: ODCConnect() must have been called once prior to
 * calling this function.
 */
 
odbcquery: PROCEDURE EXPOSE odbc.

   /* Execute the ODBC Statement */
   exec = ARG(1)
   ret = sqlexecdirect(odbc.!glstmt, exec, LENGTH(exec))
   IF ret \== 0 /* SQL_SUCCESS */ & ret \== 1 /* SQL_SUCCESS_WITH_INFO */ THEN DO
      exec = "Bad SQL statement"
odbcqueryerr:
      errlen = 0
      CALL sqlerror odbc.!glenv, odbc.!gldbc, odbc.!glstmt, STATE, statelen, err, 511, errlen
      RETURN odbcprocesserror(exec)
   END

   /* Get number of columns in the results returned by the database server */
   ret = sqlnumresultcols(odbc.!glstmt, numcols)
   IF ret \== 0 /* SQL_SUCCESS */ THEN DO
      exec = "Can't get results"
      SIGNAL odbcqueryerr
   END
   odbc.!fieldcount = numcols

   /* Get column descriptor for each row and store in ODBC. stem variable */
   DO col = 1 TO numcols

      ret = sqldescribecol(odbc.!glstmt, col, colname, 255 /* If you redefine the buffer size, change this */, colnamelen, dtype, colsize, DIGITS, av)
      IF ret \== 0 /* SQL_SUCCESS */ THEN DO
         exec = "Can't get column descriptions"
         SIGNAL odbcqueryerr
      END

      SELECT
         WHEN dtype == 1 THEN odbc.!fieldtypestr.col = "CHAR"
         WHEN dtype == 2 THEN odbc.!fieldtypestr.col = "NUMERIC"
         WHEN dtype == 3 THEN odbc.!fieldtypestr.col = "DECIMAL"
         WHEN dtype == 4 THEN odbc.!fieldtypestr.col = "INTEGER"
         WHEN dtype == 5 THEN odbc.!fieldtypestr.col = "SMALLINT"
         WHEN dtype == 6 THEN odbc.!fieldtypestr.col = "FLOAT"
         WHEN dtype == 7 THEN odbc.!fieldtypestr.col = "REAL"
         WHEN dtype == 8 THEN odbc.!fieldtypestr.col = "DOUBLE"
         WHEN dtype == 9 THEN odbc.!fieldtypestr.col = "DATE"
         WHEN dtype == 10 THEN odbc.!fieldtypestr.col = "TIME"
         WHEN dtype == 11 THEN odbc.!fieldtypestr.col = "TIMESTAMP"
         WHEN dtype == 12 THEN odbc.!fieldtypestr.col = "VARCHAR"
         WHEN dtype == -1 THEN odbc.!fieldtypestr.col = "LONGVARCHAR"
         WHEN dtype == -2 THEN odbc.!fieldtypestr.col = "BINARY"
         WHEN dtype == -3 THEN odbc.!fieldtypestr.col = "VARBINARY"
         WHEN dtype == -4 THEN odbc.!fieldtypestr.col = "LONGVARBINARY"
         WHEN dtype == -5 THEN odbc.!fieldtypestr.col = "BIGINT"
         WHEN dtype == -6 THEN odbc.!fieldtypestr.col = "TINYINT"
         WHEN dtype == -7 THEN odbc.!fieldtypestr.col = "BIT"
         WHEN dtype == -11 THEN odbc.!fieldtypestr.col = "GUID"
         OTHERWISE odbc.!fieldtypestr.col = "UNKNOWN TYPE"
      END
      odbc.!fieldname.col = LEFT(colname, colnamelen)
      odbc.!fieldtypenum.col = dtype
      odbc.!fieldsize.col = colsize
      odbc.!fielddecdigits.col = DIGITS
      odbc.!fieldnullav.col = av
   END

   RETURN ""





/* ******************* ODBCGetRecord() *******************
 * Fetch one row of results from the executed SQL
 * statement in ODBCQuery().
 *
 * SYNTAX:
 * error = ODBCGetRecord()
 *
 * RETURNS: An empty string if success, or an error message.
 *
 * Also sets various other stem variables as so:
 *
 * ODBC.!FieldData.1 = The data for the first column.
 * ODBC.!FieldData.2 = The data for the second column.
 * ODBC.!FieldData.3 = The data for the third column.
 * etc.
 *
 * NOTES: ODBCQuery() must have been called successfully
 * once prior to calling this function.
 */
 
odbcgetrecord: PROCEDURE EXPOSE odbc.

   /* Get the next row of data */
odbcgetrecordagain:
   ret = sqlfetch(odbc.!glstmt)
/*   IF ret == 100 /* SQL_NO_DATA */ THEN SIGNAL ODBCGetRecordAgain */

   IF ret \== 0 /* SQL_SUCCESS */ & ret \== 1 /* SQL_SUCCESS_WITH_INFO */ THEN DO
      column = "Can't fetch the row"
odbcgetrecorderr:
      errlen = 0
      CALL sqlerror odbc.!glenv, odbc.!gldbc, odbc.!glstmt, STATE, statelen, err, 511, errlen
      RETURN odbcprocesserror(column)
   END

   /* Get the Data in each field of the Fetched row and stuff it into
    * ODBC.Field.Data.xxx where xxx is the field number
    */
   DO column = 1 TO odbc.!fieldcount

      ret = sqlgetdata(odbc.!glstmt, column, 1, data, 255  /* If you redefine the buffer size, change this */, len)
      IF ret \== 0 THEN DO
         column = "Can't get the data for the row"
         SIGNAL odbcgetrecorderr
      END

      /* len is now the length of the data in "data". This is -1 if no data or NULL data */
      IF len \== -1 THEN odbc.!fielddata.column = LEFT(data, len)
      ELSE odbc.!fielddata.column = ""

   END

   RETURN ""





/* ******************** ODBCDisconnect() ********************
 * Closes/Disconnects from a database that was opened
 * by ODBCConnect(). Must be called once after done using
 * a database (ie, before you use ODBCConnect() to open
 * a different database).
 *
 * SYNTAX:
 * error = ODBCDisconnect()
 *
 * RETURNS: An empty string if success, or an error message.
 */

odbcdisconnect: PROCEDURE EXPOSE odbc.

   /* Release the SQL statement handle gotten with ODBCConnect() */
   IF SYMBOL('ODBC.!GLSTMT') == 'VAR' THEN DO
      ret = sqlfreestmt(odbc.!glstmt, 1 /* SQL_DROP */)
      IF ret \== 0 /* SQL_SUCCESS */ THEN DO
         errlen = 0
         CALL sqlerror odbc.!glenv, odbc.!gldbc, odbc.!glstmt, STATE, statelen, err, 511, errlen
         RETURN odbcprocesserror("Can't release the statement handle")
      END
      DROP odbc.!glstmt
   END

   /* Disconnect from ODBC Database gotten with ODBCConnect() */
   IF SYMBOL('ODBC.!CONNECTED') == 'VAR' THEN DO
      ret = sqldisconnect(odbc.!gldbc)
      IF ret \== 0 THEN DO
         errlen = 0
         CALL sqlerror odbc.!glenv, odbc.!gldbc, 0, STATE, statelen, err, 511, errlen
         RETURN odbcprocesserror("Can't disconnect from the database")
      END
      DROP odbc.!connected
   END 

   RETURN ""





/* ******************** ODBCExit() ********************
 * Releases the ODBC interface that was allocated/initialized
 * with ODBCInit(). Must be called once after done using
 * the ODBC functions. (To use the ODBC functions again,
 * you would then need to call ODBCInit again).
 *
 * SYNTAX:
 * error = ODBCExit()
 *
 * RETURNS: An empty string if success, or an error message.
 */

odbcexit: PROCEDURE EXPOSE odbc.

   /* Disconnect from any database, and free any SQL statement handle */
   ret = odbcdisconnect()
   IF ret \== "" THEN RETURN sqlret

   /* Release the ODBC Database manager */
   IF SYMBOL('ODBC.!GLDBC') == 'VAR' THEN DO
      ret = sqlfreeconnect(odbc.!gldbc)
      IF ret \== 0 THEN DO
         errlen = 0
         CALL sqlerror odbc.!glenv, odbc.!gldbc, 0, STATE, statelen, err, 511, errlen
         RETURN odbcprocesserror("Can't release the database manager")
       END
      DROP odbc.!gldbc
   END

   /* Release the ODBC interface */
   IF SYMBOL('ODBC.!GLENV') == 'VAR' THEN DO
      ret = sqlfreeenv(odbc.!glenv)
      IF ret \== 0 THEN DO
         errlen = 0
         CALL sqlerror odbc.!glenv, 0, 0, STATE, statelen, err, 511, errlen
         RETURN odbcprocesserror("Can't release the interface")
       END
   END

   /* We no longer need the ODBC variable's values */
   DROP odbc.

   RETURN ""





/* *************** ODBCGetDsnSupportedList() ****************
 * Retrieves the descriptions of the database drivers on this
 * computer. These are not the data sources that are necessarily
 * setup on this computer, but rather, a data source _can_ be
 * setup with the Windows ODBC Manager and set to use one of
 * these drivers. For example, someone could setup a data source
 * named "My Data Source" (ie, that will be its DSN) and set it
 * to use the "Microsoft Access Driver" or the "Microsoft Oracle
 * Driver", etc. So ODBCGetDsnSupportedList() would return a
 * listing of the supported driver descriptions, such as
 * "Microsoft Access Driver", "Microsoft Oracle Driver", etc.
 *
 * SYNTAX:
 * error = ODBCGetDsnSupportedList()
 *
 * RETURNS: An empty string if success, or an error message.
 *
 * Also sets various other stem variables as so:
 *
 * ODBC.!SupportCount = Count of how many supported drivers.
 * ODBC.!Support.1 = The description for the first supported driver.
 * ODBC.!Support.2 = The description for the second supported driver.
 * etc.
 *
 * NOTES: ODBCInit() must be called first.
 */

odbcgetdsnsupportedlist: PROCEDURE EXPOSE odbc.

   /* SQL_FETCH_FIRST = Tell SQLDrivers() we want the first supported driver's description */
   DIR = 2

   /* Initially the list is empty */
   odbc.!supportcount = 0

   DO UNTIL ret \== 0

      /* Get next driver's description into the variable "descrip" */
      ret = sqldrivers(odbc.!glenv, DIR, descrip, 513 /* If you redefine the buffer size, change this */, dlen)

      /* Check for an error. Note: If the buffer we supplied was too small, then
       * ret = 1 (SQL_SUCCESS_WITH_INFO). In that case, we would have to redefine
       * SQLDrivers with a larger char[] buffer size, and pass that length in
       * place of 511.
       */
      IF ret \== 0 THEN DO

         /* Did we enumerate them all? */
         IF ret == 100 /* SQL_NO_DATA_FOUND */ THEN LEAVE         

         /* Oops! Must be a real error */
         errlen = 0
         CALL sqlerror 0, 0, 0, STATE, statelen, err, 511 /* SQL_MAX_MESSAGE_LENGTH – 1 */, errlen
         RETURN odbcprocesserror("Can't get supported databases list")
      END

      /* Note: The description is now in "descrip", and "dlen" is the number of
       * characters in it. Let's permanently store the data to our stem variable
       */

      /* Bump the count and store the description in our ODBC. variable */
      odbc.!supportcount = odbc.!supportcount + 1 /* Inc the count */
      odbc.!support.[odbc.!supportcount] = LEFT(descrip, dlen) /* Grab only the pertinent chars of the description */

      /* SQL_FETCH_NEXT = Tell SQLDrivers() we want the next driver's name */
      DIR = 1
      
   END

   /* Success */
   RETURN ""





/* ******************* ODBCGetDSNList() *******************
 * Retrieves the Data Source Names (DSN) and descriptions
 * on this computer. These are the actual data sources for
 * which connection information has been set up by the
 * Windows ODBC Manager. For example, the name of a data
 * source may be "My Data Source", and its description may
 * by "Microsoft Access Driver" (meaning that it uses this
 * driver to interact with a Microsoft Access database).
 *
 * SYNTAX:
 * error = ODBCGetDSNList()
 *
 * RETURNS: An empty string if success, or an error message.
 *
 * Also sets various other stem variables as so:
 *
 * ODBC.!DsnCount = Count of how many data sources.
 * ODBC.!Dsn.1 = The name for the first data source. This
 *               is what is passed to ODBCConnect() in the
 *               connection string after "DSN=".
 * ODBC.!DsnDesc.1 = The description of the first data source.
 * ODBC.!Dsn.2 = The name for the second data source.
 * etc.
 *
 * NOTES: ODBCInit() must be called first.
 */
 
odbcgetdsnlist: PROCEDURE EXPOSE odbc.

   DIR = 2 /* SQL_FETCH_FIRST */

   index = 0
   DO UNTIL ret \== 0

      /* Get the next data source name (into "name") and description (into "descrip") */
      ret = sqldatasources(odbc.!glenv, DIR, NAME, 513 /* SQL_MAX_DSN_LENGTH + 1 */, namelen, descrip, 513, descriplen)
 
      /* Check for an error. Note: If the buffer we supplied for "descrip" was too small,
       * then ret = 1 (SQL_SUCCESS_WITH_INFO). In that case, we would have to redefine
       * SQLDataSources with a larger char[] buffer size, and pass that length in
       * place of 513.
       */
      IF ret \== 0 THEN DO

         /* Did we enumerate them all? */
         IF ret == 100 /* SQL_NO_DATA_FOUND */ THEN LEAVE         

         /* Oops! Must be a real error */
         errlen = 0
         CALL sqlerror 0, 0, 0, STATE, statelen, err, 511 /* SQL_MAX_MESSAGE_LENGTH – 1 */, errlen
         RETURN odbcprocesserror("Can't get DSN list")
      END

      /* Bump the count and save the name/description */
      index = index + 1
      odbc.!dsn.index = LEFT(NAME, namelen)
      odbc.!dsndesc.index = LEFT(descrip, descriplen)

      /* SQL_FETCH_NEXT = Tell SQLDataSources() we want the next source's name/description */
      DIR = 1

   END
   odbc.!dsncount = index

   RETURN ""





/* ********************** ODBCFuncName2Id() *********************
 * Converts an ODBC API function name to its equivalent ID number
 * as passed to SQLGetFunctions().
 *
 * SYNTAX:
 * number = ODBCFuncName2Id(name)
 *
 * RETURNS: The ID number if success, or an empty string if an error.
 */

odbcfuncname2id: PROCEDURE

   func.1 = "SQLAllocConnect"
   func.2 = "SQLAllocEnv"
   func.3 = "SQLAllocStmt"
   func.4 = "SQLBindCol"
   func.5 = "SQLCancel"
   func.6 = "SQLColAttributes"
   func.7 = "SQLConnect"
   func.8 = "SQLDescribeCol"
   func.9 = "SQLDisconnect"
   func.10 = "SQLError"
   func.11 = "SQLExecDirect"
   func.12 = "SQLExecute"
   func.13 = "SQLFetch"
   func.14 = "SQLFreeConnect"
   func.15 = "SQLFreeEnv"
   func.16 = "SQLFreeStmt"
   func.17 = "SQLGetCursorName"
   func.18 = "SQLNumResultCols"
   func.19 = "SQLPrepare"
   func.20 = "SQLRowCount"
   func.21 = "SQLSetCursorName"
   func.22 = "SQLSetParam"
   func.23 = "SQLTransact"
   func.40 = "SQLColumns"
   func.41 = "SQLDriverConnect"
   func.42 = "SQLGetConnectOption"
   func.43 = "SQLGetData"
   func.44 = "SQLGetFunctions"
   func.45 = "SQLGetInfo"
   func.46 = "SQLGetStmtOption"
   func.47 = "SQLGetTypeInfo"
   func.48 = "SQLParamData"
   func.49 = "SQLPutData"
   func.50 = "SQLSetConnectOption"
   func.51 = "SQLSetStmtOption"
   func.52 = "SQLSpecialColumns"
   func.53 = "SQLStatistics"
   func.54 = "SQLTables"
   func.55 = "SQLBrowseConnect"
   func.56 = "SQLColumnPrivileges"
   func.57 = "SQLDataSources"
   func.58 = "SQLDescribeParam"
   func.59 = "SQLExtendedFetch"
   func.60 = "SQLForeignKeys"
   func.61 = "SQLMoreResults"
   func.62 = "SQLNativeSql"
   func.63 = "SQLNumParams"
   func.64 = "SQLParamOptions"
   func.65 = "SQLPrimaryKeys"
   func.66 = "SQLProcedureColumns"
   func.67 = "SQLProcedures"
   func.68 = "SQLSetPos"
   func.69 = "SQLSetScrollOptions"
   func.70 = "SQLTablePrivileges"
   func.71 = "SQLDrivers"
   func.72 = "SQLBindParameter"

   /* Determine what arg to pass to SQLGetFunction() based upon
    * which function name was passed.
    */
   func = STRIP(ARG(1))
   DO i OVER func.
      IF func == func.i THEN RETURN i
   END

   RETURN ""




/* ********************** ODBCFuncIsSupported() *********************
 * Tests if a function is supported. Not all ODBC drivers support all
 * of the ODBC functions. All of them implement a basic set of functions
 * (for example SQLGetFunctions), but some do not support other functions
 * (such as SQLTables). If you're not certain whether a particular
 * driver you're using supports a function (ie, it will return an error
 * if you call that unsupported function), then you can call
 * ODBCFuncIsSupported() to query if a particular function is supported.
 *
 * SYNTAX:
 * error = ODBCFuncIsSupported(funcName)
 *
 * funcName = The name of the function to query, such as "SQLTables".
 *
 * RETURNS: An empty string if supported, or an error message.
 *
 * NOTES: ODCConnect() must have been called once prior to
 * calling this function.
 */

odbcfuncissupported: PROCEDURE EXPOSE odbc.

   func = STRIP(ARG(1))
   ret = odbcfuncname2id(func)
   IF ret \== "" THEN DO
      ret = sqlgetfunctions(odbc.!gldbc, ret, flag)
      IF ret \== 0 THEN DO
         errlen = 0
         CALL sqlerror odbc.!glenv, odbc.!gldbc, 0, STATE, statelen, err, 511, errlen
         RETURN odbcprocesserror("Can't get function information")
      END
      IF flag THEN RETURN ""
   END
   RETURN "ODBC ERROR:" func "is not supported"

   RETURN ""




/* ********************** ODBCPrintDriverInfo() *********************
 * SAY's information about the driver for the currently open database.
 *
 * SYNTAX:
 * error = ODBCPrintDriverInto()
 *
 * RETURNS: An empty string if success, or an error message.
 *
 * NOTES: ODCConnect() must have been called once prior to
 * calling this function.
 */

odbcprintdriverinfo: PROCEDURE EXPOSE odbc.

   func.1 = "SQLAllocConnect"
   func.2 = "SQLAllocEnv"
   func.3 = "SQLAllocStmt"
   func.4 = "SQLBindCol"
   func.5 = "SQLCancel"
   func.6 = "SQLColAttributes"
   func.7 = "SQLConnect"
   func.8 = "SQLDescribeCol"
   func.9 = "SQLDisconnect"
   func.10 = "SQLError"
   func.11 = "SQLExecDirect"
   func.12 = "SQLExecute"
   func.13 = "SQLFetch"
   func.14 = "SQLFreeConnect"
   func.15 = "SQLFreeEnv"
   func.16 = "SQLFreeStmt"
   func.17 = "SQLGetCursorName"
   func.18 = "SQLNumResultCols"
   func.19 = "SQLPrepare"
   func.20 = "SQLRowCount"
   func.21 = "SQLSetCursorName"
   func.22 = "SQLSetParam"
   func.23 = "SQLTransact"
   func.40 = "SQLColumns"
   func.41 = "SQLDriverConnect"
   func.42 = "SQLGetConnectOption"
   func.43 = "SQLGetData"
   func.44 = "SQLGetFunctions"
   func.45 = "SQLGetInfo"
   func.46 = "SQLGetStmtOption"
   func.47 = "SQLGetTypeInfo"
   func.48 = "SQLParamData"
   func.49 = "SQLPutData"
   func.50 = "SQLSetConnectOption"
   func.51 = "SQLSetStmtOption"
   func.52 = "SQLSpecialColumns"
   func.53 = "SQLStatistics"
   func.54 = "SQLTables"
   func.55 = "SQLBrowseConnect"
   func.56 = "SQLColumnPrivileges"
   func.57 = "SQLDataSources"
   func.58 = "SQLDescribeParam"
   func.59 = "SQLExtendedFetch"
   func.60 = "SQLForeignKeys"
   func.61 = "SQLMoreResults"
   func.62 = "SQLNativeSql"
   func.63 = "SQLNumParams"
   func.64 = "SQLParamOptions"
   func.65 = "SQLPrimaryKeys"
   func.66 = "SQLProcedureColumns"
   func.67 = "SQLProcedures"
   func.68 = "SQLSetPos"
   func.69 = "SQLSetScrollOptions"
   func.70 = "SQLTablePrivileges"
   func.71 = "SQLDrivers"
   func.72 = "SQLBindParameter"

   /* Display which of the ODBC APIs the driver supports */
   SAY  "------------------" || '0D0A'x || "ODBC API Functions" || '0D0A'x "------------------"
   sqlgetallfunctions(odbc.!gldbc, 0 /* SQL_API_ALL_FUNCTIONS */, funcs)
   DO i = 1 TO 100 /* SQL_NUM_FUNCTIONS */
      IF SYMBOL("FUNC."||i) == 'VAR' THEN DO
         IF funcs.i THEN SAY func.i "is supported"
         ELSE SAY func.i "is not supported"
      END
   END

/*
   /* Ok we're connected. Get the SQL_CURSOR_COMMIT_BEHAVIOR of the database */
   error = SQLGetInfoInt(ODBC.!glDbc, 23 /* SQL_CURSOR_COMMIT_BEHAVIOR */, info, 2, infoLen)
   IF error == 0 THEN SAY "SQL_CURSOR_COMMIT_BEHAVIOR =" LEFT(info, infoLen)
*/
   RETURN ""

2013-06-18 23:35