[Sqlrelay-discussion] inconsistent queries from SQLRelay?

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

I'm using SQLRelay to talk to an oracle database. The database is  
Oracle 10g running on Solaris. For the moment, the SQLRelay 0.36.4 is  
running on Linux (Suse 9.1) using the Oracle InstantClient libraries  
and I'm using the Python API to have my application talk to the  
SQLRelay and database.

Most of my fields are varchar and int fields. One field, "body" is a  
CLOB.

If I execute a "SELECT *" in SQLRelay for the row I'm looking for,  
the body often (but not always) comes back empty. If I do a request  
on just the body field, I can get back results.

Does the CLOB field need to be handled differently? Why do my results  
differ if I request the field individually?

Below is an example of the problem using SQLRelay to demonstrate the  
behavior.

langmead@breaker:~> /zope/bonzai-orp/bin/sqlrsh -id oracle
SQLRShell - Version 0.22
         Connected to: localhost:9000 as bonzai

         type help; for a help.

0> SELECT * from BZ_STORY_ASSET_SCHEMA WHERE SCHEMA_ID=539;
STORY_ID    SCHEMA_ID ABSTRACT BODY BODY_LENGTH BYLINE         
BYTTL          COPYRIGHT_DATE COPYRIGHT_HOLDER DATELINE      
EDITORIAL_TYPE OVERLINE COLUMN_NAME LOCATION  
MAIN_HEADLINE                            REVIEW_RATING SUB_HEADLINE  
EXTENDED_HEADLINE TEASE_HEADLINE TEASE_LAST_MODIFIED_DATE  
TEASE_SUB_HEADLINE TEASE_SUBJECT TEASE_TEXT TEASE_WORD_LINK_NUMBER  
URGENCY WORD_COUNT SOURCE PAGE_POSITION_PRIORITY PAGINATE_OFF  
IGNORE_LINE_BREAKS MODIFIED_DATE_TIME MODIFY_INSTANCE
======================================================================== 
======================================================================== 
======================================================================== 
======================================================================== 
======================================================================== 
======================================================================== 
===========================================
-1291481359 539                     26          Doug Ferguson AP Golf  
Writer                                 AUGUSTA, Ga.  
News                                         Traditional Augusta  
remains  
contemporary                                                             
                                                                       7 
                       5       0                  
100                    0            0
         Rows Returned   : 1
         Fields Returned : 32
         System time     : 20000

0> debug on;
0> SELECT * from BZ_STORY_ASSET_SCHEMA WHERE SCHEMA_ID=539;
Result Set Buffer Size: 100
Sending Query:
SELECT * from BZ_STORY_ASSET_SCHEMA WHERE SCHEMA_ID=539
Length: 55
Requesting Cursor: 0
Sending 0 Input Bind Variables:
Sending Output Bind Variables:
Send Column Info: yes
Skipping and Fetching
         row to get: 99
Skipping 0 rows
Fetching 100 rows
Checking For An Error... none.
Getting Cursor ID...
Cursor ID: 0
Previous result set was not suspended.
Parsing Column Info
Actual row count: unknown
Affected row count: 0
Column count: 32
"STORY_ID","VARCHAR2", 20 (0,0)
"SCHEMA_ID","NUMBER", 22 (38,0) NOT NULL
"ABSTRACT","VARCHAR2", 1000 (0,0)
"BODY","CLOB", 0 (0,0)
"BODY_LENGTH","NUMBER", 22 (38,0)
"BYLINE","VARCHAR2", 50 (0,0)
"BYTTL","VARCHAR2", 50 (0,0)
"COPYRIGHT_DATE","VARCHAR2", 10 (0,0)
"COPYRIGHT_HOLDER","VARCHAR2", 20 (0,0)
"DATELINE","VARCHAR2", 100 (0,0)
"EDITORIAL_TYPE","VARCHAR2", 10 (0,0)
"OVERLINE","VARCHAR2", 30 (0,0)
"COLUMN_NAME","VARCHAR2", 30 (0,0)
"LOCATION","VARCHAR2", 30 (0,0)
"MAIN_HEADLINE","VARCHAR2", 100 (0,0)
"REVIEW_RATING","VARCHAR2", 10 (0,0)
"SUB_HEADLINE","VARCHAR2", 30 (0,0)
"EXTENDED_HEADLINE","VARCHAR2", 30 (0,0)
"TEASE_HEADLINE","VARCHAR2", 30 (0,0)
"TEASE_LAST_MODIFIED_DATE","DATE", 7 (0,0)
"TEASE_SUB_HEADLINE","VARCHAR2", 30 (0,0)
"TEASE_SUBJECT","VARCHAR2", 20 (0,0)
"TEASE_TEXT","VARCHAR2", 400 (0,0)
"TEASE_WORD_LINK_NUMBER","NUMBER", 22 (38,0)
"URGENCY","VARCHAR2", 10 (0,0)
"WORD_COUNT","NUMBER", 22 (38,0)
"SOURCE","VARCHAR2", 10 (0,0)
"PAGE_POSITION_PRIORITY","NUMBER", 22 (38,0)
"PAGINATE_OFF","VARCHAR2", 5 (0,0)
"IGNORE_LINE_BREAKS","VARCHAR2", 5 (0,0)
"MODIFIED_DATE_TIME","UNKNOWN", 11 (0,6)
"MODIFY_INSTANCE","VARCHAR2", 100 (0,0)
Receiving Output Bind Values:
Parsing Data
"-1291481359","539",(null),(null),"26","Doug Ferguson","AP Golf  
Writer",(null),(null),"AUGUSTA, Ga.","News",(null),(null), 
(null),"Traditional Augusta remains contemporary",(null),(null), 
(null),(null),(null),(null),(null),(null),"7","5","0", 
(null),"100","0","0",(null),(null),
Got end of result set.
STORY_ID    SCHEMA_ID ABSTRACT BODY BODY_LENGTH BYLINE         
BYTTL          COPYRIGHT_DATE COPYRIGHT_HOLDER DATELINE      
EDITORIAL_TYPE OVERLINE COLUMN_NAME LOCATION  
MAIN_HEADLINE                            REVIEW_RATING SUB_HEADLINE  
EXTENDED_HEADLINE TEASE_HEADLINE TEASE_LAST_MODIFIED_DATE  
TEASE_SUB_HEADLINE TEASE_SUBJECT TEASE_TEXT TEASE_WORD_LINK_NUMBER  
URGENCY WORD_COUNT SOURCE PAGE_POSITION_PRIORITY PAGINATE_OFF  
IGNORE_LINE_BREAKS MODIFIED_DATE_TIME MODIFY_INSTANCE
======================================================================== 
======================================================================== 
======================================================================== 
======================================================================== 
======================================================================== 
======================================================================== 
===========================================
-1291481359 539                     26          Doug Ferguson AP Golf  
Writer                                 AUGUSTA, Ga.  
News                                         Traditional Augusta  
remains  
contemporary                                                             
                                                                       7 
                       5       0                  
100                    0            0
         Rows Returned   : 1
         Fields Returned : 32
         System time     : 30000

0> SELECT SUBSTR(body,0,60) from BZ_STORY_ASSET_SCHEMA WHERE  
SCHEMA_ID=539;
Result Set Buffer Size: 100
Sending Query:
SELECT SUBSTR(body,0,60) from BZ_STORY_ASSET_SCHEMA WHERE SCHEMA_ID=539
Length: 71
Requesting Cursor: 0
Sending 0 Input Bind Variables:
Sending Output Bind Variables:
Send Column Info: yes
Skipping and Fetching
         row to get: 99
Skipping 0 rows
Fetching 100 rows
Checking For An Error... none.
Getting Cursor ID...
Cursor ID: 0
Previous result set was not suspended.
Parsing Column Info
Actual row count: unknown
Affected row count: 0
Column count: 1
"SUBSTR(BODY,0,60)","CLOB", 0 (0,0)
Receiving Output Bind Values:
Parsing Data
"For more than 40 years, the yardage on the scorecard at Augu",
Got end of result set.
SUBSTR(BODY,0,60)
============================================================
For more than 40 years, the yardage on the scorecard at Augu
         Rows Returned   : 1
         Fields Returned : 1
         System time     : 30000

0> exit;
langmead@breaker:~>