Error in SQL insert

Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
11 posts Page 2 of 2

falak

Posts: 8
Joined: Tue Dec 27, 2016 3:03 am

Post by falak » Wed Jan 18, 2017 1:20 am
You are right but "insert into" not work for dynamic insertion...its good for adding specific columns and specific records.My code inserts data into table but not correct index

Code: Select all
<project name="loop_test" mainModule="Main" version="2.0" logLevel="verbose">

	<module name="Main">

		<readCSV label="Read CSV File" inputFile="E:\Program Files\Linoma Software\GoAnywhere\userdata\webdocs\falak\read10.csv" outputRowSetVariable="csvData" skipFirstRow="true" processedInputFilesVariable="file" version="1.0" disabled="false" />
		


		<readCSV inputFile="E:\Program Files\Linoma Software\GoAnywhere\userdata\webdocs\falak\read10 -header.csv" outputRowSetVariable="headers" version="1.0" />

		<sql label="Create Table" resourceId="MySQL Landing" driver="com.mysql.jdbc.Driver" version="1.0" disabled="false">
			<query label="CREATE TABLE ABC">
				<statement>CREATE TABLE goanywhere (dummycolumn VARCHAR(30) DEFAULT NULL) ENGINE = MyISAM CHARACTER SET latin1;</statement>
			</query>
		</sql>

		<modifyRowSet label="Rowset modify" inputRowSetVariable="${csvData}" outputRowSetVariable="data_modify" existingColumns="true" startAtRow="1" version="1.0" />

		<forEachLoop itemsVariable="${headers}" currentItemVariable="add_header" currentIterationVariable="loop" beginIndex="1" step="1">
			<forLoop beginIndex="1" endIndex="10" step="1" currentIterationVariable="Iloop" currentIndexVariable="rowscounting">

				<sql label="Adding Headers to Table" resourceId="MySQL Landing" version="1.0" disabled="false">
					<query outputVariable="allheaders">
						<statement>ALTER TABLE `goanywhere` ADD COLUMN `${headers[Iloop]}`VARCHAR(30) DEFAULT NULL;</statement>
					</query>
				</sql>

			</forLoop>

			<print version="1.0">
				<![CDATA[${allheaders}]]>
			</print>

		</forEachLoop>

		<sql label="Drop Dummy Column" resourceId="MySQL Landing" version="1.0" disabled="false">
			<query>
				<statement>ALTER TABLE goanywhere DROP `dummycolumn`</statement>
			</query>
		</sql>

		<forEachLoop label="Headers foreach" itemsVariable="${headers}" currentItemVariable="head_container" currentIterationVariable="head" currentIndexVariable="headindex" beginIndex="1" disabled="true">
			<forEachLoop label="Data foreach" itemsVariable="${csvData}" currentItemVariable="data_container" currentIterationVariable="data" currentIndexVariable="dataindex" beginIndex="1" step="1">
				<forLoop beginIndex="1" endIndex="10" step="1" currentIterationVariable="i" currentIndexVariable="index" disabled="false">

					<sql label="Data Insertion" resourceId="MySQL Landing" version="1.0" disabled="false">
						<query label="Insert Into Table">
							<statement>INSERT INTO goanywhere (${headers[i]}) Values(&apos;${csvData[i]}&apos;);</statement>
						</query>
					</sql>

				</forLoop>
			</forEachLoop>
		</forEachLoop>
	</module>

</project>
11 posts Page 2 of 2