Re: ODBC formatting

new topic     » goto parent     » topic index » view thread      » older message » newer message 

Matt Lewis wrote:
> I think this is the fault of the wrapper.  Looking at the code, it appears
> to interpret a SQL_INTEGER as an unsigned int, which seems incorrect.
> 
> And it doesn't understand SQL_SMALLINTs at all.

Matt/Mike,

Yes, I ran into the same problem. I'm including in this post (sorry 
about the long post) my modified ODBC.e that fixes the SQL_INTEGER issue 
and adds support for SQL_SMALLINT.  It also has the following 
changes/enhancements:
* Returns date/time stamp in a character format.
* New "getColAttribute" function
* Fixed bug where a block of records would be skipped when limiting the 
number of records returned on a fetch
* New "tableListEx" function that allows you to list tables based on a 
pattern
* New "columnList" function to get the columns in a table

Sorry but I haven't updated any documentation for these changes...


--Introduction
----------------

/topic Introduction

/info

odbc.e v1.33 <br> Matt Lewis <br>

 <a href="mailto:matthewwalkerlewis at yahoo.com">matthewwalkerlewis at yahoo.com</a>

ODBC.e is a library that opens up Open Database Connectivity (ODBC) to Euphoria. At this point, virtually every database has an ODBC driver,

which allows other applications to interact with the database through the standard ODBC API, and using Structured Query Language (SQL).

 For more details on ODBC, I recommend looking at Microsoft's

<a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odch21apr.asp?frame=true"> ODBC API Reference</a>.

 In order to use this library, you'll either need to set up a User Data Source

Name (DSN) (System DSN's should work, too, but I haven't tested them yet) or use a connection string to connect to a database.

See your database manual, or Windows/UnixODBC help for info on setting up a DSN.

Once the DSN is up and running, you're ready to use the database with Euphoria!

 LICENSE AND DISCLAIMER

 This software is freeware, but you must give me credit for any applications developed with

it. You can modify any of the code, so long as you do not take credit for any of the original source, and any modifications must be marked as such.

 THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT

NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,

SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFIT; OR BUSINESS INTERRUPTION) HOWEVER

CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN

IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

/code Included files:

 * odbc.e : ODBC Library * wxOdbc.exw : Win32/Linux Demo

 * print.e : Used by the demo

Not included: * <a href="http://wxeuphoria.sourceforge.net">wxEuphoria</a> v0.3.0 : Required for the demo

/endcode

Change Log

/topic Change Log

/info History of ODBC.e

 /code

v1.33 * Renamed to odbc.e: Jonas Temple added support for Linux using UnixODBC

* Fixed problem with /dataSourcesODBC() (thanks to Jonas Temple) * Added data types

 * Use SQLFetchScroll instead of deprecated SQLExtendedFetch * Improved error reporting to cover other return codes

* /odbcError() no longer calls message_box(), since this is Win32 only. Instead it prints to STDERR

* /moveToRecord() now returns an atom on error for better error handling. * Demo converted to wxEuphoria so that it's cross platform.

* Fixed memory leaks in /prepareSQL(), /getColumnHeaders() and /getData() * Added /rowCount()

* /initODBC() now a function in order to verify that ODBC properly initialized. * /execDirectODBC() now returns all data regardless of calls to /setMaxRecords,

 so that handles are properly freed

v1.32.1

 * Fixed bug in /moveToRecord() when record had NULL fields

v1.32 * Added handling for SQL_TYPE_DATE to convertData() and revertData()

 Returns { Y, M, D } (I think)... * Added SQL_TYPE_TIMESTAMP (used my MS Access) to return YYYY-MM-DD HH:MM:SS

 v1.31.1

* Fixed resource leak in /execDirectODBC() (thanks to Josef Jindra) * Fixed zero-length string error in convertData() (thanks to Josef Jindra)

 v1.31

 * Fixed bug in createCursor() that didn't take into account the new behavior of getColumnHeaders()

 * Added getErrorODBC() to allow more flexible error handling

v1.3 * getColumnHeaders() no longer returns the headers in an extra

 layer of sequence * Added routines for inserting data

 * Added dataTypeName()

v1.2 * Added ability to compile SQL without executing and set parameters

 * Rollback and Commit transactions * Cursors

 * Converts SQL_NUMERIC and SQL_DECIMAL values * Improved connection options

 * Removed Win32Lib dependency from the library (the demo still uses it, though)

 v1.1

 * Get partial chunks of data. * Execute a SQL statement without returning data.

/endcode

include get.e include dll.e include msgbox.e include machine.e without warning include misc.e

with trace

Beg JT01 - Define odbc32, kernel32 and xlstrlen as atoms atom odbc32, kernel32, xlstrlen if platform() = LINUX then odbc32 = open_dll("/usr/lib/libodbc.so") else odbc32 = open_dll("odbc32.dll") kernel32 = open_dll( "kernel32" ) xlstrlen = define_c_func(kernel32,"lstrlen",{C_POINTER},C_INT) end if End JT01

 Connecting to a data source Beg JT01 constant

 End JT01

Connecting to a data source SQLAllocHandle = define_c_func(odbc32, "SQLAllocHandle", repeat( C_LONG, 3 ), C_SHORT), SQLConnect = define_c_func( odbc32, "SQLConnect", { C_LONG, C_POINTER, C_USHORT, C_POINTER, C_SHORT, C_POINTER, C_USHORT}, C_SHORT ), SQLBrowseConnect = define_c_func(odbc32, "SQLBrowseConnect", { C_LONG, C_CHAR, C_SHORT, C_CHAR, C_SHORT, C_SHORT}, C_SHORT ), SQLDriverConnect = define_c_func( odbc32, "SQLDriverConnect", { C_LONG, C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_USHORT}, C_SHORT ), SQLDataSources = define_c_func( odbc32, "SQLDataSources", { C_LONG, C_USHORT, C_POINTER, C_SHORT, C_POINTER, C_POINTER, C_SHORT, C_POINTER }, C_SHORT ),

Obtainint information about a driver and data source SQLDrivers = define_c_func( odbc32, "SQLDrivers", { C_LONG, C_USHORT, C_POINTER, C_SHORT, C_POINTER, C_POINTER, C_SHORT, C_POINTER}, C_SHORT ), SQLGetInfo = define_c_func( odbc32, "SQLGetInfo", { C_LONG, C_USHORT, C_POINTER, C_SHORT, C_POINTER} , C_SHORT ), SQLGetFunctions = define_c_func( odbc32, "SQLGetFunctions", { C_LONG, C_USHORT, C_POINTER}, C_SHORT ), SQLGetTypeInfo = define_c_func( odbc32, "SQLGetTypeInfo", { C_LONG, C_SHORT }, C_SHORT ), SQLGetEnvAttr = define_c_func( odbc32, "SQLGetEnvAttr", { C_LONG, C_INT, C_POINTER, C_INT, C_POINTER }, C_SHORT ), SQLSetEnvAttr = define_c_func( odbc32, "SQLSetEnvAttr", { C_LONG, C_INT, C_POINTER, C_INT}, C_SHORT ), Setting and retrieving driver attributes SQLSetConnectAttr = define_c_func( odbc32, "SQLSetConnectAttr", { C_LONG, C_INT, C_POINTER, C_INT}, C_SHORT ), SQLGetConnectAttr = define_c_func( odbc32, "SQLGetConnectAttr", { C_LONG, C_INT, C_POINTER, C_INT, C_INT, C_POINTER }, C_SHORT ), SQLSetStmtAttr = define_c_func( odbc32, "SQLSetStmtAttr", { C_LONG, C_INT, C_POINTER, C_INT}, C_SHORT ), SQLGetStmtAttr = define_c_func( odbc32, "SQLGetStmtAttr", { C_LONG, C_INT, C_POINTER, C_INT, C_POINTER}, C_SHORT ),

 Setting and retrieving descriptor fields SQLGetDescField = define_c_func( odbc32, "SQLGetDescField", { C_LONG, C_SHORT, C_SHORT, C_POINTER, C_INT, C_POINTER }, C_SHORT ), SQLGetDescRec = define_c_func( odbc32, "SQLGetDescRec", { C_LONG, C_SHORT, C_POINTER, C_SHORT } & repeat( C_POINTER, 7), C_SHORT ), SQLSetDescField = define_c_func( odbc32, "SQLSetDescField", { C_LONG, C_SHORT, C_SHORT, C_POINTER, C_INT }, C_SHORT ), SQLSetDescRec = define_c_func( odbc32, "SQLSetDescRec", { C_LONG, C_SHORT, C_SHORT, C_SHORT, C_INT, C_SHORT, C_SHORT } & repeat( C_POINTER, 3 ), C_SHORT ), SQLCopyDesc = define_c_func( odbc32, "SQLCopyDesc", { C_LONG, C_LONG}, C_SHORT ),

Preparing SQL requests SQLPrepare = define_c_func( odbc32, "SQLPrepare", { C_LONG, C_POINTER, C_INT }, C_SHORT ), SQLBindParameter = define_c_func(odbc32, "SQLBindParameter", { C_LONG, C_USHORT, C_SHORT, C_SHORT, C_SHORT, C_USHORT, C_SHORT, C_POINTER, C_INT, C_INT}, C_SHORT ), SQLGetCursorName = define_c_func( odbc32, "SQLGetCursorName", { C_LONG, C_POINTER, C_SHORT, C_POINTER }, C_SHORT ), SQLSetCursorName = define_c_func( odbc32, "SQLSetCursorName", { C_LONG, C_POINTER, C_INT }, C_SHORT ),

Submiting requests SQLExecute = define_c_func( odbc32, "SQLExecute", {C_LONG}, C_SHORT ), SQLExecDirect = define_c_func( odbc32, "SQLExecDirect", { C_LONG, C_POINTER, C_INT }, C_SHORT ), SQLNativeSql = define_c_func( odbc32, "SQLNativeSql", { C_LONG, C_POINTER, C_INT, C_POINTER, C_INT, C_POINTER}, C_SHORT ), SQLDescribeParam = define_c_func( odbc32, "SQLCDescribeParam", { C_LONG, C_USHORT } & repeat( C_POINTER, 4 ), C_SHORT ), SQLNumParams = define_c_func( odbc32, "SQLNumParams", { C_LONG, C_SHORT }, C_SHORT ), SQLParamData = define_c_func( odbc32, "SQLParamData", { C_LONG, C_POINTER }, C_SHORT ), SQLPutData = define_c_func( odbc32, "SQLPutData", { C_LONG, C_POINTER, C_INT }, C_SHORT ),

Retrieving results and information about results SQLRowCount = define_c_func( odbc32, "SQLRowCount", { C_LONG, C_POINTER}, C_SHORT ), SQLNumResultCols = define_c_func( odbc32, "SQLNumResultCols", { C_LONG, C_SHORT }, C_SHORT ), SQLDescribeCol = define_c_func( odbc32, "SQLDescribeCol", { C_LONG, C_SHORT, C_POINTER, C_SHORT } & repeat( C_POINTER, 5), C_SHORT ), SQLColAttribute = define_c_func( odbc32, "SQLColAttribute", {C_LONG, C_USHORT, C_USHORT, C_POINTER, C_SHORT, C_POINTER, C_POINTER}, C_SHORT ), SQLBindCol = define_c_func(odbc32, "SQLBindCol", { C_LONG, C_USHORT, C_SHORT, C_POINTER, C_INT, C_POINTER}, C_SHORT ), SQLFetch = define_c_func( odbc32, "SQLFetch", { C_LONG }, C_SHORT ), SQLExtendedFetch = define_c_func( odbc32, "SQLExtendedFetch", { C_LONG, C_USHORT, C_INT, C_POINTER, C_POINTER}, C_SHORT ), SQLFetchScroll = define_c_func( odbc32, "SQLFetchScroll", { C_LONG, C_SHORT, C_INT }, C_SHORT ), SQLGetData = define_c_func( odbc32, "SQLGetData", { C_LONG, C_USHORT, C_SHORT, C_POINTER, C_INT, C_POINTER }, C_SHORT ), SQLSetPos = define_c_func( odbc32, "SQLSetPos", { C_LONG, C_USHORT, C_USHORT, C_USHORT}, C_SHORT ), SQLBulkOperations = define_c_func( odbc32, "SQLBulkOperations", { C_LONG, C_USHORT }, C_SHORT ), SQLMoreResults = define_c_func( odbc32, "SQLMoreResults", { C_LONG }, C_SHORT ), SQLGetDiagField = define_c_func( odbc32, "SQLGetDiagField", { C_SHORT, C_LONG, C_SHORT, C_SHORT, C_POINTER, C_SHORT, C_POINTER }, C_SHORT ), SQLGetDiagRec = define_c_func( odbc32, "SQLGetDiagRec", { C_SHORT, C_LONG, C_SHORT, C_POINTER, C_POINTER, C_POINTER, C_SHORT, C_POINTER}, C_SHORT ),

Obtaining information about the data source's system tables (catalog functions) SQLColumnPrivileges = define_c_func( odbc32, "SQLColumnPriveleges", { C_LONG, C_POINTER, C_USHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT}, C_SHORT ), SQLColumns = define_c_func( odbc32, "SQLColumns", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT}, C_SHORT ), SQLForeignKeys = define_c_func( odbc32, "SQLForeignKeys", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT}, C_SHORT ), SQLPrimaryKeys = define_c_func( odbc32, "SQLPrimaryKeys", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT} , C_SHORT ), SQLProcedureColumns = define_c_func( odbc32, "SQLProcedureColumns", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT}, C_SHORT ), SQLProcedures = define_c_func( odbc32, "SQLProcedures", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT}, C_SHORT ), SQLSpecialColumns = define_c_func( odbc32, "SQLSpecialColumns", { C_LONG, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER } & repeat( C_SHORT, 3), C_SHORT ), SQLStatistics = define_c_func( odbc32, "SQLStatistics", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_USHORT, C_USHORT}, C_SHORT ), SQLTablePrivileges = define_c_func( odbc32, "SQLTablePrivileges", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT}, C_SHORT ), SQLTables = define_c_func( odbc32, "SQLTables", { C_LONG, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT, C_POINTER, C_SHORT}, C_SHORT ),

 Terminating a statement SQLFreeStmt = define_c_func( odbc32, "SQLFreeStmt", {C_LONG, C_USHORT}, C_SHORT ), SQLCloseCursor = define_c_func( odbc32, "SQLCloseCursor", {C_LONG}, C_SHORT ), SQLCancel = define_c_func(odbc32, "SQLCancel", { C_LONG }, C_SHORT ), SQLEndTran = define_c_func( odbc32, "SQLEndTran", { C_SHORT, C_LONG, C_SHORT }, C_SHORT ),

Terminating a connection SQLDisconnect = define_c_func( odbc32, "SQLDisconnect", { C_LONG }, C_SHORT ), SQLFreeHandle = define_c_func( odbc32, "SQLFreeHandle", { C_SHORT, C_LONG }, C_SHORT )

global constant

/* special length/indicator values */ SQL_NULL_DATA =(-1), SQL_DATA_AT_EXEC =(-2),

/* return values from functions */ SQL_SUCCESS = 0, SQL_SUCCESS_WITH_INFO = 1, SQL_NO_DATA = 100, SQL_ERROR = (-1), SQL_INVALID_HANDLE = (-2),

SQL_STILL_EXECUTING = 2, SQL_NEED_DATA = 99,

/* test for SQL_SUCCESS or SQL_SUCCESS_WITH_INFO */ ??

SQL_SUCCEEDED(rc) (((rc)&(~1))==0)

/* flags for null-terminated string */ SQL_NTS = (-3), SQL_NTSL = (-3),

/* maximum message length */ SQL_MAX_MESSAGE_LENGTH = 512,

/* date/time length constants */ SQL_DATE_LEN =10, SQL_TIME_LEN = 8 ,

/* add P+1 if precision is nonzero */ SQL_TIMESTAMP_LEN =19 , /* add P+1 if precision is nonzero */

/* handle type identifiers */ SQL_HANDLE_ENV = 1, SQL_HANDLE_DBC = 2, SQL_HANDLE_STMT = 3, SQL_HANDLE_DESC = 4,

/* environment attribute */ SQL_ATTR_OUTPUT_NTS =10001,

/* connection attributes */ SQL_ATTR_AUTO_IPD =10001, SQL_ATTR_METADATA_ID =10014,

/* statement attributes */ SQL_ATTR_APP_ROW_DESC = 10010, SQL_ATTR_APP_PARAM_DESC= 10011, SQL_ATTR_IMP_ROW_DESC = 10012, SQL_ATTR_IMP_PARAM_DESC= 10013, SQL_ATTR_CURSOR_SCROLLABLE= (-1), SQL_ATTR_CURSOR_SENSITIVITY= (-2),

/* SQL_ATTR_CURSOR_SCROLLABLE values */ SQL_NONSCROLLABLE = 0, SQL_SCROLLABLE = 1,

Beg JT02

SQLColAttribute identifiers SQL_DESC_UNSIGNED = 8, End JT02

/* identifiers of fields in the SQL descriptor */ SQL_DESC_COUNT = 1001, SQL_DESC_TYPE = 1002, SQL_DESC_LENGTH = 1003, SQL_DESC_OCTET_LENGTH_PTR = 1004, SQL_DESC_PRECISION = 1005, SQL_DESC_SCALE = 1006, SQL_DESC_DATETIME_INTERVAL_CODE= 1007, SQL_DESC_NULLABLE = 1008, SQL_DESC_INDICATOR_PTR = 1009, SQL_DESC_DATA_PTR = 1010, SQL_DESC_NAME = 1011, SQL_DESC_UNNAMED = 1012, SQL_DESC_OCTET_LENGTH = 1013, SQL_DESC_ALLOC_TYPE = 1099,

/* identifiers of fields in the diagnostics area */ SQL_DIAG_RETURNCODE = 1, SQL_DIAG_NUMBER = 2, SQL_DIAG_ROW_COUNT = 3, SQL_DIAG_SQLSTATE = 4, SQL_DIAG_NATIVE = 5, SQL_DIAG_MESSAGE_TEXT = 6, SQL_DIAG_DYNAMIC_FUNCTION = 7, SQL_DIAG_CLASS_ORIGIN = 8, SQL_DIAG_SUBCLASS_ORIGIN = 9, SQL_DIAG_CONNECTION_NAME =10, SQL_DIAG_SERVER_NAME =11, SQL_DIAG_DYNAMIC_FUNCTION_CODE= 12,

/* dynamic function codes */ SQL_DIAG_ALTER_DOMAIN = 3, SQL_DIAG_ALTER_TABLE = 4, SQL_DIAG_CALL = 7, SQL_DIAG_CREATE_ASSERTION = 6, SQL_DIAG_CREATE_CHARACTER_SET = 8, SQL_DIAG_CREATE_COLLATION = 10, SQL_DIAG_CREATE_DOMAIN = 23, SQL_DIAG_CREATE_INDEX = (-1), SQL_DIAG_CREATE_SCHEMA = 64, SQL_DIAG_CREATE_TABLE = 77, SQL_DIAG_CREATE_TRANSLATION = 79, SQL_DIAG_CREATE_VIEW = 84, SQL_DIAG_DELETE_WHERE = 19, SQL_DIAG_DROP_ASSERTION = 24, SQL_DIAG_DROP_CHARACTER_SET = 25, SQL_DIAG_DROP_COLLATION = 26, SQL_DIAG_DROP_DOMAIN = 27, SQL_DIAG_DROP_INDEX = (-2), SQL_DIAG_DROP_SCHEMA = 31, SQL_DIAG_DROP_TABLE = 32, SQL_DIAG_DROP_TRANSLATION = 33, SQL_DIAG_DROP_VIEW = 36, SQL_DIAG_DYNAMIC_DELETE_CURSOR= 38, SQL_DIAG_DYNAMIC_UPDATE_CURSOR= 81, SQL_DIAG_GRANT = 48, SQL_DIAG_INSERT = 50, SQL_DIAG_REVOKE = 59, SQL_DIAG_SELECT_CURSOR = 85, SQL_DIAG_UNKNOWN_STATEMENT = 0, SQL_DIAG_UPDATE_WHERE = 82,

/* SQL data type codes */ SQL_BINARY = (-2), SQL_VARBINARY = (-3), SQL_LONGVARBINARY = (-4), SQL_BIGINT = (-5), SQL_TINYINT = (-6), SQL_BIT = (-7), SQL_UNKNOWN_TYPE = 0, SQL_CHAR = 1, SQL_NUMERIC = 2, SQL_DECIMAL = 3, SQL_INTEGER = 4, SQL_SMALLINT = 5, SQL_FLOAT = 6, SQL_REAL = 7, SQL_DOUBLE = 8, SQL_DATETIME = 9, SQL_VARCHAR = 12,

SQL_C_CHAR = SQL_CHAR,

/* CHAR, VARCHAR, DECIMAL, NUMERIC */ SQL_C_LONG = SQL_INTEGER, /* INTEGER */ SQL_C_SHORT = SQL_SMALLINT,

 /* SMALLINT */ SQL_C_FLOAT = SQL_REAL, /* REAL */ SQL_C_DOUBLE = SQL_DOUBLE,

 /* FLOAT, DOUBLE */ SQL_C_NUMERIC = SQL_NUMERIC, SQL_C_DEFAULT =99,

/* One-parameter shortcuts for date/time data types */ SQL_TYPE_DATE =91, SQL_TYPE_TIME =92, SQL_TYPE_TIMESTAMP =93,

/* Statement attribute values for cursor sensitivity */ SQL_UNSPECIFIED = 0, SQL_INSENSITIVE = 1, SQL_SENSITIVE = 2,

/* GetTypeInfo() request for all data types */ SQL_ALL_TYPES =0,

/* Default conversion code for SQLBindCol(), SQLBindParam() and SQLGetData() */ SQL_DEFAULT =99,

/* SQLGetData() code indicating that the application row descriptor

* specifies the data type SQL_ARD_TYPE = (-99),

/* SQL date/time type subcodes */ SQL_CODE_DATE = 1, SQL_CODE_TIME = 2, SQL_CODE_TIMESTAMP= 3,

/* CLI option values */ SQL_FALSE = 0, SQL_TRUE = 1,

/* values of NULLABLE field in descriptor */ SQL_NO_NULLS = 0, SQL_NULLABLE = 1,

/* Value returned by SQLGetTypeInfo() to denote that it is /* not known whether or not a data type supports null values. SQL_NULLABLE_UNKNOWN = 2,

/* Values returned by SQLGetTypeInfo() to show WHERE clause * supported SQL_PRED_NONE = 0, SQL_PRED_CHAR = 1, SQL_PRED_BASIC = 2,

/* values of UNNAMED field in descriptor */ SQL_NAMED = 0, SQL_UNNAMED = 1,

/* values of ALLOC_TYPE field in descriptor */ SQL_DESC_ALLOC_AUTO= 1, SQL_DESC_ALLOC_USER= 2,

/* FreeStmt() options */ SQL_CLOSE = 0, SQL_DROP = 1, SQL_UNBIND = 2, SQL_RESET_PARAMS = 3,

/* Codes used for FetchOrientation in SQLFetchScroll(),

and in SQLDataSources() SQL_FETCH_NEXT = 1, SQL_FETCH_FIRST = 2,

/* Other codes used for FetchOrientation in SQLFetchScroll() */ SQL_FETCH_LAST = 3, SQL_FETCH_PRIOR = 4, SQL_FETCH_ABSOLUTE= 5, SQL_FETCH_RELATIVE= 6,

/* SQLEndTran() options */ SQL_COMMIT = 0, SQL_ROLLBACK = 1,

/* null handles returned by SQLAllocHandle() */ SQL_NULL_HENV = 0, SQL_NULL_HDBC = 0, SQL_NULL_HSTMT = 0, SQL_NULL_HDESC = 0,

/* null handle used in place of parent handle when allocating HENV */ SQL_NULL_HANDLE = 0,

/* Values that may appear in the result set of SQLSpecialColumns() */ SQL_SCOPE_CURROW = 0, SQL_SCOPE_TRANSACTION= 1, SQL_SCOPE_SESSION = 2,

SQL_PC_UNKNOWN = 0, SQL_PC_NON_PSEUDO = 1, SQL_PC_PSEUDO = 2,

/* Reserved value for the IdentifierType argument of SQLSpecialColumns() */ SQL_ROW_IDENTIFIER = 1,

/* Reserved values for UNIQUE argument of SQLStatistics() */ SQL_INDEX_UNIQUE = 0, SQL_INDEX_ALL = 1,

/* Values that may appear in the result set of SQLStatistics() */ SQL_INDEX_CLUSTERED= 1, SQL_INDEX_HASHED = 2, SQL_INDEX_OTHER = 3,

/* SQLGetFunctions() values to identify ODBC APIs */ SQL_API_SQLALLOCCONNECT = 1, SQL_API_SQLALLOCENV = 2, SQL_API_SQLALLOCHANDLE = 1001, SQL_API_SQLALLOCSTMT = 3, SQL_API_SQLBINDCOL = 4, SQL_API_SQLBINDPARAM = 1002, SQL_API_SQLCANCEL = 5, SQL_API_SQLCLOSECURSOR = 1003, SQL_API_SQLCOLATTRIBUTE = 6, SQL_API_SQLCOLUMNS = 40, SQL_API_SQLCONNECT = 7, SQL_API_SQLCOPYDESC = 1004, SQL_API_SQLDATASOURCES = 57, SQL_API_SQLDESCRIBECOL = 8, SQL_API_SQLDISCONNECT = 9, SQL_API_SQLENDTRAN = 1005, SQL_API_SQLERROR = 10, SQL_API_SQLEXECDIRECT = 11, SQL_API_SQLEXECUTE = 12, SQL_API_SQLFETCH = 13, SQL_API_SQLFETCHSCROLL = 1021, SQL_API_SQLFREECONNECT = 14, SQL_API_SQLFREEENV = 15, SQL_API_SQLFREEHANDLE = 1006, SQL_API_SQLFREESTMT = 16, SQL_API_SQLGETCONNECTATTR = 1007, SQL_API_SQLGETCONNECTOPTION= 42, SQL_API_SQLGETCURSORNAME = 17, SQL_API_SQLGETDATA = 43, SQL_API_SQLGETDESCFIELD = 1008, SQL_API_SQLGETDESCREC = 1009, SQL_API_SQLGETDIAGFIELD = 1010, SQL_API_SQLGETDIAGREC = 1011, SQL_API_SQLGETENVATTR = 1012, SQL_API_SQLGETFUNCTIONS = 44, SQL_API_SQLGETINFO = 45, SQL_API_SQLGETSTMTATTR = 1014, SQL_API_SQLGETSTMTOPTION = 46, SQL_API_SQLGETTYPEINFO = 47, SQL_API_SQLNUMRESULTCOLS = 18, SQL_API_SQLPARAMDATA = 48, SQL_API_SQLPREPARE = 19, SQL_API_SQLPUTDATA = 49, SQL_API_SQLROWCOUNT = 20, SQL_API_SQLSETCONNECTATTR = 1016, SQL_API_SQLSETCONNECTOPTION= 50, SQL_API_SQLSETCURSORNAME = 21, SQL_API_SQLSETDESCFIELD = 1017, SQL_API_SQLSETDESCREC = 1018, SQL_API_SQLSETENVATTR = 1019, SQL_API_SQLSETPARAM = 22, SQL_API_SQLSETSTMTATTR = 1020, SQL_API_SQLSETSTMTOPTION = 51, SQL_API_SQLSPECIALCOLUMNS = 52, SQL_API_SQLSTATISTICS = 53, SQL_API_SQLTABLES = 54, SQL_API_SQLTRANSACT = 23,

/* Information requested by SQLGetInfo() */ SQL_MAX_DRIVER_CONNECTIONS = 0, SQL_MAXIMUM_DRIVER_CONNECTIONS = SQL_MAX_DRIVER_CONNECTIONS, SQL_MAX_CONCURRENT_ACTIVITIES = 1, SQL_MAXIMUM_CONCURRENT_ACTIVITIES = SQL_MAX_CONCURRENT_ACTIVITIES, SQL_DATA_SOURCE_NAME = 2, SQL_FETCH_DIRECTION = 8, SQL_SERVER_NAME = 13, SQL_SEARCH_PATTERN_ESCAPE = 14, SQL_DBMS_NAME = 17, SQL_DBMS_VER = 18, SQL_ACCESSIBLE_TABLES = 19, SQL_ACCESSIBLE_PROCEDURES = 20, SQL_CURSOR_COMMIT_BEHAVIOR = 23, SQL_DATA_SOURCE_READ_ONLY = 25, SQL_DEFAULT_TXN_ISOLATION = 26, SQL_IDENTIFIER_CASE = 28, SQL_IDENTIFIER_QUOTE_CHAR = 29, SQL_MAX_COLUMN_NAME_LEN = 30, SQL_MAXIMUM_COLUMN_NAME_LENGTH = SQL_MAX_COLUMN_NAME_LEN, SQL_MAX_CURSOR_NAME_LEN = 31, SQL_MAXIMUM_CURSOR_NAME_LENGTH = SQL_MAX_CURSOR_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN = 32, SQL_MAXIMUM_SCHEMA_NAME_LENGTH = SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_CATALOG_NAME_LEN = 34, SQL_MAXIMUM_CATALOG_NAME_LENGTH = SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_TABLE_NAME_LEN = 35, SQL_SCROLL_CONCURRENCY = 43, SQL_TXN_CAPABLE = 46, SQL_TRANSACTION_CAPABLE = SQL_TXN_CAPABLE, SQL_USER_NAME = 47, SQL_TXN_ISOLATION_OPTION = 72, SQL_TRANSACTION_ISOLATION_OPTION = SQL_TXN_ISOLATION_OPTION, SQL_INTEGRITY = 73, SQL_GETDATA_EXTENSIONS = 81, SQL_NULL_COLLATION = 85, SQL_ALTER_TABLE = 86, SQL_ORDER_BY_COLUMNS_IN_SELECT = 90, SQL_SPECIAL_CHARACTERS = 94, SQL_MAX_COLUMNS_IN_GROUP_BY = 97, SQL_MAXIMUM_COLUMNS_IN_GROUP_BY = SQL_MAX_COLUMNS_IN_GROUP_BY, SQL_MAX_COLUMNS_IN_INDEX = 98, SQL_MAXIMUM_COLUMNS_IN_INDEX = SQL_MAX_COLUMNS_IN_INDEX, SQL_MAX_COLUMNS_IN_ORDER_BY = 99, SQL_MAXIMUM_COLUMNS_IN_ORDER_BY = SQL_MAX_COLUMNS_IN_ORDER_BY, SQL_MAX_COLUMNS_IN_SELECT = 100, SQL_MAXIMUM_COLUMNS_IN_SELECT = SQL_MAX_COLUMNS_IN_SELECT, SQL_MAX_COLUMNS_IN_TABLE = 101, SQL_MAX_INDEX_SIZE = 102, SQL_MAXIMUM_INDEX_SIZE = SQL_MAX_INDEX_SIZE, SQL_MAX_ROW_SIZE = 104, SQL_MAXIMUM_ROW_SIZE = SQL_MAX_ROW_SIZE, SQL_MAX_STATEMENT_LEN = 105, SQL_MAXIMUM_STATEMENT_LENGTH = SQL_MAX_STATEMENT_LEN, SQL_MAX_TABLES_IN_SELECT = 106, SQL_MAXIMUM_TABLES_IN_SELECT = SQL_MAX_TABLES_IN_SELECT, SQL_MAX_USER_NAME_LEN = 107, SQL_MAXIMUM_USER_NAME_LENGTH = SQL_MAX_USER_NAME_LEN, SQL_OJ_CAPABILITIES = 115, SQL_OUTER_JOIN_CAPABILITIES = SQL_OJ_CAPABILITIES,

SQL_XOPEN_CLI_YEAR = 10000, SQL_CURSOR_SENSITIVITY = 10001, SQL_DESCRIBE_PARAMETER = 10002, SQL_CATALOG_NAME = 10003, SQL_COLLATION_SEQ = 10004, SQL_MAX_IDENTIFIER_LEN = 10005, SQL_MAXIMUM_IDENTIFIER_LENGTH = SQL_MAX_IDENTIFIER_LEN,

SQL_AT_ADD_COLUMN = #00000001, SQL_AT_DROP_COLUMN = #00000002,

SQL_AT_ADD_CONSTRAINT = #00000008,

/* The following bitmasks are ODBC extensions and defined in sqlext.h SQL_AT_COLUMN_SINGLE = #00000020, SQL_AT_ADD_COLUMN_DEFAULT = #00000040, SQL_AT_ADD_COLUMN_COLLATION = #00000080, SQL_AT_SET_COLUMN_DEFAULT = #00000100, SQL_AT_DROP_COLUMN_DEFAULT = #00000200, SQL_AT_DROP_COLUMN_CASCADE = #00000400, SQL_AT_DROP_COLUMN_RESTRICT = #00000800, SQL_AT_ADD_TABLE_CONSTRAINT = #00001000, SQL_AT_DROP_TABLE_CONSTRAINT_CASCADE = #00002000, SQL_AT_DROP_TABLE_CONSTRAINT_RESTRICT = #00004000, SQL_AT_CONSTRAINT_NAME_DEFINITION = #00008000, SQL_AT_CONSTRAINT_INITIALLY_DEFERRED = #00010000, SQL_AT_CONSTRAINT_INITIALLY_IMMEDIATE = #00020000, SQL_AT_CONSTRAINT_DEFERRABLE = #00040000, SQL_AT_CONSTRAINT_NON_DEFERRABLE = #00080000,

/* SQL_ASYNC_MODE values */ SQL_AM_NONE = 0, SQL_AM_CONNECTION = 1, SQL_AM_STATEMENT = 2,

/* SQL_CURSOR_COMMIT_BEHAVIOR values */ SQL_CB_DELETE = 0, SQL_CB_CLOSE = 1, SQL_CB_PRESERVE = 2,

/* SQL_FETCH_DIRECTION bitmasks */ SQL_FD_FETCH_NEXT = #00000001, SQL_FD_FETCH_FIRST = #00000002, SQL_FD_FETCH_LAST = #00000004, SQL_FD_FETCH_PRIOR = #00000008, SQL_FD_FETCH_ABSOLUTE = #00000010, SQL_FD_FETCH_RELATIVE = #00000020,

/* SQL_GETDATA_EXTENSIONS bitmasks */ SQL_GD_ANY_COLUMN = #00000001, SQL_GD_ANY_ORDER = #00000002,

/* SQL_IDENTIFIER_CASE values */ SQL_IC_UPPER = 1, SQL_IC_LOWER = 2, SQL_IC_SENSITIVE = 3, SQL_IC_MIXED = 4,

/* SQL_OJ_CAPABILITIES bitmasks */ /* NB: this means 'outer join', not what you may be thinking */

SQL_OJ_LEFT = #00000001, SQL_OJ_RIGHT = #00000002, SQL_OJ_FULL = #00000004, SQL_OJ_NESTED = #00000008, SQL_OJ_NOT_ORDERED = #00000010, SQL_OJ_INNER = #00000020, SQL_OJ_ALL_COMPARISON_OPS = #00000040,

/* SQL_SCROLL_CONCURRENCY bitmasks */ SQL_SCCO_READ_ONLY = #00000001, SQL_SCCO_LOCK = #00000002, SQL_SCCO_OPT_ROWVER = #00000004, SQL_SCCO_OPT_VALUES = #00000008,

/* SQL_TXN_CAPABLE values */ SQL_TC_NONE = 0, SQL_TC_DML = 1, SQL_TC_ALL = 2, SQL_TC_DDL_COMMIT = 3, SQL_TC_DDL_IGNORE = 4,

/* SQL_TXN_ISOLATION_OPTION bitmasks */ SQL_TXN_READ_UNCOMMITTED = #00000001, SQL_TRANSACTION_READ_UNCOMMITTED = SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED = #00000002, SQL_TRANSACTION_READ_COMMITTED = SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ = #00000004, SQL_TRANSACTION_REPEATABLE_READ = SQL_TXN_REPEATABLE_READ, SQL_TXN_SERIALIZABLE = #00000008, SQL_TRANSACTION_SERIALIZABLE = SQL_TXN_SERIALIZABLE,

/* SQL_NULL_COLLATION values */ SQL_NC_HIGH = 0, SQL_NC_LOW = 1,

/* env attribute */ SQL_ATTR_ODBC_VERSION = 200, SQL_ATTR_CONNECTION_POOLING = 201, SQL_ATTR_CP_MATCH = 202,

SQL_OV_ODBC2 = 2, SQL_OV_ODBC3 = 3,

/* generally useful constants */ SQL_SPEC_MAJOR =3 ,

/* Major version of specification */ SQL_SPEC_MINOR = 51, /* Minor version of specification */ SQL_SPEC_STRING ="03.51" ,

/* String constant for version */

SQL_SQLSTATE_SIZE = 5 , /* size of SQLSTATE */, SQL_MAX_DSN_LENGTH = 32 ,

/* maximum data source name size */

SQL_MAX_OPTION_STRING_LENGTH = 256,

SQL_FETCH_FIRST_USER = 31, SQL_FETCH_FIRST_SYSTEM = 32,

/* return code SQL_NO_DATA_FOUND is the same as SQL_NO_DATA */ SQL_NO_DATA_FOUND = SQL_NO_DATA,

SQL_PARAM_INPUT = 1, SQL_PARAM_OUTPUT = 4,

statement attributes SQL_QUERY_TIMEOUT = 0, SQL_MAX_ROWS = 1, SQL_NOSCAN = 2, SQL_MAX_LENGTH = 3, SQL_ASYNC_ENABLE = 4, SQL_BIND_TYPE = 5, SQL_CURSOR_TYPE = 6, SQL_CONCURRENCY = 7, SQL_KEYSET_SIZE = 8, SQL_ROWSET_SIZE = 9, SQL_SIMULATE_CURSOR = 10, SQL_RETRIEVE_DATA = 11, SQL_USE_BOOKMARKS = 12, SQL_GET_BOOKMARK = 13, SQL_ROW_NUMBER = 14,

-- SQL_CURSOR_TYPE options SQL_CURSOR_FORWARD_ONLY = 0, SQL_CURSOR_KEYSET_DRIVEN = 1, SQL_CURSOR_DYNAMIC = 2, SQL_CURSOR_STATIC = 3, SQL_CURSOR_TYPE_DEFAULT = SQL_CURSOR_FORWARD_ONLY, SQL_CONCURRENCY options SQL_CONCUR_READ_ONLY = 1, SQL_CONCUR_LOCK = 2, SQL_CONCUR_ROWVER = 3, SQL_CONCUR_VALUES = 4, SQL_CONCUR_DEFAULT = SQL_CONCUR_READ_ONLY,

connection attributes SQL_ACCESS_MODE = 101, SQL_AUTOCOMMIT = 102, SQL_LOGIN_TIMEOUT = 103, SQL_OPT_TRACE = 104, SQL_OPT_TRACEFILE = 105, SQL_TRANSLATE_DLL = 106, SQL_TRANSLATE_OPTION = 107, SQL_TXN_ISOLATION = 108, SQL_CURRENT_QUALIFIER = 109, SQL_ODBC_CURSORS = 110, SQL_QUIET_MODE = 111, SQL_PACKET_SIZE = 112,

-- SQL_AUTOCOMMIT options SQL_AUTOCOMMIT_OFF = 0, SQL_AUTOCOMMIT_ON = 1, SQL_AUTOCOMMIT_DEFAULT = SQL_AUTOCOMMIT_ON,

whether an attribute is a pointer or not

#if (ODBCVER >= 0x0300) SQL_IS_POINTER = (-4), SQL_IS_UINTEGER = (-5), SQL_IS_INTEGER = (-6), SQL_IS_USMALLINT = (-7), SQL_IS_SMALLINT = (-8), -- #endif /* ODBCVER >= 0x0300 */

Operations in SQLSetPos SQL_POSITION = 0, SQL_REFRESH = 1, SQL_UPDATE = 2, SQL_DELETE = 3,

Lock options in SQLSetPos SQL_LOCK_NO_CHANGE = 0, SQL_LOCK_EXCLUSIVE = 1, SQL_LOCK_UNLOCK = 2,

-- SQLBindParameter extensions SQL_DEFAULT_PARAM = (-5), SQL_IGNORE = (-6), #if (ODBCVER >= 0x0300) SQL_COLUMN_IGNORE = SQL_IGNORE,

#endif /* ODBCVER >= 0x0300 */ SQL_LEN_DATA_AT_EXEC_OFFSET = (-100), -- SQL_LEN_DATA_AT_EXEC(length) (-(length)+SQL_LEN_DATA_AT_EXEC_OFFSET)

SQL_ASYNC_MODE = 10021,

SQL_ATTR_ASYNC_ENABLE = 4,

values for SQL_ATTR_ASYNC_ENABLE SQL_ASYNC_ENABLE_OFF = 0, SQL_ASYNC_ENABLE_ON = 1, SQL_ASYNC_ENABLE_DEFAULT = SQL_ASYNC_ENABLE_OFF,

SQL_DRIVER_NOPROMPT = 0, SQL_DRIVER_COMPLETE = 1, SQL_DRIVER_PROMPT = 2, SQL_DRIVER_COMPLETE_REQUIRED = 3

integer henv, current_handle, max_records object VOID

current_handle = 0

global sequence last_datatype

last_datatype = {}

max_records = -1

sequence handle_odbc, handle_children, handle_type, handle_parent, handle_params, handle_cols handle_odbc = {} handle_children = {} handle_type = {} handle_parent = {} handle_params = {} handle_cols = {}

constant blank_params = repeat( {}, 2 ), HP_PARAMVALUE = 1, HP_STRLEN = 2,

HC_PTR = 1, HC_SIZE = 2, HC_STRLEN = 3, HC_DATATYPE = 4, HC_VALUE = 5

SQL_SUCCEEDED(rc) (((rc)&(~1))==0) constant not1 = not_bits(1),

dt_names = { "CHAR(255)", "VARCHAR(255)", "NUMERIC", "DECIMAL", "INTEGER", "SMALLINT", "FLOAT", "REAL", "DOUBLE", "DATETIME", "VARCHAR"},

dt_vals = { 0,1,2,3,4,5,6,7,8,9,12 }

/topic API

/func dataTypeName( integer dt )

Returns the name of an ODBC datatype that can be used in a sql create statement. global function dataTypeName( integer dt ) integer ix ix = find( dt, dt_vals )

if ix then return dt_names[ix] else return dt_names[1] end if end function

/topic API /func SQL_SUCCEEDED( atom rc )

 Tests for a successful return code from a call to an ODBC API function. global function SQL_SUCCEEDED( atom rc ) return (rc = 0) or (rc = 1) end function

function peek2s( atom a ) atom x x = peek(a) + peek(a+1) * #100 if x > #8000 then x -= #10000 end if return x end function

function peek_string(atom a)

 V0.56 Al Getz integer l sequence s Beg JT01 atom idx

 End JT01

Only deal with non-zero addresses if a then

Beg JT01 if platform() = LINUX then idx = a while idx do if peek(idx) = #00 then s = peek({a, (idx-a)}) exit else idx += 1 end if end while else End JT01 l = c_func(xlstrlen,{a}) s = peek({a, l})

 Beg JT01 end if End JT01 else s = {} end if

 send back all the bytes found. return s

end function

/topic API

/proc setMaxRecords( atom max )

Sets the maximum number of records that will be returned by /getData(). global procedure setMaxRecords( atom max ) max_records = max end procedure

/topic API

/func getCurrentHandle( )

Returns the current ODBC handle in use. global function getCurrentHandle( ) return current_handle end function

global function getHandleODBC( integer id ) if id > length( handle_odbc ) or handle_odbc[id] = -1 then return 0 end if

return handle_odbc[id] end function

/topic API

/func getErrorODBC( atom handle )

Returns a sequence containing the error number as the first element, and a destription of the error as the second element.

global function getErrorODBC( atom handle ) atom sqlstate, ne, msg, tlp integer ok, htype

object errno sequence error

if handle < 0 then handle = - handle end if htype = handle_type[handle]

sqlstate = allocate( 6 ) poke( sqlstate, repeat( 0, 6) ) ne = allocate( 4 ) msg = allocate( 1024 ) tlp = allocate( 4 )

ok = c_func( SQLGetDiagRec, { htype, getHandleODBC( handle ), 1, sqlstate, ne, msg, 1024, tlp } ) error = sprintf("[%s]", peek( sqlstate & 5 )) if ok = SQL_INVALID_HANDLE then errno = 0 error &= " Invalid ODBC Handle" elsif ok = SQL_NO_DATA then errno = 0 error &= " No Data" elsif ok = SQL_STILL_EXECUTING then errno = 0 error &= " Still Executing"

elsif ok = SQL_NEED_DATA then errno = 0 error &= " Need Data"

else error = peek( {sqlstate, 5} )

errno = value(error) errno = errno[2]

error &= 32 & peek_string( msg ) if not error[1] then error = error[7..length( error )] end if

end if free( sqlstate ) free( ne ) free( msg ) free( tlp ) return { errno, error } end function

/topic API /proc odbcError( atom handle )

 Displays the error result returned by the ODBC driver to STDERR. global procedure odbcError( atom handle ) sequence e integer ok e = getErrorODBC( handle )

printf(2, "ODBC Error %d: %s\n", e)

end procedure

function allocHandleODBC( integer htype, integer parent ) integer id, mset atom ptr, hparent

if parent > length( handle_odbc ) then return 0 end if

if parent and parent <= length(handle_odbc) and handle_odbc[parent]

-1 then

return 0 end if

id = find( -1, handle_odbc ) if not id then handle_odbc &= 0 handle_children &= handle_type &= 0 handle_parent &= 0 handle_params = append( handle_params, blank_params ) handle_cols &= id = length( handle_odbc ) end if

mset = new_memset() ptr = allocate( 4 )

if parent then hparent = getHandleODBC( parent ) else hparent = 0 end if VOID = c_func( SQLAllocHandle, {htype, hparent, ptr} ) handle_odbc[id] = peek4u( ptr ) handle_type[id] = htype

free( ptr )

if parent then handle_children[parent] &= id handle_parent[id] = parent end if

return id end function

/topic API

/proc freeStmt( integer id, atom flags )

SQLFreeStmt stops processing associated with a specific statement, closes any open cursors associated with the statement, discards pending

results, or, optionally, frees all resources associated with the statement handle. Valid flags are:

<ul> /li /b SQL_CLOSE: Closes the cursor associated with StatementHandle

(if one was defined) and discards all pending results. The application can reopen this cursor later by executing a SELECT statement again with

the same or different parameter values. If no cursor is open, this option has no effect for the application. SQLCloseCursor can also be called to

close a cursor. For more information, see Closing the Cursor.

/li /b SQL_DROP: This option is deprecated. A call to SQLFreeStmt with an Option of SQL_DROP is mapped in the Driver Manager to SQLFreeHandle.

 /li /b SQL_UNBIND: Sets the SQL_DESC_COUNT field of the ARD to 0, releasing all

column buffers bound by SQLBindCol for the given StatementHandle. This does not unbind the bookmark column; to do that, the SQL_DESC_DATA_PTR field of the

ARD for the bookmark column is set to NULL. Note that if this operation is performed on an explicitly allocated descriptor that is shared by more than

one statement, the operation will affect the bindings of all statements that share the descriptor.

 /li /b SQL_RESET_PARAMS: Sets the SQL_DESC_COUNT field of the APD to 0, releasing

all parameter buffers set by SQLBindParameter for the given StatementHandle. If this operation is performed on an explicitly allocated descriptor that is shared by

more than one statement, this operation will affect the bindings of all the statements that share the descriptor. For more information, see Binding Parameters.

</ul> global procedure freeStmt( integer id, atom flags )

if and_bits( flags, SQL_RESET_PARAMS ) then for i = 1 to length( handle_params[id][1] ) do for j = 1 to length(handle_params[id]) do if handle_params[id][j][i] then free( handle_params[id][j][i] ) end if end for end for handle_params[id] = blank_params elsif and_bits( flags, SQL_UNBIND ) then for i = 1 to length(handle_cols[id]) do free( handle_cols[id][i][HC_PTR] ) free( handle_cols[id][i][HC_STRLEN] ) end for handle_cols[id] = {} end if

end procedure

/topic API

/proc freeHandleODBC( integer id )

Frees an ODBC handle. global procedure freeHandleODBC( integer id ) atom handle integer ix

if id < 0 then id = - id end if

if not id then id = current_handle end if

handle = getHandleODBC( id ) if not handle then return end if

for i = 1 to length( handle_children[id] ) do freeHandleODBC( handle_children[id][i] ) end for

if handle_type[id] = SQL_HANDLE_STMT then freeStmt( id, SQL_RESET_PARAMS ) freeStmt( id, SQL_UNBIND ) end if

VOID = c_func( SQLFreeHandle, { handle_type[id], handle_odbc[id] } ) handle_odbc[id] = -1

if id = current_handle then current_handle = 0 end if

end procedure

/topic API

/func dataSourcesODBC( )

Returns the User Data Sources. The return sequence is the form of: /code

 {{ "DSN Name 1", "Description / Type" }, { "DSN Name 2", "Description / Type" }}

/endcode The first element is the text to pass to /openConnectionODBC() global function dataSourcesODBC( ) atom server, nl1, desc, nl2 integer mset, state sequence dsn

 mset = new_memset()

server = allocate( SQL_MAX_DSN_LENGTH ) desc = allocate( SQL_MAX_DSN_LENGTH * 2 ) nl1 = allocate( 4 ) nl2 = allocate( 4 )

dsn = {}

state = c_func( SQLDataSources, { getHandleODBC(henv), SQL_FETCH_FIRST, server, SQL_MAX_DSN_LENGTH, nl1, desc, SQL_MAX_DSN_LENGTH*2,nl2})

dsn &= { {peek_string( server ), peek_string( desc ) }}

while state = SQL_SUCCESS do not find( state, { SQL_NO_DATA, SQL_ERROR }) do

state = c_func( SQLDataSources, { getHandleODBC(henv), SQL_FETCH_NEXT, server, SQL_MAX_DSN_LENGTH, nl1, desc, SQL_MAX_DSN_LENGTH*2,nl2})

Beg JT01 if state = SQL_SUCCESS then End JT01 dsn &= { {peek_string( server ), peek_string( desc ) }}

 Beg JT01 end if End JT01 end while free( server ) free( desc ) free( nl1 ) free( nl2 )

return dsn end function

/topic API /func openConnectionODBC( sequence server, sequence user, sequence authentication )

 Establishes a connection to a DSN. Returns the integer id of the connection. global function openConnectionODBC( sequence server, sequence user, sequence authentication ) atom s_ptr, h_ptr, hconn, u_ptr, a_ptr, ok integer mset

hconn = allocHandleODBC( SQL_HANDLE_DBC, henv )

if not hconn then

return 0 end if current_handle = hconn

h_ptr = allocate( 4 ) s_ptr = allocate_string( server ) u_ptr = allocate_string( user ) a_ptr = allocate_string( authentication )

ok = c_func( SQLConnect, { getHandleODBC(hconn), s_ptr, length( server ) + 1, u_ptr, length( user ) + 1, a_ptr, length( authentication ) + 1 })

free( h_ptr ) free( s_ptr ) free( u_ptr ) free( a_ptr )

if not SQL_SUCCEEDED( ok ) then

odbcError( hconn ) return -hconn end if

return hconn end function

SQLRETURN SQLDriverConnect(

SQLHDBC ConnectionHandle, SQLHWND WindowHandle,

 SQLCHAR * InConnectionString, SQLSMALLINT StringLength1,

 SQLCHAR * OutConnectionString, SQLSMALLINT BufferLength,

 SQLSMALLINT * StringLength2Ptr, SQLUSMALLINT DriverCompletion);

/topic API /func openDriverConnectionODBC( sequence connect_string, atom hwnd, atom driver_completion )

 SQLDriverConnect is an alternative to SQLConnect. It supports data

sources that require more connection information than the three arguments in SQLConnect, dialog boxes to prompt the user for all connection information,

and data sources that are not defined in the system information. /code

A connection string has the following syntax: connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string

empty-string ::= attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]

attribute-keyword ::= DSN | UID | PWD | driver-defined-attribute-keyword

attribute-value ::= character-string driver-defined-attribute-keyword ::= identifier

/endcode <table border=1>

<tr><td> /b Keyword</td><td><strong>Attribute value description</strong></td></tr> <tr><td> /b DSN</td><td> Name of a data source as returned by SQLDataSources or the data

sources dialog box of SQLDriverConnect. </td></tr> <tr><td> /b FILEDSN</td><td> Name of a .dsn file from which a connection string

will be built for the data source. These data sources are called file data sources.</td></tr> <tr><td> /b DRIVER</td><td> Description of the driver as returned by the SQLDrivers function.

For example, Rdb or SQL Server. </td></tr> <tr><td> /b UID </td><td>A user ID. </td></tr>

<tr><td> /b PWD</td><td> The password corresponding to the user ID, or an empty string if there is no password for the user ID (PWD=;). </td></tr>

<tr><td> /b SAVEFILE</td><td> The file name of a .dsn file in which the attribute values of keywords used in making the present, successful connection should be saved. </td></tr></table>

 /b hwnd enables the Driver Manager to display dialogs (if required and allowed) to prompt the

user for additional information to build the connection string.

The Driver Manager constructs a connection string to pass to the driver in the /b connect_string argument of the driver's SQLDriverConnect function. The Driver Manager does not modify the

InConnectionString argument passed to it by the application.

The action of the Driver Manager is based on the value of the /b driver_completion argument: <ul>

/li /b SQL_DRIVER_PROMPT: If the connection string does not contain either the DRIVER, DSN, or FILEDSN keyword, the Driver Manager displays the Data Sources dialog box. It constructs a

connection string from the data source name returned by the dialog box and any other keywords passed to it by the application. If the data source name returned by the dialog box is empty,

the Driver Manager specifies the keyword-value pair DSN=Default. (This dialog box will not display a data source with the name "Default".)

 /li /b SQL_DRIVER_COMPLETE or /b SQL_DRIVER_COMPLETE_REQUIRED: If the connection string

specified by the application includes the DSN keyword, the Driver Manager copies the connection string specified by the application. Otherwise, it takes the same actions as

it does when /b driver_completion is SQL_DRIVER_PROMPT.

/li /b SQL_DRIVER_NOPROMPT: The Driver Manager copies the connection string specified by the application. </ul> global sequence last_connect_string last_connect_string = "" global function openDriverConnectionODBC( sequence connect_string, atom hwnd, atom driver_completion ) atom hconn, instr, outstr, strlen2, ok integer strlen

hconn = allocHandleODBC( SQL_HANDLE_DBC, henv ) if not hconn then return -henv end if

instr = allocate_string( connect_string ) outstr = allocate( 2048 ) strlen2 = allocate( 4 )

ok = c_func( SQLDriverConnect, { getHandleODBC(hconn), hwnd, instr, length(connect_string), outstr, 2048, strlen2, driver_completion } )

strlen = peek2s( strlen2 ) last_connect_string = peek_string( outstr )

free( instr ) free( outstr ) free( strlen2 )

if not SQL_SUCCEEDED(ok) then hconn = -hconn end if

return hconn

end function

 Beg JT02 /topic API

/func getColAttribute( atom hstmt )

Get a specific column attribute global function getColAttribute(atom hstmt, atom column, atom field)

atom xattribute, attribute, hs, ok

if not hstmt then hstmt = current_handle else current_handle = hstmt end if

hs = getHandleODBC( hstmt)

xattribute = allocate( 4) ok = c_func(SQLColAttribute, {hs, column, field, 0, 0, 0, xattribute})

if not SQL_SUCCEEDED( ok ) then free( xattribute ) return -hstmt end if

attribute = peek4u( xattribute ) free( xattribute )

return attribute

end function End JT02

/topic API /func getColumnHeaders( atom hstmt )

Pass the handle for the statement in question, and a sequence of column headers will be returned. If hstmt is 0, uses the current handle. The global sequence

/b last_datatype will contain the data types for each column. global function getColumnHeaders( atom hstmt ) object data, data_type atom h_ptr, ok, mset, handle, ptr1, ptr2, ptr3, ptr4, ptr5, ptr6, ptr7, len, hs, rec_count integer cols, row

if not hstmt then hstmt = current_handle else current_handle = hstmt end if

hs = getHandleODBC( hstmt)

h_ptr = allocate( 4 ) poke4( h_ptr, 0 ) ok = c_func( SQLNumResultCols, { hs, h_ptr } )

if not SQL_SUCCEEDED( ok ) then odbcError( hstmt ) free( h_ptr ) return -hstmt end if

cols = peek4u( h_ptr ) free( h_ptr)

data = repeat( {}, cols ) data_type = repeat( 0, cols )

ptr1 = allocate( 256 ) ptr2 = allocate( 4 ) ptr3 = allocate( 4 ) ptr4 = allocate( 4 ) ptr5 = allocate( 4 ) ptr6 = allocate( 4 ) poke4( ptr3, 0 )

for i = 1 to cols do ok = c_func( SQLDescribeCol, { hs, i, ptr1, 256, ptr2, ptr3, ptr4, ptr5, ptr6 } )

data[i] = peek_string( ptr1 ) data_type[i] = peek2s( ptr3 ) end for

last_datatype = data_type free( ptr1 ) free( ptr2 ) free( ptr3 ) free( ptr4 ) free( ptr5 ) free( ptr6 )

return data end function

function sql_to_c_dt( atom dt ) if find(dt, {SQL_CHAR, SQL_VARCHAR}) then return SQL_C_CHAR elsif dt = SQL_NULL_DATA then

return SQL_C_CHAR

elsif find(dt, {SQL_NUMERIC, SQL_DECIMAL}) then return SQL_C_DOUBLE elsif dt = SQL_INTEGER then

return SQL_C_LONG

elsif dt = SQL_FLOAT then return SQL_C_FLOAT

elsif dt = SQL_REAL then return SQL_C_FLOAT

elsif dt = SQL_DOUBLE then return SQL_C_DOUBLE end if

return SQL_C_CHAR end function

function convertData( object str, atom dt ) sequence data integer ix, dx atom int

if find(dt, {SQL_CHAR, SQL_VARCHAR}) then return str elsif dt = SQL_NULL_DATA then

Josef Jindra: if length (str) > 1 then

str = str[1..length(str)-1] else str = {} end if

elsif find(dt, {SQL_NUMERIC, SQL_DECIMAL}) then str = value( str ) str = str[2] elsif dt = SQL_INTEGER then

Beg JT02 str = bytes_to_int( str ) return str

elsif dt = SQL_SMALLINT then

if length(str) < 2 then if not length(str) then str = 0 else str = str[1] end if else str = str[1] + 256 * str[2] if str > #7FFF then str = and_bits(not_bits(str) + 1, #FFFF) str = -str end if end if

elsif dt = SQL_BIT then

return str[1]

 End JT02 elsif dt = SQL_FLOAT then

str = float32_to_atom( str )

elsif dt = SQL_REAL then

str = float32_to_atom( str )

elsif dt = SQL_DOUBLE then str = float64_to_atom( str )

v1.31.2 4/16/04: elsif dt = SQL_TYPE_DATE or dt = SQL_TYPE_TIME then

data = repeat( 0, 3 ) if not length(str) then return data end if

data[1] = str[1] + str[2] * #100 data[2] = str[3] data[3] = str[5]

str = data str = sprintf("%04d-%02d-%02d",data)

elsif dt = SQL_TYPE_TIME then

elsif dt = SQL_TYPE_TIMESTAMP then data = repeat( 0, 6 ) data[1] = str[1] + str[2] * #100 data[2] = str[3] data[3] = str[5] data[4] = str[7] data[5] = str[9] data[6] = str[11] Beg JT02

str = data str = sprintf("%04d-%02d-%02d %02d:%02d:%02d",data) End JT02 end if

return str end function

function revertData( object val, atom dt ) sequence temp, num if find( dt, {SQL_CHAR, SQL_VARCHAR, SQL_NULL_DATA}) then if atom(val) then return {0,val} end if val &= 0 elsif find( dt, {SQL_NUMERIC, SQL_DECIMAL}) then if atom(val) then val = sprint(val) end if elsif dt = SQL_INTEGER then val = int_to_bytes( val ) elsif dt = SQL_FLOAT then val = atom_to_float32( val ) elsif dt = SQL_DOUBLE then val = atom_to_float64( val )

 v1.31.2: elsif dt = SQL_TYPE_DATE then if length(val) = 3 then val = sprintf("%04d-%02d-%02d", val) end if

elsif dt = SQL_TYPE_TIMESTAMP then temp = repeat( 0, 16 )

temp[2] = floor( val[1] / #100 ) temp[1] = val[1] - temp[2] temp[3] = val[2] temp[5] = val[3] temp[7] = val[4] temp[9] = val[5] temp[11] = val[6] val = temp

else return {0,val} end if return {1,val} end function

/topic API

/func getData( atom hstmt )

Return the next block of data from statement hstmt. Use hstmt = 0 to use the current handle. global function getData( atom hstmt ) object data, data_type atom h_ptr, ok, mset, handle, ptr1, ptr2, ptr3, ptr4, ptr5, ptr6, ptr7, len, hs, rec_count, mylen integer cols, row, ix, fetch sequence blank_row

if not hstmt then hstmt = current_handle else current_handle = hstmt end if

hs = getHandleODBC( hstmt)

h_ptr = allocate( 4 ) poke4( h_ptr, 0 ) ok = c_func( SQLNumResultCols, { hs, h_ptr } )

if not SQL_SUCCEEDED( ok ) then

odbcError( hstmt ) free( h_ptr ) return -hstmt end if

cols = peek4u( h_ptr ) free( h_ptr ) data = { } data_type = repeat( 0, cols )

mylen = 256 ptr1 = allocate( mylen ) ptr2 = allocate( 4 ) ptr3 = allocate( 4 ) ptr4 = allocate( 4 ) ptr5 = allocate( 4 ) ptr6 = allocate( 4 )

row = 1 for i = 1 to cols do poke4( ptr3, 0 ) ok = c_func( SQLDescribeCol, { hs, i, ptr1, 256, ptr2, ptr3, ptr4, ptr5, ptr6 } )

data_type[i] = peek2s( ptr3 )

end for

rec_count = 0 blank_row = repeat( {}, cols ) fetch = c_func( SQLFetch, { hs } ) while ( rec_count < max_records or max_records < 0 ) and SQL_SUCCEEDED( fetch ) do

rec_count += 1

data = append( data, blank_row ) row = length( data ) for i = 1 to cols do

ok = c_func( SQLGetData, { hs, i, SQL_C_DEFAULT, ptr1, mylen, ptr2 } )

len = peek4s( ptr2 )

if len = SQL_NULL_DATA then

else

if len <= mylen then if data_type[i] = SQL_INTEGER then if getColAttribute(hstmt, i, SQL_DESC_UNSIGNED)

SQL_TRUE then

data[row][i] = peek4u(ptr1) else data[row][i] = peek4s(ptr1) end if else data[row][i] = peek( { ptr1, len } ) end if else have to call repeatedly, since data was too big for the

buffer while len > mylen do data[row][i] &= peek( { ptr1, mylen - 1 } ) ok = c_func( SQLGetData, { hs, i, SQL_C_DEFAULT, ptr1, mylen, ptr2 } ) len = peek4s( ptr2 )

end while data[row][i] &= peek( { ptr1, len } ) end if

data[row][i] = convertData( data[row][i], data_type[i] )

end if end for Beg JT02 if (rec_count < max_records or max_records < 0) then

End JT02 fetch = c_func( SQLFetch, { hs } ) Beg JT02 end if

 End JT02 end while

free( ptr1 ) free( ptr2 ) free( ptr3 ) free( ptr4 ) free( ptr5 ) free( ptr6 )

return data end function

/topic API

/func runDirectODBC( atom hconn, sequence sql ) Same as /execDirectODBC(), but runDirectODBC() does not return any data. You

must use /getColumnHeaders() and /getData(). This function returns an ODBC statement handle, which must be freed by the application ( /freeHandleODBC). global function runDirectODBC( atom hconn, sequence sql ) sequence data, data_type atom hstmt, h_ptr, ok, mset, handle, ptr1, ptr2, ptr3, ptr4, ptr5, ptr6, ptr7, hs integer cols, row atom len

data = {}

 mset = new_memset() h_ptr = allocate( 4 )

hstmt = allocHandleODBC( SQL_HANDLE_STMT, hconn ) free( h_ptr ) if not hstmt then odbcError( hstmt ) return -hstmt end if

hs = getHandleODBC( hstmt ) h_ptr = allocate_string( sql )

ok = c_func( SQLExecDirect, { hs, h_ptr, length( sql ) }) free( h_ptr ) if not SQL_SUCCEEDED( ok ) then

return -hstmt end if

return hstmt

end function

/topic API /func execDirectODBC( atom hconn, sequence sql )

 Returns the results of query sql_text. This function ignores the settings made

by /setMaxRecords. If no errors occur, /execDirectODBC frees the statement handle it creates. If there is an error, the negative statement handle is returned, so

that the application can query for errors. The statment must then be freed by the application ( /freeHandleODBC) to avoid resource leakage. global function execDirectODBC( atom hconn, sequence sql ) object data atom hstmt, records

hstmt = runDirectODBC( hconn, sql )

if hstmt > 0 then current_handle = hstmt records = max_records max_records = -1 data = {getColumnHeaders( hstmt )} & getData( hstmt ) max_records = 1 else data = hstmt end if

 Josef Jindra: freeHandleODBC( hstmt )

return data end function

/topic API

/func prepareSQL( atom hconn, sequence sql )

Compiles a SQL statement for later execution and returns the ODBC statement handle id. Queries that will be run multiple times should typically be

prepared prior to excecution, and executed using /executeSQL(). This can save a significant amount of time for frequently run queries over

/execDirectODBC().

The statment handle must be freed by the application to avoid resource leakage ( /freeHandleODBC). global function prepareSQL( atom hconn, sequence sql ) atom hstmt, psql, ok

if handle_type[hconn] = SQL_HANDLE_STMT then hstmt = hconn else hstmt = allocHandleODBC( SQL_HANDLE_STMT, hconn ) if not hstmt then return -hconn end if end if

psql = allocate_string( sql )

ok = c_func( SQLPrepare, { getHandleODBC(hstmt), psql, length(sql) } ) free( psql )

if not SQL_SUCCEEDED( ok ) then return -hstmt end if

return hstmt end function

/topic API

/func executeSQL( atom hstmt )

Executes a SQL statement that has already been processed by /prepareSQL(). global function executeSQL( atom hstmt ) atom ok

if hstmt = 0 then hstmt = current_handle else current_handle = hstmt end if

ok = c_func( SQLExecute, {getHandleODBC(hstmt)}) if not SQL_SUCCEEDED( ok ) and ok != SQL_NO_DATA then return -hstmt end if

return hstmt end function

/topic API /func rollback( atom handle )

 Attempts to rollback the current transaction to the last /commit. global function rollback( atom handle ) atom ok ok = c_func( SQLEndTran, {getHandleODBC(handle), handle_type[handle], SQL_ROLLBACK }) if not SQL_SUCCEEDED(ok) then handle = - handle end if return handle end function

/topic API /func commit( atom handle )

 Attempts to commit the current transaction. (See also /rollback) global function commit( atom handle ) atom ok ok = c_func( SQLEndTran, {getHandleODBC(handle), handle_type[handle], SQL_COMMIT} ) if not SQL_SUCCEEDED(ok) then handle = - handle end if return handle end function

SQLRETURN SQLBindParameter( SQLHSTMT StatementHandle,

 SQLUSMALLINT ParameterNumber, SQLSMALLINT InputOutputType,

 SQLSMALLINT ValueType, SQLSMALLINT ParameterType,

 SQLUINTEGER ColumnSize, SQLSMALLINT DecimalDigits,

 SQLPOINTER ParameterValuePtr, SQLINTEGER BufferLength,

 SQLINTEGER * StrLen_or_IndPtr);

/topic API

/func bindParameter( [very long parameter list] )

<strong>atom StatementHandle, integer ParameterNumber, integer InputOutputType, integer ValueType, integer ParameterType,

integer ColumnSize, integer DecimalDigits, object ParameterValue )</strong>

bindParameter binds a buffer to a parameter marker in an SQL statement. /b ParameterNumber refers to the nth parameter in the statement. Parameters

are referenced by the order in which they appear in the statement: /code

 SELECT NAME, ADDRESS, STATE FROM EMPLOYEES WHERE NAME LIKE ? AND AGE > ? /endcode

The value for NAME would be parameter 1, and the value for AGE would be parameter 2.

 <ul>

/li /b ValueType is the C data type (SQL_C_CHAR, SQL_C_LONG, SQL_C_DOUBLE, etc.)<br>

/li /b InputOutputType should always be SQL_PARAM_INPUT, unless the parameter calls a procedure

 /li /b ParameterType is the SQL data type (SQL_CHAR, SQL_VARCHAR, SQL_INTEGER,

SQL_NUMERIC, etc).

/li /b ColumnSize specifies the size of the column or expression corresponding to the parameter marker, the length of that data, or both.

<ul> /li If ParameterType is SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, SQL_BINARY, SQL_VARBINARY,

SQL_LONGVARBINARY, or one of the concise SQL datetime or interval data types, the SQL_DESC_LENGTH field of the IPD is set to the value of ColumnSize.

/li If ParameterType

new topic     » goto parent     » topic index » view thread      » older message » newer message

Search



Quick Links

User menu

Not signed in.

Misc Menu