1. ODBC formatting
- Posted by Mike777 <anon4321 at gmail?co?> Dec 18, 2007
- 617 views
I apologize if this posts twice. I can read my ODBC databases, but I'm having a tough time figuring out how to interpret somee of the data types. Anything coming through as an SQL_VARCHAR can be echoed to the screen with:
for i = 1 to length(myData) VOID= message_box(sprintf("Here is the data:[%s",mySequence[i][1])&"]","Test Data",0) next i
But I haven't figured out how to work with things that are numeric. Two examples. If I have an SQL_INTEGER (which is a 4) which has the value -693341829. And an SQL_SMALLINT (which is a 5) which has the value 20. They are displaying as: 3601625467 and 0. The display statement I'm using is (x is the field number being displayed): VOID= message_box(sprintf("Here is the data:[%d",mySequence[i][x])&"]","More Test Data",0) Is this just a fault in the ODBC driver that I'm using and therefore there is "no hope"? Or is there a format I can use to visually display these values properly? Thanks Mike
2. Re: ODBC formatting
- Posted by Matt Lewis <matthewwalkerlewis at gmail.?o?> Dec 18, 2007
- 600 views
Mike777 wrote: > > I apologize if this posts twice. > > I can read my ODBC databases, but I'm having a tough time figuring out how to > interpret somee of the data types. Anything coming through as an SQL_VARCHAR > can be echoed to the screen with: > }}} <eucode> > for i = 1 to length(myData) > VOID= message_box(sprintf("Here is the > data:[%s",mySequence[i][1])&"]","Test Data",0) > next i > </eucode> {{{ > > But I haven't figured out how to work with things that are numeric. Two > examples. > If I have an SQL_INTEGER (which is a 4) which has the value -693341829. And > an SQL_SMALLINT (which is a 5) which has the value 20. They are displaying as: > 3601625467 and 0. > > The display statement I'm using is (x is the field number being displayed): > > VOID= message_box(sprintf("Here is the data:[%d",mySequence[i][x])&"]","More > Test Data",0) > > Is this just a fault in the ODBC driver that I'm using and therefore there is > "no hope"? Or is there a format I can use to visually display these values > properly? 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
3. Re: ODBC formatting
- Posted by Mike777 <anon4321 at gm?il.c?m> Dec 18, 2007
- 601 views
- Last edited Dec 19, 2007
Matt Lewis wrote: > > Mike777 wrote: > > > > I can read my ODBC databases, but I'm having a tough time figuring out how > > to > > interpret somee of the data types. > > Is this just a fault in the ODBC driver that I'm using and therefore there > > is > > "no hope"? Or is there a format I can use to visually display these values > > properly? > > 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. Should I just plan on insisting that all data be text based (even numbers) or is there some wrapper modification I should consider (I'm certainly willing to edit odbc.e to change the way it deals with SQL_INTEGER and SQL_SMALLINT)? Mike
4. Re: ODBC formatting
- Posted by Jonas Temple <jtemple at yht?.net> Dec 18, 2007
- 609 views
- Last edited Dec 19, 2007
Mike777 wrote: > Should I just plan on insisting that all data be text based (even numbers) or > is there some wrapper modification I should consider (I'm certainly willing > to edit odbc.e to change the way it deals with SQL_INTEGER and SQL_SMALLINT)? > > Mike Mike, I tried posting a couple of hours ago with the "fix" for odbc.e to handle SQL_INTEGER and SQL_SMALLINT but it looks like it didn't go through. Email me privately at "jtemple at yhti dot net" I'll send you the modified odbc.e Matt, I'll send this to you as well. Jonas Temple http://www.innovativesys.net
5. Re: ODBC formatting
- Posted by Jonas Temple <jtemple at y?ti.?et> Dec 18, 2007
- 678 views
- Last edited Dec 19, 2007
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
Change Log
/topic Change Log
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 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 ),
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 ),
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 ),
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 */ /* flags for null-terminated string */ SQL_NTS = (-3), SQL_NTSL = (-3),
/* date/time length constants */ SQL_DATE_LEN =10, SQL_TIME_LEN = 8 ,
/* environment attribute */ SQL_ATTR_OUTPUT_NTS =10001,
/* 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),
Beg JT02
/* 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,
/* 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, /* One-parameter shortcuts for date/time data types */ SQL_TYPE_DATE =91, SQL_TYPE_TIME =92, SQL_TYPE_TIMESTAMP =93,
/* GetTypeInfo() request for all data types */ SQL_ALL_TYPES =0,
/* SQLGetData() code indicating that the application row descriptor
/* SQL date/time type subcodes */ SQL_CODE_DATE = 1, SQL_CODE_TIME = 2, SQL_CODE_TIMESTAMP= 3,
/* values of NULLABLE field in descriptor */ SQL_NO_NULLS = 0, SQL_NULLABLE = 1,
/* values of ALLOC_TYPE field in descriptor */ SQL_DESC_ALLOC_AUTO= 1, SQL_DESC_ALLOC_USER= 2,
/* Codes used for FetchOrientation in SQLFetchScroll(),
/* Other codes used for FetchOrientation in SQLFetchScroll() */ SQL_FETCH_LAST = 3, SQL_FETCH_PRIOR = 4, SQL_FETCH_ABSOLUTE= 5, SQL_FETCH_RELATIVE= 6,
/* null handles returned by SQLAllocHandle() */ SQL_NULL_HENV = 0, SQL_NULL_HDBC = 0, SQL_NULL_HSTMT = 0, SQL_NULL_HDESC = 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,
/* 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,
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_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_IDENTIFIER_CASE values */ SQL_IC_UPPER = 1, SQL_IC_LOWER = 2, SQL_IC_SENSITIVE = 3, SQL_IC_MIXED = 4,
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_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_NULL_COLLATION values */ SQL_NC_HIGH = 0, SQL_NC_LOW = 1,
SQL_OV_ODBC2 = 2, SQL_OV_ODBC3 = 3,
/* generally useful constants */ SQL_SPEC_MAJOR =3 ,
SQL_SQLSTATE_SIZE = 5 , /* size of SQLSTATE */, SQL_MAX_DSN_LENGTH = 32 ,
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_AUTOCOMMIT options SQL_AUTOCOMMIT_OFF = 0, SQL_AUTOCOMMIT_ON = 1, SQL_AUTOCOMMIT_DEFAULT = SQL_AUTOCOMMIT_ON, whether an attribute is a pointer or not
Operations in SQLSetPos SQL_POSITION = 0, SQL_REFRESH = 1, SQL_UPDATE = 2, SQL_DELETE = 3,
--
SQLBindParameter extensions SQL_DEFAULT_PARAM = (-5), SQL_IGNORE = (-6),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
if ix then return dt_names[ix] else return dt_names[1] end if 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)
Only deal with non-zero addresses if a then
end function
/topic API
/topic API
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
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 APIprintf(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
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
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
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 thenreturn dsn end function
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
return hconn end function
SQLRETURN SQLDriverConnect(
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
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
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 JT02elsif 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]
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
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 )
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
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
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
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
free( ptr1 ) free( ptr2 ) free( ptr3 ) free( ptr4 ) free( ptr5 ) free( ptr6 )
return data end function
/topic API
data = {}
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 APIhstmt = 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
return data end function
/topic API
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
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
6. Re: ODBC formatting
- Posted by Mike777 <anon4321 at gmai??com> Dec 18, 2007
- 616 views
- Last edited Dec 19, 2007
Jonas Temple wrote: > > 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... The latest version is 1.34 and you updated 1.33. I *think* that the only difference between 1.33 and 1.34 is the addition of the following: --v1.34 --* Added /closeConnectionODBC() (thanks to Andres Cabezas) -- That function is: --/topic API --/func closeConnectionODBC( atom hconn ) -- -- Close a database connection and release its resources. This procedure --automatically calls /freeHandleODBC() on the connection. global procedure closeConnectionODBC( atom hconn ) atom ok ok = c_func( SQLDisconnect, { getHandleODBC(hconn) } ) if SQL_SUCCEEDED( ok ) then freeHandleODBC( hconn ) end if end procedure Jonas, it appears, at first blush testing, to work splendidly. Thanks Mike
7. Re: ODBC formatting
- Posted by Matt Lewis <matthewwalkerlewis at gm?il.co?> Dec 19, 2007
- 629 views
Mike777 wrote: > > Matt Lewis wrote: > > > > Mike777 wrote: > > > > > > I can read my ODBC databases, but I'm having a tough time figuring out how > > > to > > > interpret somee of the data types. > > > > Is this just a fault in the ODBC driver that I'm using and therefore there > > > is > > > "no hope"? Or is there a format I can use to visually display these > > > values > > > properly? > > > > 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. > > Should I just plan on insisting that all data be text based (even numbers) or > is there some wrapper modification I should consider (I'm certainly willing > to edit odbc.e to change the way it deals with SQL_INTEGER and SQL_SMALLINT)? I haven't tested this, but the fix seems pretty straightforward. Try this file: http://eudbc.svn.sourceforge.net/viewvc/*checkout*/eudbc/trunk/odbc.e Matt