>Hi!
>
>I need some help on these two ODBC API Functions
>SQLPrimaryKeys and SQLForeignKeys
>
>I am trying to get the PrimaryKey and ForeignKey info. for any specified table from the backend.
>I don't want to call a Stored Procedure (sp_pkeys, sp_fkeys) as they could/would be different across backends.
>
>The following program always returns 65534 Don't know why! What am I doing wrong here. (when I call SQLPrimaryKeys() with its parameters)
>I would appreciate a working example of both functions (SQLPrimaryKeys and SQLForeignKeys()) if possible.
>
>Also help on converting VB types to VFP
>e.g.
>"ByVal hstmt&" -> integer
>szTblQualifier As Any -> string @
>ByVal cbTblQualifier% -> ingeter
>etc.
>

Looking at the definition in the MSDN, you need to build a structure a bit different than you expect. You can use my CLSHEAP class to handle the necessary memory management; I would not be surprised if Christof Lange's STRUCT had support for this as well:

DECLARE INTEGER SQLPrimaryKeys IN ODBC32 ;
INTEGER hStmt, ;
INTEGER nCatalogNamePtr, ;
SHORT nCatalogNameLength, ;
INTEGER nSchemaNamePtr, ;
SHORT nSchemaNameLength, ;
INTEGER nTableNamePtr, ;
SHORT nTableNameLength

SET PROC TO CLSHEAP ADDITIVE
oHeap = CREATEOBJ('Heap')
nCatNamePtr = oHeap.AllocString(as_TableQualifier)
nSchNamePtr = oHeap.AllocString(as_TableOwner)
nTabNamePtr = oHeap.AllocString(as_TableName)
nResult = SQLPrimaryKeys(VAL(SYS(3053), ;
@ nCatNamePtr, ;
LEN(as_TableQualifier) + 1, ;
@ nSchNamePtr, ;
LEN(as_TableOwner)+ 1, ;
@ nTabNamePtr, ;
LEN(as_TableName) + 1 )

What I've done is allocated buffers outside of VFP memory and stored their addresses as integers. I pass those integer values to the API call, which it treats as pointers, what it expects to get. If you need to retrieve the content of any of the buffers passed in the function call, you can use the UDF GetMemString() in the procedure library to move the content of the buffer to a VFP string. When you're done, release the Heap by:

oHeap = NULL




>Thanks.
>
>Sarosh
>
>*--
>Func GetPKeys(as_TblQualifier, as_TblOwner, as_TblName)
>local ls_Return
>
>m.olddec = set("decimal")
>set deci to 0
>hstmt = val(SYS(3053)) && SYS(3053) - ODBC Environment Handle
>set deci to (m.olddec)
>*--
>m.max = 128
>szTblQualifier = as_TblQualifier &&padr("northwnd",m.max) &&+ chr(0)
>cbTblQualifier = len(szTblQualifier)
>*--
>szTblOwner = as_TblOwner &&padr("dbo",m.max) &&+ chr(0)
>cbTblOwner = len(szTblOwner)
>*--
>szTblName = as_TblName &&padr("Products",m.max) &&+ chr(0)
>cbTblName = len(szTblName)
>*--
>Declare integer SQLPrimaryKeys in "odbc32.dll" integer hstmt, string @ szTblQualifier, integer cbTblQualifier, string @ szTblOwner, integer cbTblOwner, string @ szTblName, integer cbTblName
>
>ls_Return = SQLPrimaryKeys(hstmt, @szTblQualifier, cbTblQualifier, @szTblOwner, cbTblOwner, @szTblName, cbTblName)
>*--returns 65534
>
>Return ls_Return
>
>*--
>*-- VB ODBC API's Definitions -- 32 bit versions
>
>*-- Declare Function SQLForeignKeys Lib "odbc32.dll" (ByVal hstmt&, ByVal PTQual&, ByVal PTQual%, ByVal PTOwnr&, ByVal PTOwnr%, ByVal PTName&, ByVal PTName%, ByVal FTQual&, ByVal FTQf%, ByVal FTOwnr&, ByVal FTOwnr%, ByVal FTName&, ByVal FTName%) As Integer
>
>*-- Declare Function SQLPrimaryKeys Lib "odbc32.dll" (ByVal hstmt&, szTblQualifier As Any, ByVal cbTblQualifier%, szTblOwner As Any, ByVal cbTblOwner%, szTblName As Any, ByVal cbTblName%) As Integer