DB Connection Query

One Star

DB Connection Query

Hello,
I wish to run a SQL query in a DB Connection to retrive the required data.
I have successfully set up the DB Connection. I have successfully retrieved the table schemas. I have successfully set up the SQL Query in the Query repository for the DB Connection, then ran the query and the results returned from running this query are successful too. However when I place the DB Connection on the Job Designer and link it to a log component then run the job I receive the message of "No schema has been defined yet".
I am assuming it is possible to run a query that has been stored in the DB Connections Query repository, otherwise what would be the point of having these DB Connection sub-components. I just need someone to provide with some guidance on how to do this.
Thank you in advance
ajfarroll
Employee

Re: DB Connection Query

Can you postpost a screenshot of your component configuration ? Thank
One Star

Re: DB Connection Query

Hello Camsellem,
Thanks for your response.
Please find attached the image of the Job Design components.
Thanks again
Regards
AJFarroll
One Star

Re: DB Connection Query

Image missing.
One Star

Re: DB Connection Query

Hello,
I apoligise. here is the attached screen shot
One Star

Re: DB Connection Query

I apologise but the image will not load as it continually says it exceeds maximum attributes even when I have reduced size and quality of image as much as I can.
Thanks for everyones help anyway.
I am starting to become un-impressed by this product. I mean why have a repository for query's in a DB connection when the query in repository will not run???
Regards
One Star

Re: DB Connection Query

You are using an Input component aren't you? You should set up the repository so you have your connection and schemas and optionally, the query/ies. In your job then you drag on a Database Input componenent (according to to your DB platform) and set the properties to point to the repo connection (you can also drag a connection object into the job and reference this if you're doing several things with it) - you also set the Query, either by referencing a repo query or entering it as built-in text.
Next add your output component (eg tLogRow), join them together and ,sync the schema to the source.
Run the job and then realise that you shouldn't be quite so quick to be less than impressed with a product that has had teh work that Talend has had go into it which you are using for an outlay of $0
The manuals - User Guide and components Guide - are pretty reasonable and, together with the tutorials, pretty much obligatory to get you started even if you think you know ETL.
I've used BODI and SSIS a lot and have to say I rate Talend well above teh former and pretty close with the latter - and that's just on capability not price.
T
One Star

Re: DB Connection Query

Hello,
Toaster thank you for your response and guidance. I do realise that talend have put a lot of effort into this product and for a free open source version of their product they are probably being generous too. I have used other middleware products and Talend offers more functionality than they do also. Trouble is, to acheive the outcome my manager wants, it will be better for me to use a query in the repository for a connection instead of building quite complex querys in a mapping component. I have performed the process you advised and indeed the DB connection can run the complex query and return the results i am looking for but when I add an Oracle DB Input component, link it to my DB connection set up and set its query to the repository query, then point it to a tLogRow component and sync the schemas it still creates an error when I run the job. The error is;
Starting job SystemP_Connection at 10:40 27/09/2010.
connecting to socket on port 3644
connected
Exception in component tOracleInput_1
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.CharCommonAccessor.getBigDecimal(CharCommonAccessor.java:341)
at oracle.jdbc.driver.OracleResultSetImpl.getBigDecimal(OracleResultSetImpl.java:1543)
at SystemPDBConn.SystemP_Connection_0_1.SystemP_Connection.tOracleInput_1Process(SystemP_Connection.java:785)
at SystemPDBConn.SystemP_Connection_0_1.SystemP_Connection.runJobInTOS(SystemP_Connection.java:1319)
at SystemPDBConn.SystemP_Connection_0_1.SystemP_Connection.main(SystemP_Connection.java:1193)
disconnected
Job SystemP_Connection ended at 10:40 27/09/2010.
##############################
I have also attempted to attach another screen shot of the Job design.
Forgive me for being so quick to sound negative but for me the interface appears to make the task I am trying to acheive quite obvious but then it doesn't work.
Thanks again for any assistance.
AJFarroll
One Star

Re: DB Connection Query

Given that the exception is thrown in oracle jdbc driver class and it is being passed your own SQL (rather than a Talend component) then that is where you should start - maybe a screenshot of the tOracleInput properties, thequery and the schema you're trying to read from would help. Note also the ref to BigDecimal - maybe you have some casting issue with the Double datatype? Bear in mind that under the covers, Talend generates Java code and this is abig plus - you can view/debug the code directly and isolate bits to test yourself if you need to unravel a problem like this (it doesn't sound like a Talend issue).
T
One Star

Re: DB Connection Query

Hello Toaster,
Thanks again for your response. Believe it or not we were just trying that sort of process that you suggested and it looks like you are correct. We were narrowing down the columns being interrogated by removing them one ata time from the original complex SQL statement and we think we have pinpointed the relevant one.
It appears that some sort of convertion is being attempted on a certain column where we are not expecting a conversion to be taking place.
The column in question is a string 6 characters long of letters where most of the last 2 or 3 characters are numbers. None of the selected rows have a number as the first character so they should definetily be trated as a string. Also when I sinc the columns in the tLogRow component the relevant column has been assigned to a string type on each side.
Therefore this appears to be quite a strange problem where it may be that Java is somehow interpretting the relevant column has to be converted to a bigDecimal for some reason.
Does anyone have any clues why this would occur? We have tried doing a "CAST" in the SQL statement to ensure the relevant column remains a string but that doesn't solve the problem
Thanks again
AJFarroll
One Star

Re: DB Connection Query

Screenshot of the schema? If you schema says that the column is numeric then this behaviour is what you would see, maybe you need to correct the schema (ie the metadata schema in the Talend repository)?
One Star

Re: DB Connection Query

Thanks again for your assistance. I have checked the schema in Talend metadata repository and it all seems as it should be. The relevant field that appears to be causing the problem shown in the screen shot below is a string as expected. So why is there a conversion taking place when one is not necessary?
Thank you
AJFarroll
One Star

Re: DB Connection Query

You're still only providing minimal info here - are there other columns in the schema? What makes you think it's PARENT_CODE that is the problem? what is the complete query and the schema you're trying to map it into?
One Star

Re: DB Connection Query

Hi Toaster,
We know its PARENT_CODE column because we used a process of elimination. In the query we took out all the columns in the select statement except the first one and then added them in one by one. The query and Talend Job ran with every column until PARENT_CODE was added back in.
I will ask my manager about providing the complete query but not sure he will agree as it sensitive data.
Regards
AJFarroll
One Star

Re: DB Connection Query

OK - anyway the key thing is probably to reiterate that what you are trying to do is bread and butter stuff for any ETL, Talend included. If you are seeing odd behaviour it sounds like it is more to do with the query itself - debug it in the java code as it passes thru the jdbc layers and you should be able to see what's going wrong...