SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
JAL DatabaseRetrieve( select * from ...) does not work?

 
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite View previous topic
View next topic
JAL DatabaseRetrieve( select * from ...) does not work?
Author Message
splutino



Joined: 02 May 2007
Posts: 21

Post JAL DatabaseRetrieve( select * from ...) does not work? Reply with quote
Hi,
I have been using the DatabaseRetrieve function correctly in the past,
declaring the columns in the select i.e.:

DatabaseRetrieve (select col1, col2 from table1)

and it worked, but if I try to use the * rather than the column I need in the select it would not work
i.e.
DatabaseRetrieve (select * from table1)

any idea?
what is my error
(I am connecting via ODBC to an oracle DB)
Wed Nov 19, 2008 4:30 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
You have got several fatal syntax errors in your script. Neither command would work. I suggest reading "Syntax" topic in the JAL documentation to avoid further confusion and waste of time (your and ours). It should be also helpful to read the documentation to find out required statement parameters, and take a look at examples provided in the manual for each and every JAL command. The documentation has been designed specifically for people to read it.

Here is an except from example code in the manual for the DatabaseRetrieve statement


Code:
Dim rows, number
// connect to database described in the Jobs profile
DatabaseConnect( "Jobs" )
//...
DatabaseRetrieve( "SELECT max(run_date) FROM job_log WHERE job_id = 14", rows )

Wed Nov 19, 2008 8:12 pm View user's profile Send private message
splutino



Joined: 02 May 2007
Posts: 21

Post Reply with quote
sorry, I did not mean to paste the all correct syntax, but just a logical model

this is the code:

DatabaseConnect( "eclipse_live" )

DatabaseRetrieve( "SELECT COMPCD, ACCODE, ACNAME1, ACNAME2 FROM ACC_MAST WHERE COMPCD='IMI' ORDER BY ACCODE", retrieved_rows)

DatabaseRowCount( retrieved_rows )

DatabaseSave ( "C:\\RICO\\EST_DB\\edb_ACC_MAST_24x7a.CSV","CSV", retrieved_rows)

DatabaseDisconnect

If I change the above with "Select * FROM ACC_MAST ...." il will not retrieve anything
that's what I meant as I did go and read the manual about anything else ...

Thanks
S
Fri Nov 21, 2008 11:16 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
Ok. thanks for clarification.

I still think the problem is not there. 24x7 does not read the SQL, not parses it. It simple sends the text to the database as is. when the data comes back, it asks the database driver to provide column names

Please enable the Trace option in Tools/Options menu; Log page, re-run this job and paste a trace of the job run. (You can get the trace from either script.log or the associated file from Performance Data directory).

Let's take a look together at what is going on there.

also please let us know which driver you selected for the database3 connection in the "eclipse_live" database profile properties.
Fri Nov 21, 2008 12:27 pm View user's profile Send private message
splutino



Joined: 02 May 2007
Posts: 21

Post Reply with quote
script.log:
---------------------------------------------------------------
JAL
**** 21/11/2008 16:35:28 ****

JAL 1: DIM
JAL 1: Executing DIM("RETRIEVED_ROWS", "NUMBER")
JAL 3: DATABASECONNECT
JAL 3: Executing DATABASECONNECT("eclipse_live")
JAL
JAL 5: DATABASERETRIEVE
JAL 5: Executing DATABASERETRIEVE("SELECT * FROM ACC_MAST WHERE COMPCD='IMI' ORDER BY ACCODE", "0")

**** 21/11/2008 16:35:28 ****
performance data .log file
----------------------------------------------------------------
1: DIM
1: Executing DIM("RETRIEVED_ROWS", "NUMBER")
3: DATABASECONNECT
3: Executing DATABASECONNECT("eclipse_live")

5: DATABASERETRIEVE
5: Executing DATABASERETRIEVE("SELECT * FROM ACC_MAST WHERE COMPCD='IMI' ORDER BY ACCODE", "0")
Fri Nov 21, 2008 12:38 pm View user's profile Send private message
splutino



Joined: 02 May 2007
Posts: 21

Post Reply with quote
forgot ...
the profile is a oracle ODBC driver
Fri Nov 21, 2008 12:39 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
There must be some error in the job log. What does it say?

Do you have permissions to access all columns of the referenced table (I mean the user specified in the profile)?
Fri Nov 21, 2008 12:41 pm View user's profile Send private message
splutino



Joined: 02 May 2007
Posts: 21

Post Reply with quote
If I run the very same SQL from a SQL*plus screen it's fine ...

is there any limitation in the numbers of columns that the DatabaseRetrieve can pass ?

as I know the table has got a large number of columns ...?
Fri Nov 21, 2008 12:45 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
No there is no hard limitation on 24x7 side (anything less then 32K columns should be ok). I cannot confirm this for the ODBC driver, which I simply don't know.

So what does the error in the log say?
Fri Nov 21, 2008 12:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
PS. There could be also an issue with incompatible data types. For example BLOB type columns cannot be retrieved and saved in such manner.
Fri Nov 21, 2008 12:54 pm View user's profile Send private message
splutino



Joined: 02 May 2007
Posts: 21

Post Reply with quote
just a very generic

"An error occurred while executing 24x7 script.

Line 5:

"

but the ODBC dirver I am usign has no restriction either ...
Fri Nov 21, 2008 12:54 pm View user's profile Send private message
splutino



Joined: 02 May 2007
Posts: 21

Post Reply with quote
There should be only string and date type fields,
it's a static data table of clients' information
Fri Nov 21, 2008 12:58 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
I suggest changing the connection to use Oracle native driver calling OCI directly. Perhaps this will return some meaningful error message. It will also eliminate one level of middleware which is ODBC and which internally call the very same Oracle OCI interface.
Fri Nov 21, 2008 1:17 pm View user's profile Send private message
splutino



Joined: 02 May 2007
Posts: 21

Post Reply with quote
Never Mind,

I have pasted all the columns names rather than * and it worked perfectly


regards,
Sebastiano
Fri Nov 21, 2008 1:24 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6500

Post Reply with quote
Thanks for the status update. I suspect that this issue was caused by the ODBC driver. You may still want to get rid of the unnecessary ODBC middle layer.
Fri Nov 21, 2008 1:52 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.