Database Inserts
Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
3 posts
Page 1 of 1
Hello,
I am trying to use GAD to insert rows to a database table and the documentation for the SQL task seems to indicate that this can be done but it doesn't say how to do so.
In my test project I read a set of rows from a CSV and assign them to rowset ${inrows}. I set that variable in the Input Rowset field in the sql task and set my query to
INSERT INTO MYTEST(COLUMN1, COLUMN2, COLUMN3)
but I get an error back indicating java.sql.SQLException: Invalid column index
I tried using this instead:
INSERT INTO MFTTEST(COLUMN1, COLUMN2, COLUMN3)
VALUES(${inrows[1]},${inrows[2]},${inrows[3]})
but I get a different error:
Invalid cursor position: AFTER_LAST
Original expression: inrows[1].
Can you tell me if it is possible to do this and if so what is the appropriate syntax?
Thanks!
Sean
I am trying to use GAD to insert rows to a database table and the documentation for the SQL task seems to indicate that this can be done but it doesn't say how to do so.
In my test project I read a set of rows from a CSV and assign them to rowset ${inrows}. I set that variable in the Input Rowset field in the sql task and set my query to
INSERT INTO MYTEST(COLUMN1, COLUMN2, COLUMN3)
but I get an error back indicating java.sql.SQLException: Invalid column index
I tried using this instead:
INSERT INTO MFTTEST(COLUMN1, COLUMN2, COLUMN3)
VALUES(${inrows[1]},${inrows[2]},${inrows[3]})
but I get a different error:
Invalid cursor position: AFTER_LAST
Original expression: inrows[1].
Can you tell me if it is possible to do this and if so what is the appropriate syntax?
Thanks!
Sean
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Hey Sean!
Inserting data from a ROWSET read of a CSV or XML or Excel is pretty simple .. and can be performed a couple ways.
The first example would be doing the readCSV then SQL Task with Insert using the Rowset Variable like you described by mapping to each field within the CSVFile Rowset Variable ...
Inserting data from a ROWSET read of a CSV or XML or Excel is pretty simple .. and can be performed a couple ways.
The first example would be doing the readCSV then SQL Task with Insert using the Rowset Variable like you described by mapping to each field within the CSVFile Rowset Variable ...
Code: Select all
The second example would be doing the readCSV then SQL Task with Insert while looping through the Rowset Variable ... <readCSV label="Read CSV file" inputFile="\MyPathtoFile\Myfile.csv" outputRowSetVariable="CSVFile" skipInvalidRecords="true" version="1.0" />
<sql label="Connect to DB" resourceId="MyDBResourceID" version="1.0">
<query label="Insert Data" inputRowSetVariable="${CSVFile}">
<statement>
INSERT INTO MyTable ( Field1, Field2, Field3 )
Values(?,?,?)
</statement>
</query>
</sql>
Code: Select all
<readCSV label="Read CSV file" inputFile="\MyPathtoFile\Myfile.csv" outputRowSetVariable="CSVFile" skipInvalidRecords="true" version="1.0" />
<forEachLoop itemsVariable="${CSVFile}" currentItemVariable="Row">
<sql label="Connect to DB" resourceId="MyDBResourceID" version="1.0">
<query label="Insert Specific Data">
<statement>
INSERT INTO MyTable ( Field1, Field2, Field3 )
VALUES ( ${Row[1]}, ${Row[2], ${Row[3] )
</statement>
</query>
</sql>
</forEachLoop>
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Rick,
First thank you very much for the quick reply! I tested both solutions, the first worked perfect and I got the second to work with a small tweak. You need to quote the string values in the insert query since you're not using a prepared statement so it ends up like this:
Many thanks!
Sean
First thank you very much for the quick reply! I tested both solutions, the first worked perfect and I got the second to work with a small tweak. You need to quote the string values in the insert query since you're not using a prepared statement so it ends up like this:
Code: Select all
With that change I got the looping method to work correctly. <forEachLoop itemsVariable="${inrows}" currentItemVariable="Row">
<print version="1.0">
<![CDATA[Inserting values('${Row[3]}','${Row[2]}','${Row[1]}')]]>
</print>
<sql resourceId="RealDevSandbox" version="1.0">
<query>
<statement>INSERT INTO MYTABLE(COLUMN1, COLUMN2, COLUMN3)
VALUES('${Row[3]}','${Row[2]}','${Row[1]}')</statement>
</query>
</sql>
</forEachLoop>
Many thanks!
Sean
3 posts
Page 1 of 1