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

SeanC

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

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

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by 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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

SeanC

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

Post by 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
3 posts Page 1 of 1