JAL DatabaseRetrieve( select * from ...) does not work?

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)

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 )

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

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.

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")

forgot ...
the profile is a oracle ODBC driver

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)?

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 ...?

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?

PS. There could be also an issue with incompatible data types. For example BLOB type columns cannot be retrieved and saved in such manner.

just a very generic

"An error occurred while executing 24x7 script.

Line 5:

"

but the ODBC dirver I am usign has no restriction either ...

There should be only string and date type fields,
it's a static data table of clients' information

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.

Never Mind,

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

regards,
Sebastiano

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.