Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
evgeniystuchalk
Partner - Creator II
Partner - Creator II

Insert records from load script to MySQL DB

Hello!

i'm trying insert records in mysql db with this code:

LIB CONNECT TO 'amo.queue (win-lng2sl4osbd_qlik.acc)';

test:

load * inline

[1,2,3,4

cool, 456, qq, z

it, 789, ww,x

works, 1230, ee, y

yo,1231, rr, w];

// Getting number of records in Table

LET V_ROWS = NoOfRows('test');

// Loop through the records and insert into Database table

FOR V_ROW = 0 TO V_ROWS - 1

LET V_FIELD1 = Peek('1',V_ROW);

LET V_FIELD2 = Peek('2',V_ROW);

LET V_FIELD3 = Peek('3',V_ROW);

LET V_FIELD4 = Peek('4',V_ROW);

SQL INSERT INTO test_odbc.new_table(1,2,3,4) VALUES('$(V_FIELD1)','$(V_FIELD2)', '$(V_FIELD3)','$(V_FIELD4)');

NEXT;

its returns error, whats wrong?

ErrorSource: Microsoft OLE DB Provider for ODBC Drivers, ErrorMsg: [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.53-0+deb7u1]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,2,3,4) VALUES('cool','456', 'qq','z')' at line 1

Ошибка произошла здесь:

SQL INSERT INTO test_odbc.new_table(1,2,3,4) VALUES('cool','456', 'qq','z')

6 Replies
girirajsinh
Creator III
Creator III

Hi Evgeny

Please try

SQL INSERT INTO test_odbc.new_table([1],[2],[3],[4]) VALUES('$(V_FIELD1)','$(V_FIELD2)', '$(V_FIELD3)','$(V_FIELD4)');

evgeniystuchalk
Partner - Creator II
Partner - Creator II
Author

Same error

petter
Partner - Champion III
Partner - Champion III

MySQL wants the list of of columns to be the names of the columns - it does not accept the column number. If you're column names are the numbers you have to quote them within matching double quotes.

So you statement should look like this:

SQL INSERT INTO test_odbc.new_table("1","2","3","4") VALUES('$(V_FIELD1)','$(V_FIELD2)', '$(V_FIELD3)','$(V_FIELD4)');



evgeniystuchalk
Partner - Creator II
Partner - Creator II
Author

double quotes also gives same error

Ошибка произошла здесь:

SQL INSERT INTO test_odbc.new_table("1","2","3","4") VALUES('cool','456', 'qq','z')

petter
Partner - Champion III
Partner - Champion III

1) Does the table test_odbc.new_table already exist?

2) Do the account you are using have the rights to insert data into this table?

3) Lastly - are the columns all character type of columns? It looks like a couple of the columns are numeric?

girirajsinh
Creator III
Creator III

Ok lets try to break it down.

Are you able to run the same command in SQL Server ? e.g. MS SQL Server management Studio ?