Database Inserts

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.

Database Inserts

Postby SeanC » Fri Aug 30, 2013 5:19 pm

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
SeanC
 
Posts: 5
Joined: Tue Feb 28, 2012 1:52 am

Re: Database Inserts

Postby Support_Rick » Fri Aug 30, 2013 5:52 pm

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

Code: Select all
<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>

The second example would be doing the readCSV then SQL Task with Insert while looping through the Rowset Variable ...

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
Sr. Product Specialist
(402) 944.4242
(800) 949-4696
Support_Rick
Support Specialist
 
Posts: 172
Joined: Tue Jul 17, 2012 2:12 pm

Re: Database Inserts

Postby SeanC » Sat Aug 31, 2013 4:40 pm

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:

Code: Select all
      <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(&apos;${Row[3]}&apos;,&apos;${Row[2]}&apos;,&apos;${Row[1]}&apos;)</statement>
            </query>
         </sql>

      </forEachLoop>


With that change I got the looping method to work correctly.

Many thanks!

Sean
SeanC
 
Posts: 5
Joined: Tue Feb 28, 2012 1:52 am


Return to Community Forum

Who is online

Users browsing this forum: Yahoo [Bot] and 1 guest

cron