Re: SQL Error - Data Type Has Changed?

["Walcker, Paul" <pwalcker@ISD.CO.SAN-BERNARDINO.CA.US>]

I hadn't seen anymore e-mail, so I assume you have not found the source
of your problem yet.

Here's another thought on how you might capture your problem.

Before executing your SQR, go to your control panel, and open ODBC32
(which is where your data source should be defined).  If you're using
ODBC 3.0, you should have a folder for tracing.  Go to this folder,
click the option button to turn tracing on, then click the start
tracing.  This should log all calls to the ODBC API to the specified
file.  If you are using ODBC 2.0, you will need to find the button that
says start tracing (it's there somewhere).

Run your SQR on the client.

After you have finished running the SQR, go back to the panel and turn
tracing off.  If you don't do this, you will not be allowed to view the
contents of the file because ODBC has not released it yet.

Now for the fun...open the file that contains the trace.

You are looking for a series of API calls.

First find SQLPrepare.  This statement performs an initial pass at your
SQL.  Make sure the SQL on this statement is the SQL you are looking
for.  If not keep looking.  it will look something like this:

VB5             b2:86   ENTER SQLPrepare
                HSTMT               0x08fb2cd0
                UCHAR *             0x192418f0 [      -3] "SELECT
"RECNAME","FIELDNUM","FIELDNAME","SUBRECORD"  FROM "SYSADM"."PSRECFIELD"
 WHERE "RECNAME" = ? AND "FIELDNUM" = ?"
                SDWORD                    -3

Next scroll down a few lines and you should find an SQLBindParameter.
This statement will tell you the data type that SQR thinks is being
passed. As example, the first question mark in the previous statement
(RECNAME) was defined as a varchar field in the data base.  The data
types here should be the first clue to what the problem is.

VB5             b2:86   ENTER SQLBindParameter
                HSTMT               0x08fb2cd0
                UWORD                        1
                SWORD                        1 <SQL_PARAM_INPUT>
                SWORD                       99 <SQL_C_DEFAULT>
                SWORD                       12 <SQL_VARCHAR>
                UDWORD                    15
                SWORD                        0
                PTR                0x0012ea90
                SDWORD                     0
                SDWORD *            0x0012ea8c

If you need to see the actual line where the error occurred, then you
will want to find SQLError.  If you scroll back from this line, you
might be able to tell what the input parameter was and the data type.

Hope this helps some....

Paul

>-----Original Message-----
>From:  C. Duncan Hudson [SMTP:c_duncan_hudson@YAHOO.COM]
>Sent:  Wednesday, July 22, 1998 6:02 AM
>To:    Multiple recipients of list SQR-USERS
>Subject:       SQL Error - Data Type Has Changed?
>
>Hi,
>
>
>I need help.  I've written a program, using SQR 4, that reads two
>Oracle tables and builds a flat file.  I begin by selecting records
>from one table, and then use the returned values (&invoicenum) to
>query the second table.  My second query (below) is returning an error
>about midway through the program (an error listing follows the code),
>after having successfully executed several times.  I've taken the
>offending SQL statement (with the variables that erred) and pasted it
>into SQL Plus and the query works fine.  I'm at a loss as to what's
>causing the error.  Can anyone give me a hand?  Thanks in advance,
>
>
>Dunc.
>
>...................................................
>
>{snip}
>
>begin-select on-error=error_proc(4.1)
>gldebitacct                       &debitacct
>glcreditacct                      &creditacct
>to_char(trunc(linecost * 100))    &linecost
>linecost                          &cost
>percentage                        &percentage
>    let #total_allocated = #total_allocated + &cost
>    do build_distribution
>from {schema}invoicecost
>where invoicenum = &invoicenum
>end-select
>
>{snip}
>
>begin-procedure error_proc (#source)
>evaluate #source
>when < 5 ! Invoice Select
>let #_return-status = 67
>display 'Invoice = ' noline                        display &_invoicnum
>display 'Status = ' noline
>display #source
>display 'Error selecting invoices'
>display 'SQL-Status = ' noline
>display #_sql-status
>display 'SQL-Error = ' noline
>display $_sql-error
>stop quiet
>break
>
>{snip}
>
>.........................................
>
>Invoice = 1011
>Status = 4.100000
>Error selecting invoices
>SQL-Status = 0.000000
>SQL-Error = [INTERSOLV][ODBC Oracle driver]Data type for parameter 1
>has changed since first SQLExecute call.
>
>
>_________________________________________________________
>DO YOU YAHOO!?
>Get your free @yahoo.com address at http://mail.yahoo.com