Re: Call to SQLForeignKeys truncates Table Name, doesn't include null terminator

From: Jason D. <Tronman_at_gmail.com>
Date: 8 May 2007 12:37:04 -0700
Message-ID: <1178653024.242968.190310@y80g2000hsf.googlegroups.com>

On May 8, 3:17 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Jason D. wrote:
> > On May 8, 12:24 pm, DA Morgan <damor..._at_psoug.org> wrote:
> >> Jason D. wrote:
> >>> I have a table called JayTable2 which contains a Foreign Key to
> >>> JayTable1
> >>> When I make a call to SQLForeignKeys though my C++ application for
> >>> JayTable2, nothing is returned:
> >>> retcode = SQLForeignKeys(hstmt,
> >>> NULL, 0,
> >>> NULL, 0,
> >>> NULL, 0,
> >>> NULL, 0,
> >>> NULL, 0,
> >>> szTable, SQL_NTS);
> >>> szTable is of type SQLCHAR containing the string "JayTable2"
> >>> When I ran an ODBC Trace, I found the following:
> >>> ODBCTest d44-af0 ENTER SQLForeignKeysW
> >>> HSTMT 00A32058
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00A31920 [ -3] "JayTa"
> >>> SWORD -3
> >>> ODBCTest d44-af0 EXIT SQLForeignKeysW with return code 0 (SQL_SUCCESS)
> >>> HSTMT 00A32058
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00A31920 [ -3] "JayTa"
> >>> SWORD -3
> >>> As you can see, it looks as though all that is getting passed is
> >>> "JayTa", it is getting truncated which may be while it isn't returning
> >>> any ForeignKeys. Just for fun, I created a table called JayTa with a
> >>> Foreign Key constraint, but nothing was till returned. I think this
> >>> may be due to the missing null terminator. For example, in a call to
> >>> SQLPrimaryKeys, I see this:
> >>> ODBCTest d44-af0 ENTER SQLPrimaryKeysW
> >>> HSTMT 00A32058
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00A31920 [ -3] "JayTable2\ 0"
> >>> SWORD -3
> >>> ODBCTest d44-af0 EXIT SQLPrimaryKeysW with return code 0 (SQL_SUCCESS)
> >>> HSTMT 00A32058
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00A31920 [ -3] "JayTable2\ 0"
> >>> SWORD -3
> >>> The entire table name is there as well as a string terminator. SQL_NTS
> >>> is being cited as the length in both calls. When I tried to specify a
> >>> length of 9, it was truncated at "JayT" I had to set it to 18 to get
> >>> it to pass "JayTable2"....but there were still no results, and no
> >>> \ 0 in the trace call.
> >>> Finally, I specifically included the Null character in the string, and
> >>> ran it. I finally got:
> >>> ODBCTest 918-650 ENTER SQLForeignKeysW
> >>> HSTMT 00A31DF8
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00A32230 [ 20] "JayTable2\ 0"
> >>> SWORD 20
> >>> ODBCTest 918-650 EXIT SQLForeignKeysW with return code 0 (SQL_SUCCESS)
> >>> HSTMT 00A31DF8
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00000000
> >>> SWORD 0
> >>> WCHAR * 0x00A32230 [ 20] "JayTable2\ 0"
> >>> SWORD 20
> >>> With the string terminator....but no values were returned. And ideas
> >>> or advice? Is this a bug somewhere?
> >>> Thanks!
> >> What product are you using? What version? What does any of this have to
> >> do with Oracle?
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org

>

> > I'm using an SQLForeignKeys on an Oracle Database, Version: Oracle9i
> > Release 9.2.0.1.0 - Production

>

> > I'm trying to figure out why SQLForeignKeys isn't working on my Oracle
> > Database.

>

> What ODBC driver and why unpatched 9.2.0.1 which begins desupport next
> month?

>

> And why, if you have C++, ODBC? Why not use the C APIs?
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Driver Version is: 2.575.1117.00 (installed with Visual C++ 6.0)

As for the Oracle Version, 9.2.0.1 is simply the version installed at my school and the only one available to me.

What do you mean by C API's? Are you referring to Oracle Call Interface/Oracle C++ Call Interface? The goal is eventually to have the program connect to multiple databases, not just Oracle, hence the reason I was trying to use ODBC, but it looks like I might have better luck with OLEDB anyway. Received on Tue May 08 2007 - 14:37:04 CDT