View Full Version : Database table creation
04-19-2006, 07:35 AM
I'm new to the application and, as a test, I'm trying to create a new transformation that simply imports data from a text file into a table in a database.
1) Do I need to create the table before the 'table output' transformation or will the table be created 'automagically'? It appears that I need to create the table manually before hand.
2) If I need to create the table before hand, I am assuming that I need to create this in a 'execute sql' step. Is this correct? If so, how do I handle the sql error if the table already exists. For a simple test, I know that I could create the table manually and not worry about table creation in the transformation process. However, for a process that would need to create a table with a more dynamic name, how would this be accomplished?
04-19-2006, 07:47 AM
Nothing auto-magically about it I'm afraid.
Simply press the SQL button in the Table Output step or click on the SQL icon in spoon to see which statements need to be executed for the transformation to work properly.
The dynamic creation of tables like CREATE TABLE A1 .. ; CREATE TABLE A2 ... etc is possible thanks to the SQL step in a transformation. Kettle simply replaces the question marks (?) in the statement with the supplied fields as parameters.
These fields can come from any possible data source.
04-19-2006, 09:40 AM
Thanks for the quick response.
I've been doing some more investigation since I posted the message.
For the project that I am working on, Kettle is just one piece of the puzzle and I will be calling the transformations (the transformations will be fairly static) via java calls when specific events and signals occur within the third-party app from which I need to retrieve data.
I took a look at the code with in the TransBuilder.java file in the extra file. This helped alot. However, I have another question or two. (BTW, I really do appreciate the quick response.)
I create two database connections (ex: Demo1 and Demo2) and I will be doing a 'table output' step to a table in each database (ex: table Part1 in database Demo1 and table Part2 in database Demo2). I retrieve the sql statements via transMeta.getSQLStatementsString() and I get back the sql statements to create both tables. The problem is that I don't want to create table Part2 in database Demo1 (and vice-versa). Looking at the API, I don't see where I can get the sql statements for each individual database. Is this correct? If so, is there anyway to change this?
If I am working with a table that is named based upon a field value that is within the data, I don't get the correct field name in the transMeta.getSQLStatementsString(). I assume that this is because the process doesn't yet know the actual name of the table yet. You indicate that I can do an 'SQL' step to create the tables on the fly. I understand that, and I have no problem with doing this, but the transformation process will fail if I try to create a table that already exists. What is the correct way to handle this situation?
Also, I just tried to create the dynamic tables on the fly with the sql statement
CREATE TABLE ?
, PartId TEXT
, Specie TEXT
, Finish TEXT
, cutlength DOUBLE
, cutwidth DOUBLE
, cutthickness DOUBLE
And I set the parameter to the field GroupId. I get the error message that the sql statement failed. Did I not set things up correctly? I am currently using MS access as the test database right now. Any issues with that?
04-19-2006, 11:07 AM
Sorry to be a pain with all the questions.
I've seemed to answer one of my questions. To get the sql statements and the database connection that is needed, I had to iterate over the transMeta.getSqlStatements() and use the database specified in the SqlStatement object.
That works better, but MS Access is throwing an exception:
Couldn't execute SQL: ALTER TABLE Part ALTER COLUMN cutlength DOUBLE
ALTER TABLE Part ALTER COLUMN cutwidth DOUBLE
ALTER TABLE Part ALTER COLUMN cutthickness DOUBLE
[Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.
So, another thing to dive into. I execute the commands in the 'Execute Sql' dialog button under the 'table output' step and it executes ok.
Thanks again for the help and the patience.
04-19-2006, 11:37 AM
Just in case anyone wants to stay in the loop on this.
MS Access doesn't like to receive the three ALTER TABLE commands in one execution. I split the string and do a database.execStatement on each individual statement and that works.
05-16-2006, 02:18 AM
Ive hacked a change for automatic table creation against v2.3. Im going to try to contribute it, but if youd like it now post a message and Ill post the required classes.
05-16-2006, 02:22 AM
Fixes are always welcome and even an occasional hack is allowed ;-)
If you have problems, add a feature request tracker and attach the source to it.
Send myself or any of the other developers a mail with the source so that it doesn't get lost.
Thanks in advance!