| Author |
Message
|
| madi |
 Posted: Mon Apr 03, 2006 9:12 am Post subject: eSQL -- Database |
 |
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
I am using the following code for a database extract
| Code: |
SET Environment.Variables.TempOrderIds.Id[] = (SELECT T.AHIGNB AS OrderID FROM Database.{SchemaName}.RTINPH AS T
WHERE T.AHFYJS = 'N'); |
where I am getting the SchemaName from a properties file. I get the error:
| Quote: |
Database error: SQL State '42601'; Native Error Code '-104'; Error Text '[IBM][CLI Driver][AS] SQL0104N An unexpected token "." was found following "". Expected tokens may include: "( TABLE <IDENTIFIER>". SQLSTATE=42601 '.
The error has the following diagnostic information: SQL State '42601' SQL Native Error Code '-104' SQL Error Text '[IBM][CLI Driver][AS] SQL0104N An unexpected token "." was found following "". Expected tokens may include: "( TABLE <IDENTIFIER>". SQLSTATE=42601 '
|
What am I doing wrong here??
When I do a debug I can see the SchemaName getting populated by what I want but its not wokring inside the statement.
plz help
--madi[/code] |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon Apr 03, 2006 9:19 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is SchemaName a one part name, or more than one part? I mean, does it resolve to something like "SCHEMA.DatabaseName." or just to "Schema"?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| madi |
| Posted: Mon Apr 03, 2006 9:33 am Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
| no just something like "ADKSJRRT" |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Apr 03, 2006 9:39 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Okay, so it's only a one part name. Are there spaces or etc in the properties file that are getting included when you read the name?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| madi |
| Posted: Mon Apr 03, 2006 9:43 am Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
this is what i have in the properties file
| Quote: |
SchemaName=FDSFDGSG
Company=6 |
--madi |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Apr 03, 2006 9:49 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Can you add
| Code: |
| Set Environment.Variables.SchemaName = SchemaName; |
and then trace Environment and post this value?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| EddieA |
| Posted: Mon Apr 03, 2006 9:50 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Is the {...} evaluation allowed in a SELECT.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Apr 03, 2006 9:53 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| EddieA wrote: |
| Is the {...} evaluation allowed in a SELECT. |
I'm pretty sure it is in v6.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| madi |
| Posted: Mon Apr 03, 2006 9:53 am Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
| Quote: |
| Is the {...} evaluation allowed in a SELECT. |
that is what i wanted to know
I am trying something else which might give an answer for this .. lets see
--madi |
|
| Back to top |
|
|
| madi |
| Posted: Mon Apr 03, 2006 10:00 am Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
Nop. It looks like it does not allow ... i set SchemaName to what I want and tried to access it but didnt work.
SO i guess i'll have to work around i
thanks
--madi |
|
| Back to top |
|
|
| wschutz |
| Posted: Mon Apr 03, 2006 11:37 am Post subject: |
|
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
Madi, is this V5 or V6?
If V6, you should be able to do this (from the infocenter)
| Quote: |
-- Declare variables to hold the data source, schema, and table names
-- and set their default values
DECLARE Source CHARACTER 'Production';
DECLARE Schema CHARACTER 'db2admin';
DECLARE Table CHARACTER 'DynamicTable1';
-- Code which calculates their actual values comes here
-- Insert the data into the tabl
INSERT INTO Database.{Source}.{Schema}.{Table} (Name, Value) values ('Joe', 12.34);
|
The V5 documentation is pretty clear that "you cannot calculate a scema or table name at runtime."
_________________
-wayne |
|
| Back to top |
|
|
| madi |
| Posted: Mon Apr 03, 2006 11:43 am Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
|
| Back to top |
|
|
| alexey |
| Posted: Mon Apr 03, 2006 1:44 pm Post subject: |
|
|
Acolyte
Joined: 18 Dec 2003
Posts: 62
Location: Israel
|
1 - you can use EVAL
2 - you can use PASSTHRU
| Code: |
DECLARE selStmnt CHAR
'SELECT T.AHIGNB AS OrderID FROM '||SchemaName||'.RTINPH AS T WHERE T.AHFYJS = ''N''';
SET Environment.Variables.TempOrderIds.Id[] = PASSTHRU(selStmnt);
|
FYI, EVAL with SELECT has a memory leak problem in v5 - see
http://www-1.ibm.com/support/docview.wss?uid=swg1IC47363
Alexey. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Apr 03, 2006 1:55 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
3: Never use EVAL for dynamic database work. PASSTHRU will work just fine and is the way to go here. The { } syntax to calculate a schema / table / dsn name is V6 only. The V5 way is PASSTHRU.
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Apr 03, 2006 3:46 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| mgk wrote: |
| 3: Never use EVAL |
I couldn't agree more.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
