SQLTables function

[donkey]

I've been trying to extract a list of tables from an MSJet database using the ODBC API, however I don't get anything back but junk data. Has anyone ever used SQLTables successfully ?

Code Select Expand

TABLES STRUCT
Catalog DB 128 DUP (?)
cbCatalog DD ?
Schema DB 128 DUP (?)
cbSchema DD ?
Name DB 128 DUP (?)
cbName DD ?
Type DB 128 DUP (?)
cbType DD ?
Remarks DB 254 DUP (?)
cbRemarks DD ?
ENDS


// Note that ODBCConnect just connects to the database, since a statement handle is returned I have not included it here...

ODBCGetStatementHandle FRAME
LOCAL hStmt:D

invoke SQLAllocHandle, SQL_HANDLE_STMT, [odbcdata.hConn],addr hStmt
cmp ax, SQL_SUCCESS
je >
cmp ax, SQL_SUCCESS_WITH_INFO
jne >.ERROR
:
invoke SQLSetStmtAttr,[hStmt], SQL_ATTR_CONCURRENCY, SQL_CONCUR_ROWVER, 0
invoke SQLSetStmtAttr,[hStmt], SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN, 0
mov eax,[hStmt]
ret

.ERROR
invoke MessageBox, NULL, 'Unable to allocate statement handle.', 0, 0
xor eax,eax
ENDF

ODBCBindTableCols FRAME hStmt, lpData
uses ebx

mov ebx,[lpData]

lea eax,[ebx+TABLES.Catalog]
lea ecx,[ebx+TABLES.cbCatalog]
invoke SQLBindCol,[hStmt], 1, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+TABLES.Schema]
lea ecx,[ebx+TABLES.cbSchema]
invoke SQLBindCol,[hStmt], 2, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+TABLES.Name]
lea ecx,[ebx+TABLES.cbName]
invoke SQLBindCol,[hStmt], 3, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+TABLES.Type]
lea ecx,[ebx+TABLES.cbType]
invoke SQLBindCol,[hStmt], 4, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+TABLES.Remarks]
lea ecx,[ebx+TABLES.cbRemarks]
invoke SQLBindCol,[hStmt], 5, SQL_C_CHAR, eax, 254, ecx
ret

ENDF

ODBCGetTableList FRAME pszFilename
uses ebx
LOCAL hStmt:D
invoke ODBCConnect, [pszFilename]

invoke ODBCGetStatementHandle
mov [hStmt],eax

invoke GlobalAlloc,GMEM_FIXED,SIZEOF TABLES
mov ebx,eax

invoke ODBCBindTableCols, [hStmt], ebx

invoke SQLTables,[hStmt], NULL, 0, NULL, 0, NULL, 0, NULL,0

.READ_RECORD
mov B[ebx+TABLES.Catalog],0
mov B[ebx+TABLES.Schema],0
mov B[ebx+TABLES.Name],0
mov B[ebx+TABLES.Type],0
mov B[ebx+TABLES.Remarks],0

invoke SQLFetch,[hStmt]
cmp ax,SQL_SUCCESS
je >
cmp ax,SQL_SUCCESS_WITH_INFO
jne >.END_READ
:
cmp ax,SQL_NO_DATA
je >.END_READ
// Send the table name to the treeview here
jmp <.READ_RECORD
.END_READ
invoke SQLFreeHandle,SQL_HANDLE_STMT,[hStmt]
.EXIT

invoke GlobalFree, ebx
RET
ENDF

Thanks,
Donkey

[donkey]

Sorry about that all, the program works perfectly, I was forgetting to parse out the system tables and since their names look like junk data I thought thats what I was getting. Much further down the list the actual table names appeared. It should have looked like this...

Code Select Expand

ODBCGetTableList FRAME pszFilename
uses ebx
LOCAL hStmt:D
invoke ODBCConnect, [pszFilename]

invoke ODBCGetStatementHandle
mov [hStmt],eax

invoke GlobalAlloc,GMEM_FIXED,SIZEOF TABLES
mov ebx,eax

invoke ODBCBindTableCols, [hStmt], ebx

invoke SQLTables,[hStmt], NULL, 0, NULL, 0, NULL, 0, NULL,0

.READ_RECORD
mov B[ebx+TABLES.Catalog],0
mov B[ebx+TABLES.Schema],0
mov B[ebx+TABLES.Name],0
mov B[ebx+TABLES.Type],0
mov B[ebx+TABLES.Remarks],0

invoke SQLFetch,[hStmt]
cmp ax,SQL_SUCCESS
je >
cmp ax,SQL_SUCCESS_WITH_INFO
jne >.END_READ
:
cmp ax,SQL_NO_DATA
je >.END_READ
lea eax,[ebx+TABLES.Type]
invoke lstrcmp,eax,"SYSTEM TABLE"
test eax,eax
jz <.READ_RECORD
// Send the table name to the treeview here
jmp <.READ_RECORD
.END_READ
invoke SQLFreeHandle,SQL_HANDLE_STMT,[hStmt]
.EXIT

invoke GlobalFree, ebx
RET
ENDF
Thanks anyway ;)

[donkey]

This will get the column names and table name associated with the column. Very useful if you need to know the structure of an unknown database (as I do in my current project)...

Code Select Expand

COLUMNS STRUCT
Catalog DB 128 DUP (?)
cbCatalog DD ?
Schema DB 128 DUP (?)
cbSchema DD ?
TableName DB 128 DUP (?)
cbTableName DD ?
ColumnName DB 128 DUP (?)
cbColumnName DD ?
DataType DW ?
cbDataType DD ?
TypeName DB 128 DUP (?)
cbTypeName DD ?
ColumnSize DD ?
cbColumnSize DD ?
BufferLength DD ?
cbBufferLength DD ?
DecimalDigits DD ?
cbDecimalDigits DD ?
PrecRadix DD ?
cbPrecRadix DD ?
Nullable DD ?
cbNullable DD ?
Remarks DB 254 DUP (?)
cbRemarks DD ?
ColumnDef DB 254 DUP (?)
cbColumnDef DD ?
SQL_DATA_TYPE DW ?
cbSQL_DATA_TYPE DD ?
SQL_DATETIME_SUB DW ?
cbSQL_DATETIME_SUB DD ?
CHAR_OCTET_LENGTH DD ?
cbCHAR_OCTET_LENGTH DD ?
ORDINAL_POSITION DD ?
cbORDINAL_POSITION DD ?
IS_NULLABLE DB 128 DUP (?)
cbIS_NULLABLE DD ?
ENDS

ODBCBindColumnCols FRAME hStmt, lpData
uses ebx

mov ebx,[lpData]

lea eax,[ebx+COLUMNS.Catalog]
lea ecx,[ebx+COLUMNS.cbCatalog]
invoke SQLBindCol,[hStmt], 1, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+COLUMNS.Schema]
lea ecx,[ebx+COLUMNS.cbSchema]
invoke SQLBindCol,[hStmt], 2, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+COLUMNS.TableName]
lea ecx,[ebx+COLUMNS.cbTableName]
invoke SQLBindCol,[hStmt], 3, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+COLUMNS.ColumnName]
lea ecx,[ebx+COLUMNS.cbColumnName]
invoke SQLBindCol,[hStmt], 4, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+COLUMNS.DataType]
lea ecx,[ebx+COLUMNS.cbDataType]
invoke SQLBindCol,[hStmt], 5, SQL_C_USHORT, eax, 0, ecx

lea eax,[ebx+COLUMNS.TypeName]
lea ecx,[ebx+COLUMNS.cbTypeName]
invoke SQLBindCol,[hStmt], 6, SQL_C_CHAR, eax, 128, ecx

lea eax,[ebx+COLUMNS.ColumnSize]
lea ecx,[ebx+COLUMNS.cbColumnSize]
invoke SQLBindCol,[hStmt], 7, SQL_C_ULONG, eax, 0, ecx

lea eax,[ebx+COLUMNS.BufferLength]
lea ecx,[ebx+COLUMNS.cbBufferLength]
invoke SQLBindCol,[hStmt], 8, SQL_C_ULONG, eax, 0, ecx

lea eax,[ebx+COLUMNS.DecimalDigits]
lea ecx,[ebx+COLUMNS.cbDecimalDigits]
invoke SQLBindCol,[hStmt], 9, SQL_C_ULONG, eax, 0, ecx

lea eax,[ebx+COLUMNS.PrecRadix]
lea ecx,[ebx+COLUMNS.cbPrecRadix]
invoke SQLBindCol,[hStmt], 10, SQL_C_ULONG, eax, 0, ecx

lea eax,[ebx+COLUMNS.Nullable]
lea ecx,[ebx+COLUMNS.cbNullable]
invoke SQLBindCol,[hStmt], 11, SQL_C_ULONG, eax, 0, ecx

lea eax,[ebx+COLUMNS.Remarks]
lea ecx,[ebx+COLUMNS.cbRemarks]
invoke SQLBindCol,[hStmt], 12, SQL_C_CHAR, eax, 254, ecx

lea eax,[ebx+COLUMNS.ColumnDef]
lea ecx,[ebx+COLUMNS.cbColumnDef]
invoke SQLBindCol,[hStmt], 13, SQL_C_CHAR, eax, 254, ecx

lea eax,[ebx+COLUMNS.SQL_DATA_TYPE]
lea ecx,[ebx+COLUMNS.cbSQL_DATA_TYPE]
invoke SQLBindCol,[hStmt], 14, SQL_C_USHORT, eax, 0, ecx

lea eax,[ebx+COLUMNS.SQL_DATETIME_SUB]
lea ecx,[ebx+COLUMNS.cbSQL_DATETIME_SUB]
invoke SQLBindCol,[hStmt], 15, SQL_C_USHORT, eax, 0, ecx

lea eax,[ebx+COLUMNS.CHAR_OCTET_LENGTH]
lea ecx,[ebx+COLUMNS.cbCHAR_OCTET_LENGTH]
invoke SQLBindCol,[hStmt], 16, SQL_C_ULONG, eax, 0, ecx

lea eax,[ebx+COLUMNS.ORDINAL_POSITION]
lea ecx,[ebx+COLUMNS.cbORDINAL_POSITION]
invoke SQLBindCol,[hStmt], 17, SQL_C_ULONG, eax, 0, ecx

lea eax,[ebx+COLUMNS.IS_NULLABLE]
lea ecx,[ebx+COLUMNS.cbIS_NULLABLE]
invoke SQLBindCol,[hStmt], 18, SQL_C_CHAR, eax, 128, ecx

ret

ENDF

ODBCGetColumnList FRAME pszFilename
uses ebx
LOCAL hStmt:D
invoke ODBCConnect, [pszFilename]

invoke ODBCGetStatementHandle
mov [hStmt],eax

invoke GlobalAlloc,GMEM_FIXED,SIZEOF COLUMNS
mov ebx,eax

invoke ODBCBindTableCols, [hStmt], ebx

invoke SQLColumns,[hStmt], NULL, 0, NULL, 0, NULL, 0, NULL,0

.READ_RECORD
mov B[ebx+COLUMNS.Catalog],0
mov B[ebx+COLUMNS.Schema],0
mov B[ebx+COLUMNS.TableName],0
mov B[ebx+COLUMNS.ColumnName],0
mov B[ebx+COLUMNS.DataType],0
mov B[ebx+COLUMNS.TypeName],0
mov B[ebx+COLUMNS.ColumnSize],0
mov B[ebx+COLUMNS.BufferLength],0
mov B[ebx+COLUMNS.DecimalDigits],0
mov B[ebx+COLUMNS.PrecRadix],0
mov B[ebx+COLUMNS.Nullable],0
mov B[ebx+COLUMNS.Remarks],0
mov B[ebx+COLUMNS.ColumnDef],0
mov B[ebx+COLUMNS.SQL_DATA_TYPE],0
mov B[ebx+COLUMNS.SQL_DATETIME_SUB],0
mov B[ebx+COLUMNS.CHAR_OCTET_LENGTH],0
mov B[ebx+COLUMNS.ORDINAL_POSITION],0
mov B[ebx+COLUMNS.IS_NULLABLE],0

invoke SQLFetch,[hStmt]
cmp ax,SQL_SUCCESS
je >
cmp ax,SQL_SUCCESS_WITH_INFO
jne >.END_READ
:
cmp ax,SQL_NO_DATA
je >.END_READ

; data is ready to process

jmp <.READ_RECORD
.END_READ
invoke SQLFreeHandle,SQL_HANDLE_STMT,[hStmt]
.EXIT

invoke GlobalFree, ebx
RET
ENDF

[Draakie]

Thanks Donkey - I'am bookmarking this post.

Ta for the hard work.

Draakie