Insert records from load script to MySQL DB

[evgeniystuchalk]

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')

[girirajsinh]

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]

Same error

[petter]

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]

double quotes also gives same error

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

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

[petter]

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]

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 ?