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