Re: DB2 and sql-reader-syntax

Hello,

my last posting seems actually not directly related to DB2, but may still be 
related to ODBC...

For example :
Schema X
Table T1
Table T2
Schema Y
Table T1
Table T2

In SQL, if I login as X, or if my schema is X, I can denote tables there as 
X.T1, X.T2 or T1 and T2. If I want to access tables in a different schema, I 
must mention it explicitly as in Y.T1 and Y.T2 (for example "select * from 
y.t1"). As allready said, that works in CommonSQL if I use sql:query or 
sql:execute where I pass strings containing the SQL statements, but how do I 
denote tables in foreign schemas using the sql-reader-syntax?

Regards
Plamen

----- Weitergeleitete Nachricht von softinfo@stamov.ch -----
    Datum: Wed, 24 Mar 2004 11:33:55 +0100
    Von: softinfo@stamov.ch
Antwort an: softinfo@stamov.ch
 Betreff: DB2 and sql-reader-syntax
      An: lisp-hug@xanalys.com

Hello,

Does anybody know how to denote a different schema, than the userid of the 
login-user when I use the sql-reader-synatx in conjuction with a DB2 database 
over ODBC?

I'm using LWW 4.3.6, DB2 ODBC Driver 7.01.00.40, ODBC 3.520.

As you see, I can issue a direct query at prompt line 18 for table 
@VERB.TM0053 and it works. 

If I issue (list-true-tables) I receive a list, which contains several times 
the string "TM0053", which seems to mean that the table is accessible through 
several schemas/userids, but the string itself never contains a schema/userid.

Enabling SQL recording doesn't seem to help, because the errors happen during 
the construction of the SQL statement.

Connecting to the same database and browsing and editing the table works when 
I use for example MS Access as front-end for ODBC.

Thank you

Plamen Stamov


CL-USER 1 > (require "sql")
; Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\concat\clos-sql.fsl
;   Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\concat\sql-common.fsl
;  Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\util\describe.fsl
;   Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\streams\indentin.fsl
;   Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\util\inspvals.fsl
;   Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\concat\fli-insp.fsl
;  Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\patches\describe\0001\0001.fsl
; Loaded public patch DESCRIBE 1.1

; Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\patches\sql\0001\0001.fsl
; Loaded public patch SQL 1.1

T

CL-USER 2 > (in-package :sql)
#<PACKAGE SQL>


SQL 5 > (connect "DB2T/UE05401/******" :database-type :odbc)
; Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\concat\odbc.fsl
;  Loading fasl file C:\Program Files\Xanalys\LispWorks\lib\4-3-0-0
\modules\concat\odbc-common.fsl
#<ODBC-DATABASE "DB2T/UE05401/******" 206961BC>

SQL 6 > (enable-sql-reader-syntax)
Error while reading: 
@VERB_TM00053 is not a valid database identifier.

SQL 18 > (query "SELECT * FROM @VERB.TM0053")
((1 80) (1 81) (1 83) (1 87))
("M0053_ANWEN" "M0053_UBAHN")


SQL 21 > (select [M0053_ANWEN] :from [TM0053])

Error: Sql-Database-Data-Error id 42S02[-204] : [IBM][CLI Driver][DB2] 
SQL0204N  "UE05401.TM0053" ist ein nicht definierter Name.  SQLSTATE=42704

  1 (abort) Return to level 0.
  2 Return to top loop level 0.

Type :b for backtrace, :c <option number> to proceed,  or :? for other options

SQL 23 > (select [M0053_ANWEN] :from [@VERB.TM0053])

Error: 
@VERB.TM0053 is not a valid database identifier.
  1 (abort) Return to level 0.
  2 Return to top loop level 0.

Type :b for backtrace, :c <option number> to proceed,  or :? for other options


SQL 24 > (select [M0053_ANWEN] :from ["@VERB.TM0053"])

Error: :TABLE argument to SQL-EXPRESSION is not a string, symbol, or sql 
identifier -- #<SQL: "@VERB.TM0053">
  1 (abort) Return to level 0.
  2 Return to top loop level 0.

Type :b for backtrace, :c <option number> to proceed,  or :? for other options


SQL 26 > (select [M0053_ANWEN] :from [|@VERB.TM0053|])

Error: 
@VERB.TM0053 is not a valid database identifier.
  1 (abort) Return to level 0.
  2 Return to top loop level 0.

Type :b for backtrace, :c <option number> to proceed,  or :? for other options








----- Ende der weitergeleiteten Nachricht -----