CODEWITHSUNDEEP
mysql
rain_
rain_

Reputation: 794

SQL syntax error using prepared statements and variables

I have three stored procedures for three different ERPs making almost exactly the same operations, so I decided to make just one, and use parameters. Some of the field names change depending on the parameters, so I set them in variables. Then I am using prepared statements to make it work.

A working update that does not use prepared statements is as follows:

update lt_erp_barcodes
set barcode_list = concat(',', curr_barcode, barcode_list)
where cod_navision = curr_erp_code;

It updates the current barcode_list adding curr_barcode at the beggining of the list, and adding also a comma. (,curr_barcode,the rest of the list,)

My approach to make it work with prepared statement is:

set @erp_code_field_name = "cod_navision";
set @curr_erp_code = '12345';
set @curr_barcode = '123123123';
set @q1 = concat('update lt_erp_barcodes
                  set barcode_list = (\',\'', @curr_barcode, ' barcode_list)
                   where ', @erp_code_field_name, ' = ', @curr_erp_code);
prepare update_barcode_list from @q1;
execute update_barcode_list;

But when I call to prepare the following error is raised:

0 228 10:30:17 prepare update_barcode_list from @q1 Error Code: 1064. 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 '123123123 barcode_list where cod_navision = 12345' at line 2 0.047 sec

How could I make the second block of code work the same as the first one? Thanks

Upvotes: 0

Views: 189

Answers (3)

rain_
rain_

Reputation: 794

Thank you for all your answers. I finally achieved it as follows:

set @q1 = concat('update lt_main_barcodes_v2
              set barcode_list = concat(\',', @curr_barcode, '\', barcode_list)
               where ', @erp_code_field_name, ' = ', @curr_erp_code);

I corrected syntax errors (thanks James) and added another concat(). Now, MySQL understands barcode_list is a field. Selecting @q1 gives brings the following output:

update lt_erp_barcodes
set barcode_list = concat(',123123123', barcode_list)
where cod_navision = 12345

Upvotes: 0

James Scott
James Scott

Reputation: 1084

Your code is generating non legal SQL in the @Q1 variable:

DELIMITER $

DROP PROCEDURE IF EXISTS procTest$

CREATE PROCEDURE procTest()
BEGIN

    set @erp_code_field_name := "cod_navision";
    set @curr_erp_code := '12345';
    set @curr_barcode := '123123123';
    set @q1 := concat('update lt_erp_barcodes
                      set barcode_list = (\',\'', @curr_barcode, ' barcode_list)
                       where ', @erp_code_field_name, ' = ', @curr_erp_code);

    SELECT @q1;                       

    prepare update_barcode_list from @q1;
    execute update_barcode_list;

END$

DELIMITER ;

CALL procTest();

See the result here:

update lt_erp_barcodes
                      set barcode_list = (','123123123 barcode_list)
                       where cod_navision = 12345

What is the actual result you are trying to achieve? Let me know if you still can't solve this,

James

Upvotes: 1

nacho
nacho

Reputation: 5397

You may need to take off the ' barcode_list' from your @q1

set @q1 = concat('update lt_erp_barcodes
                  set barcode_list = (\',\'', @curr_barcode, ') where ',
                  @erp_code_field_name, ' = ', @curr_erp_code);

Anyway, you should change it as @Phylogenesis told you in its comment.

Upvotes: 0

Related Questions