topic Re: [resolved] tMSSqlRow does not work correctly in Talend Studio

[resolved] tMSSqlRow does not work correctly

Anonymous — Wed, 24 Jul 2013 13:29:43 GMT

Hello,
referring to the ticket with the topic "tMSSQLRow with tsql-Statement using Linked Server" I have the same issue.
I use talend 5.3.1 and want to use "tMSSqlRow" to create a custom SQL. But i end up in only one empty result.
In the same way I do it against a Postgres database with "tPostgressqlRow" and this works fine.
Is this a bug and is there a workaround to get the Result from the SQL?
I've tried to use a "tMSSQLInput" and as long as I do the query against one table it works but if I use a custom query with few different tables joined I get the following exception:
"
Exception in component tMSSqlInput_3
java.sql.SQLException: The executeQuery method must return a result set.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:488)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
.......
"
Hope anybody can help me out.
Thanks, Peter

Re: [resolved] tMSSqlRow does not work correctly

Anonymous — Wed, 24 Jul 2013 19:57:57 GMT

The problem is - I am pretty sure - your statement. Your are trying to get an resultset in the advanced settings. The database does not produce a result set as response to your statement. It would be great to see your statement.
To develop the correct statement I suggest using a JDBC based database client like SQuirrel:
http://squirrel-sql.sourceforge.net/
If you can run your statement here and get a result set, you get it.

Re: [resolved] tMSSqlRow does not work correctly

Anonymous — Thu, 25 Jul 2013 07:25:42 GMT

Thanks for your reply.
I build the statement in aqua data studio and it worked there. (This is similar to Squirrel)
Here is my SQL:
select distinct
substring(cast(a.object_id as char(36)),1,8)+
SUBSTRING(cast(a.object_id as char(36)),15,4)+
SUBSTRING(cast(a.object_id as char(36)),10,4)+
SUBSTRING(cast(a.object_id as char(36)),27,2)+
SUBSTRING(cast(a.object_id as char(36)),25,2)+
SUBSTRING(cast(a.object_id as char(36)),22,2)+
SUBSTRING(cast(a.object_id as char(36)),20,2)+
SUBSTRING(cast(a.object_id as char(36)),35,2)+
SUBSTRING(cast(a.object_id as char(36)),33,2)+
SUBSTRING(cast(a.object_id as char(36)),31,2)+
SUBSTRING(cast(a.object_id as char(36)),29,2) OBJECTID,
a.object_id SQLObjectID
,a.object_name FirstLineObject
,a.object_type
,case when a.object_type=4 then 'Metric' when a.object_type=12 then 'Attribute' else 'Unknown' end OBJECTTYPE
,b.locale M_Locale1,b.property M_Property1,b.translation MetricNameEN,d.translation MetricNameRU
,c.locale M_Locale2,c.property M_Property2,c.translation MetricDescEN,e.translation MetricDescRU
,
substring(cast(g.object_id as char(36)),1,8)+
SUBSTRING(cast(g.object_id as char(36)),15,4)+
SUBSTRING(cast(g.object_id as char(36)),10,4)+
SUBSTRING(cast(g.object_id as char(36)),27,2)+
SUBSTRING(cast(g.object_id as char(36)),25,2)+
SUBSTRING(cast(g.object_id as char(36)),22,2)+
SUBSTRING(cast(g.object_id as char(36)),20,2)+
SUBSTRING(cast(g.object_id as char(36)),35,2)+
SUBSTRING(cast(g.object_id as char(36)),33,2)+
SUBSTRING(cast(g.object_id as char(36)),31,2)+
SUBSTRING(cast(g.object_id as char(36)),29,2) ReportID
,g.object_id ReportObjectID
,g.locale R_Local1,g.translation ReportNameEN
,h.locale R_Locale2,h.translation ReportNameRU
,i.translation ReportDescEN
,j.translation ReportDescRU
from dssmdobjinfo a left outer join dssmdobjtrns b on
(a.project_id=b.project_id and a.object_id=b.object_id and b.locale=1033 and b.property=1)
left outer join dssmdobjtrns c on
(a.project_id=c.project_id and a.object_id=c.object_id and c.locale=1049 and c.property=1)
left outer join dssmdobjtrns d on
(a.project_id=d.project_id and a.object_id=d.object_id and d.locale=1033 and d.property=3)
left outer join dssmdobjtrns e on
(a.project_id=e.project_id and a.object_id=e.object_id and e.locale=1049 and e.property=3)
left outer join dssmdobjdepn f on
(a.object_id=depn_objid and a.project_id=f.depn_prjid and f.object_type=3)
left join dssmdobjtrns g on
(f.project_id=g.project_id and f.object_id=g.object_id and g.locale=1033 and g.property=1)
left join dssmdobjtrns h on
(f.project_id=h.project_id and f.object_id=h.object_id and h.locale=1049 and h.property=1)
left join dssmdobjtrns i on
(f.project_id=i.project_id and f.object_id=i.object_id and i.locale=1033 and i.property=3)
left join dssmdobjtrns j on
(f.project_id=j.project_id and f.object_id=j.object_id and j.locale=1049 and j.property=3)
where
a.object_type in (4,12)
and a.project_id='63BD4218-AD75-41E1-87E4-7795366210DE'
order by a.object_type;

Thank you for helping.

Re: [resolved] tMSSqlRow does not work correctly

Anonymous — Thu, 25 Jul 2013 07:42:38 GMT

Hi again,
I simplified my problem down to a simple select to one column of one table and the problem is still the same. I get only one emty result row instead of few thousands I get in Aqua Data Studio.
I added a screenshot of my simple job.
Thank you for helping.
Peter
http://www.talendforge.org/forum/img/members/14182/mini_113926_Schema_of

Re: [resolved] tMSSqlRow does not work correctly

Anonymous — Thu, 25 Jul 2013 08:57:53 GMT

Hi Peter
tXXXRow component is usually used to any type of sql statement excepts select statement, we use txxxInput component to execute a select statement, if you use use tXXXRow to execute a select statement, it returns a record set, and you are required to use a tParseRecordSet component after tXXXRow to parse the record sets. You can find a demo job in this page.
Shong

Re: [resolved] tMSSqlRow does not work correctly

Anonymous — Sun, 11 Aug 2013 17:38:06 GMT

Hi Shong,
thanks for your help.
Solved it with a txxxInput.
Peter

Re: [resolved] tMSSqlRow does not work correctly

RDX — Mon, 12 Aug 2013 10:40:40 GMT

Hello Shong,
You have guided very nicely.
Thank you