sqlrelay-discussion

[Sqlrelay-discussion] Getting SQLRelay to play happily with Oracle 8i, CLOBs, and Perl DBI

[Chris F. <cfu...@gm...>]

Howdy,

Trying to get SQLrelay to work with an Oracle 8i (8.1.7) database and
am having an issue pulling Oracle CLOBs.  Here's the environment
Specifics:

Data Base Server: Solaris 8 x86 running Oracle 8i (8.1.7)
SQL-Relay Server: Redhat Linux 9 running SQL-Relay 0.36.4
Application Server: Solaris 9 x86 w/ perl 5.8.0

I'm trying to pull from this table:

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------=
-----
 SES_ID                                    NOT NULL VARCHAR2(64)
 USR_ID                                             NUMBER(12)
 CLI_ID                                             NUMBER(12)
 AUTH_LEVEL                                         NUMBER(12)
 IP_ADDR                                            VARCHAR2(32)
 DATA                                               CLOB
 DATA_SIZE                                          NUMBER(12)
 STATUS                                    NOT NULL NUMBER(2)
 CTIME                                     NOT NULL DATE
 MTIME                                     NOT NULL DATE
 SECURITY                                           NUMBER(20)

As you may notice, the DATA column is a CLOB.

I've found SQL-Relay's debugging feature and tried to see what was going on=
:

08/04/2005 13:43:39 EDT connection [6005] :             fetching 100 rows..=
.

08/04/2005 13:43:39 EDT connection [6005] :                   =20
"81","2004/11/09 00:00:00","^E^F^C


And here is the SQL that was run:

  SELECT auth_level, cli_id, TO_CHAR( ctime, 'YYYY/MM/DD HH24:MI:SS'
), data, ip_addr, TO_CHAR( mtime, 'YYYY/MM/DD HH24:MI:SS' ), security,
ses_id, data_size, status, usr_id FROM ucs3_user.sess WHERE status IN
( ?, ? ) AND ses_id =3D ?
Bind values:
  [0] 0
  [1] 1
  [2] 724a33a8a67d7a7cdd0951995cb1b5bc1792a2b0

Yet I seem to be getting garbage back.  If anyone has any experience
getting SQL-relay to properly digest Oracle CLOBs or if you require
additional information, let me know.

Cheers!

--=20
Chris Fuhrman
cfu...@gm...

Re: [Sqlrelay-discussion] Getting SQLRelay to play happily with Oracle 8i, CLOBs, and Perl DBI

[Ionut C. <ion...@av...>]

Oracle CLOBS are meant to be fetched using output binds, so what you're 
experiencing is not an SQL Relay issue, but an Oracle "feature".

If you didn't experience this before, it might be because DBI probably does 
its part of abstractization, parsing the query and rewriting it to fetch the 
CLOBS separately, then transparently delivering the full result set to the 
client app.

This is what one would expect when running such a query against a DBMS that 
doesn't support CLOBS (or supports some sort of an unlimited TEXT data 
type) -- postgres, mysql etc., but no, not Oracle.

If I'm right and DBI performs such query rewrites, then it should be able to 
do that when using the Sql Relay driver too, so it's an implementation that 
has to be done within this driver, to resemble the original OCI driver.

Ionut

----- Original Message ----- 
From: "Chris Fuhrman" <cfu...@gm...>
Sent: Thursday, August 04, 2005 8:53 PM
Subject: [Sqlrelay-discussion] Getting SQLRelay to play happily with Oracle 
8i, CLOBs, and Perl DBI


Howdy,

Trying to get SQLrelay to work with an Oracle 8i (8.1.7) database and
am having an issue pulling Oracle CLOBs.  Here's the environment
Specifics:

Data Base Server: Solaris 8 x86 running Oracle 8i (8.1.7)
SQL-Relay Server: Redhat Linux 9 running SQL-Relay 0.36.4
Application Server: Solaris 9 x86 w/ perl 5.8.0

I'm trying to pull from this table:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SES_ID                                    NOT NULL VARCHAR2(64)
 USR_ID                                             NUMBER(12)
 CLI_ID                                             NUMBER(12)
 AUTH_LEVEL                                         NUMBER(12)
 IP_ADDR                                            VARCHAR2(32)
 DATA                                               CLOB
 DATA_SIZE                                          NUMBER(12)
 STATUS                                    NOT NULL NUMBER(2)
 CTIME                                     NOT NULL DATE
 MTIME                                     NOT NULL DATE
 SECURITY                                           NUMBER(20)

As you may notice, the DATA column is a CLOB.

I've found SQL-Relay's debugging feature and tried to see what was going on:

08/04/2005 13:43:39 EDT connection [6005] :             fetching 100 rows...

08/04/2005 13:43:39 EDT connection [6005] :
"81","2004/11/09 00:00:00","^E^F^C


And here is the SQL that was run:

  SELECT auth_level, cli_id, TO_CHAR( ctime, 'YYYY/MM/DD HH24:MI:SS'
), data, ip_addr, TO_CHAR( mtime, 'YYYY/MM/DD HH24:MI:SS' ), security,
ses_id, data_size, status, usr_id FROM ucs3_user.sess WHERE status IN
( ?, ? ) AND ses_id = ?
Bind values:
  [0] 0
  [1] 1
  [2] 724a33a8a67d7a7cdd0951995cb1b5bc1792a2b0

Yet I seem to be getting garbage back.  If anyone has any experience
getting SQL-relay to properly digest Oracle CLOBs or if you require
additional information, let me know.

Cheers!

-- 
Chris Fuhrman
cfu...@gm...


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
_______________________________________________
Sqlrelay-discussion mailing list
Sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion 

Re: [Sqlrelay-discussion] Getting SQLRelay to play happily with Oracle 8i, CLOBs, and Perl DBI

[Chris F. <cfu...@gm...>]

Okay,

After looking through the source code and documentation, I've realized
that I'm probably going to have to hack in CLOB support for
bind_inout_param so I can get SQLRelay to support retrieval of oracle
CLOBs.

I'll also take a look at the OCI driver and see how that does it's thing.

Cheers!

On 8/4/05, Ionut Ciocirlan <ion...@av...> wrote:
> Oracle CLOBS are meant to be fetched using output binds, so what you're
> experiencing is not an SQL Relay issue, but an Oracle "feature".
>=20
> If you didn't experience this before, it might be because DBI probably do=
es
> its part of abstractization, parsing the query and rewriting it to fetch =
the
> CLOBS separately, then transparently delivering the full result set to th=
e
> client app.
>=20
> This is what one would expect when running such a query against a DBMS th=
at
> doesn't support CLOBS (or supports some sort of an unlimited TEXT data
> type) -- postgres, mysql etc., but no, not Oracle.
>=20
> If I'm right and DBI performs such query rewrites, then it should be able=
 to
> do that when using the Sql Relay driver too, so it's an implementation th=
at
> has to be done within this driver, to resemble the original OCI driver.
>=20
> Ionut
>=20


--=20
Chris Fuhrman
cfu...@gm...