|
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
|
Description
|
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:
TEDECLARE DBSELECT ... WHERE ... AND ?I = 111
TEFETCH ... 111
But the following for text doesn't work:
TEDECLARE DBSELECT ... WHERE ... AND ?V = 'ABC'
TEFETCH ... ABC
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:
- Admin/Compile with Trace
- admin/Clear the Trace
- Run the test exhibiting the error
- Admin/View Trace
- 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
|
|
Comment
|
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: 7.3.2.3.0
Oracle ODBC driver version: 2.00.0301
SQL*Net version: 2.3.2.1.0
|