SQLColumns API Function

[pcawdron]

The following code works for SQLTables (currently rem'd out) but fails for SQLColumns. Any ideas why??? The return code is 100 SQL_No_Data_Found. I've run this on an AS400 Data Source as well as SQL Server.

Function Tables()
Dim R As Integer, henv As Long, hdbc As Long, hstmt As Long, Result As String * 128, Length As Long
R = SQLAllocEnv(henv)
R = SQLAllocConnect(henv, hdbc)
R = SQLConnect(hdbc, "DSN_Name", SQL_NTS, "", 0, "", 0)
R = SQLAllocStmt(hdbc, hstmt)
' R = SQLTables(hstmt, Null, 0, Null, 0, Null, 0, Null, 0)
R = SQLColumns(hstmt, Null, 0, Null, 0, "SYGHREP", SQL_NTS, Null, 0)
R = SQLFetch(hstmt)
Do While R = SQL_SUCCESS
R = SQLGetData(hstmt, 3, SQL_C_CHAR, Result, 128, Length)
If Length > 0 Then Debug.Print Left(Result, Length)
R = SQLFetch(hstmt)
Loop
R = SQLFreeStmt(hstmt, SQL_DROP)
End Function

Thanks,

pcawdron@gmac.com.au

PS. This Question is also posted under Visual Basic
[sig][/sig]

 

[foxdev]

Although you didn't need to specify it on SQLTables, try specifying the database name on SQLColumns and see if that helps. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[pcawdron]

I have used a recordset to try all the following possibilities but the return code is either 100 SQL_No_Data_to_Fetch or, on the odd occassion when I do it a 0 SQL_Success SQLFetch returns a zero length string.

Schema Name Database Name Table Name Column Name

Schema
Schema Database
Schema Database Table
Schema Database Table Column
Column
Table Column
Database Table Column
Schema Table Column
Schema Database Column
Schema Column
Column
Database
Table
Schema Database %
Schema Database %
Schema Database % %
Schema % % %
% % % %

Does anyone know where I'm going wrong??? [sig][/sig]

 

[vasanthgnb]

Where can i have the value of constants like SQL_SUCCESS,SQL_SUCCESS_WITH_INFO, and values like SQL_HANDLE_ENV,SQL_HANDLE_DBC?

 

[pcawdron]

vasanthgnb,

Forget API, it's a waste of time and more trouble than it's worth.

You can do EVERYTHING with ADO 2.5. Do a Web Serach on ADO OpenSchema and you'll find pretty much everything you need can be done with that. A good starting point with ADO is...

http://www.oreilly.com/catalog/mcsdnut/chapter/ch07.html

If you really want to deal with ODBC API the values you're looking for can be found in a file call ODBCTXT.EXE. It's easy enough to find with a Web Search or if you send me your email address and I'll forward it on to you.

Regards,

Peter
pcawdron@gmac.com.au

 

[vasanthgnb]

i want the values of SQL_HANDLE_ENV and SQL_HANDLE_DBC alone and the function declaration and description of SQLENDTRANS() api.

 

[vasanthgnb]

make use of the command SQLNUMRESULTCOLS to get the number of rows in a &quot;Result Set&quot;. Usage of SQLCOLUMNS is not appreciated.