Ticket #47 ( Closed )

Short Description DBSELECT with text parameter marker
Entered By: charles.gautreau@atl.bluecross.ca When: 1998-06-04 09:02:13 Build: 1.03.07A
Categories Type: Question   Department: Product   Category: ODBC Support
I'm trying to read an Oracle database using ODBC with passing parameter. To narrow down my problem I added a simple WHERE ... AND 111=111

I tried succesfully:

TEFETCH ... 111

But the following for text doesn't work:


I tried almost all various (except for the correct one obviously) with quotes, noquotes, ?V, ?C, ?V(3), ?C(3) but nothing works ???

Append By: WindSurfer  When: 1998-06-04 13:23:15  New Status: Pending Customer
Comment Can you use the DevCenter and:
  1. Admin/Compile with Trace
  2. admin/Clear the Trace
  3. Run the test exhibiting the error
  4. Admin/View Trace
  5. Email to surfer@ieinc.com, subject Ticket #47, and attach /screensurfer/hostserver/templates.log (if the file is big, you can zip ahead of time).
You don't need to e-mail if the view/trace shows you something. Screensurfer's use of parameters can make the datatype tricky, but usually there is an error returned by the DB if the datatype on input variables is wrong.
Append By: WindSurfer  When: 1998-06-05 08:44:05  New Status: Pending Customer
Comment Thanks for the traces--it appears that Oracle is not going to report any datatype mismatches to us, so it is important to get the right datatype.

We will see if we can give you a simple tool in the DevCenter to browse the DB catalogs which will provide the correct datatype for each column.

In the meantime, any chance of e-mailing surfer@ieinc.com (T47 as subject again) the DDL for the table you are querying and/or some other report that details the column specification?

We'll also look into ODBC tracing options in an Oracle environment and let you know what we find...

Append By: charles.gautreau@atl.bluecross.ca  When: 1998-06-05 10:11:38  New Status: Pending IE
SQL> desc hostdata
Name                      Null?    Type
------------------------- -------- ----
SYSUSER_ID                         VARCHAR2(8)
HOSTKEY                            VARCHAR2(80)
HOSTVALUE                          VARCHAR2(80)
HOSTOPTION                         VARCHAR2(2)
Append By: WindSurfer  When: 1998-06-05 10:51:05  New Status: Pending Customer
Comment Thanks-- hmmm, everything looks right. This is simple stuff, so we just have to figure-out the Oracle ODBC rules. Our Oracle person is out until Monday June 8, so I'm afraid some of the quicker resolutions won't be available today.

Sometimes the database will interpret a length+1 for certain datatypes and truncate data as a result if you use the "correct" value.

Can you please try with ?C(9) or ?V(9) and see if this helps any?

Still investigating ODBC tracing-- it is accessed from the Control Panel ODBC32 dialog, but we're not sure if it's going to tell us any more than our trace-- despite the high level of detail, our guess is that the problem is in the Oracle optimizer.

Append By: charles.gautreau@atl.bluecross.ca  When: 1998-06-08 08:33:18  New Status: Pending IE
Comment I tried ?C(9) and ?V(9) and I'm still not getting any rows returned by passing a TEXT parameter.
Append By: charles.gautreau@atl.bluecross.ca  When: 1998-06-12 08:38:22  New Status: Pending IE
Comment I have added a section to read a Lotus Notes database via a NotesSQL ODBC driver. With the Notes ODBC driver I can successfully read the database while passing a TEXT parameter. But with the Oracle ODBC driver I still can't pass a TEXT parameter! Any ideas ?
Append By: WindSurfer  When: 1998-06-12 17:49:01  New Status: Pending Customer
Comment I'm sorry we haven't had a chance to reproduce with Oracle yet.

All of this is due to our use of parameter markers, and I would venture to guess, possible bugs in the Oracle ODBC->SQL*Net->server environment. Oracle is a big fan of parameter markers, since they use this form of SQL in all their benchmarks, and it is key to one of their performance optimizations (avoiding compiling the same statement). So, I'm sure that there is a way to make them work!

So that we might move this along during next week, which version of Oracle are you using on the server, which version of the ODBC driver and which version of SQL*Net? It would also be great if you could get a trace at the Oracle end of one of these queries when it doesn't work.

Please bear with us--I can assure you that we are happy to guarantee that Oracle support will work should you chose to become a customer!

Append By: charles.gautreau@atl.bluecross.ca  When: 1998-06-15 13:03:30  New Status: Pending IE
Comment Here's the Oracle version numbers and I'll try to get an Oracle trace: Oracle database version: Oracle ODBC driver version: 2.00.0301 SQL*Net version: