topic Re: Schema from sql query ... in Talend Studio

Schema from sql query ...

Anonymous — Sat, 16 Nov 2024 12:21:00 GMT

Hi,
I'm wondering how to do a few things.
I can setup a new database connection and create a Query.
I can drag this query to a process as a tMSSqlInput object.
When I choose the "Guess schema" button option I get this message:
"Connection error - Connect to DB error, or dome errors in SQL query string, ir 'Guess Schema' not compatible with current SQL query string."
I would like to be able to obtain the schema from a query ...
Maybe I shoudl try re-install of TOS 5.0.1? Have T.I.S. also. I could try this ..
Is it possible to have a complex query as an input to a process (rather than a table?)
Should I expect Talend to be able to generate a schema from a complex sql ?

Query is re. MS SQL*Server database and is as per:

SELECT
"MCS-Santos_PDDB"."Well Name",
"MCS-Santos_PDDB".UWI,
"MCS-Santos_PDDB"."Well Status",
cast(floor(cast("MCS-Santos_PDDB".Date as float)) as datetime) as test_date,
"MCS-Santos_PDDB"."User",
max(
case
when PATINDEX('%PRODUCTION CASING PRESSURE%',upper(Instrument)) > 0 then "MCS-Santos_PDDB"."Reading value"
end ) as PRODUCTION_CASING_PRESSURE,
max( case
when PATINDEX('%INTERMEDIATE CASING PRESSURE%',upper(Instrument)) > 0 then "MCS-Santos_PDDB"."Reading value"
end) as INTERMEDIATE_CASING_PRESSURE,
max( case
when PATINDEX('%SURFACE CASING PRESSURE%',upper(Instrument)) > 0 then "MCS-Santos_PDDB"."Reading value"
end ) as SURFACE_CASING_PRESSURE,
max( case
when (PATINDEX('%TUBING PRESSURE%',upper(Instrument)) > 0
and PATINDEX('%L',upper("Well Name")) > 0)
then "MCS-Santos_PDDB"."Reading value"
end ) as LOWER_STRING_PRESSURE,
max( case
when (PATINDEX('%TUBING PRESSURE%',upper(Instrument)) > 0
and PATINDEX('%U',upper("Well Name")) > 0)
then "MCS-Santos_PDDB"."Reading value"
end ) as UPPER_STRING_PRESSURE,
max( case
when (PATINDEX('%TUBING PRESSURE%',upper(Instrument)) > 0
and not PATINDEX('%U',upper("Well Name")) > 0
and not PATINDEX('%L',upper("Well Name")) > 0
)
then "MCS-Santos_PDDB"."Reading value"
end ) as SINGLE_STRING_PRESSURE
FROM "MCS-Santos_PDDB"
WHERE
PATINDEX('%PRODUCTION CASING PRESSURE%',upper(Instrument)) > 0
Or PATINDEX('%INTERMEDIATE CASING PRESSURE%',upper(Instrument)) > 0
Or PATINDEX('%SURFACE CASING PRESSURE%',upper(Instrument)) > 0
Or PATINDEX('%TUBING PRESSURE%',upper(Instrument)) > 0
group by
"MCS-Santos_PDDB"."Well Name",
"MCS-Santos_PDDB".UWI,
"MCS-Santos_PDDB"."Well Status",
cast(floor(cast("MCS-Santos_PDDB".Date as float)) as datetime),
"MCS-Santos_PDDB"."User"

Re: Schema from sql query ...

Anonymous — Fri, 24 Feb 2012 03:53:09 GMT

Hi
If you want to execute complex query like above, you'd better use tMSSQLRow instead ot tMSSQMInput.
tMSSQLRow-->tParseRecordSet-->Output Components

Regards,
Pedro

Re: Schema from sql query ...

Anonymous — Mon, 27 Feb 2012 04:27:30 GMT

Issue with TOS 5.0.1
Having a problem using the "Guess Schema" button on a tOracleInput component. (on all tOracleInput components).
The error message reads: Connection error: Connect to DB error, or some errors in SQL query string, or 'Guess Schema' not compatible with current SQL query string.
This is a working process and co-worker can use the "Guess Schema" option wth the same component on the same job.
I have tried installing most recent version of Oracle and also installing new version of TOS on notebook.

Re: Schema from sql query ...

Anonymous — Mon, 27 Feb 2012 04:30:09 GMT

minor issue:
TOS 5.0.1 | Help about Talend Studio has a button called "Installation Details". This button gives message as per:
An internal error occurred during: "Fetching children of _SELF_".
java.lang.NullPointerException

Re: Schema from sql query ...

Anonymous — Mon, 27 Feb 2012 05:19:53 GMT

Hi
Sometimes when you edit a query which is so complicated and some columns don't exist in this DB table, "Guess Scheme" will get this error.
I think this issue is due to the query you type.
As I said in previous post, if you want to use complex query, tMSsqlRow is preferred.
Regards,
Pedro

Re: Schema from sql query ...

Anonymous — Mon, 27 Feb 2012 06:59:55 GMT

Hi Pedro,
Thankyou very much for your replies.
It seemed that TMSSqlInput was component item I wanted if I wanted it to provide data on to the next part of the process. I got this to work (and with a complex sql) but it seemed I had to setup the schema manually. It seems that tMSSqlRow is for running statements that act on the db structure or on the data?
The guess schema button doesn't work for me with simple sql or with tables either ... even though I've installed Talend again and most recent version of Java. (I can use a server rather than my notebook and might switch to doing that ..)
Online help for tMSSqlRow indicates:
tMSSqlRow acts on the actual DB structure or on the data
The row suffix means the component implements a flow in the job design although it doesn?t provide output.
regards, thanks, Allan

Re: Schema from sql query ...

Anonymous — Mon, 27 Feb 2012 07:36:17 GMT

Hi Allan
I don't know why "guess schema" doesn't work for you with simple sql or tables.
I need more details, such as query, structure of DB table. Or just a screenshot.
In fact tMSsqlRow can propagate output. tMSSQLRow-->tParseRecordSet-->Output Components
Check Advanced settings "Propagate QUERY's recordset" of tMSSQLRow.
Regards,
Pedro