mysql - Syntax error when preparing statements in stored procedure -
in game android users can login via google+, facebook, twitter:
when app connects mysql/php backend sends list of social ids , store them sid column in social table:
+--------+---------------+------+-----+---------+-------+ | field | type | null | key | default | | +--------+---------------+------+-----+---------+-------+ | sid | varchar(180) | no | pri | null | | | given | varchar(180) | no | | null | | | family | varchar(180) | yes | | null | | | city | varchar(180) | yes | | null | | | photo | varchar(1000) | yes | | null | | | stamp | int(11) | no | | null | | | uid | int(11) | no | | null | | +--------+---------------+------+-----+---------+-------+ in other table called users keep autoincremented user ids uid column , use track games , achievements (column medals):
+---------------+--------------+------+-----+---------+----------------+ | field | type | null | key | default | | +---------------+--------------+------+-----+---------+----------------+ | uid | int(11) | no | pri | null | auto_increment | | created | int(11) | no | | null | | | stamp | int(11) | no | | null | | | banned_until | datetime | yes | | null | | | banned_reason | varchar(180) | yes | | null | | | medals | int(11) | no | | 0 | | +---------------+--------------+------+-----+---------+----------------+ in php login script try merge social accounts whenever possible -
by taking received sids, finding corresponding uids , taking lowest found uid , updating records in social table use lowest uid.
this works in php, trying move merging functionality mysql stored procedure 2 prepared statements:
delimiter $$$ drop procedure if exists merge_users; create procedure merge_users(in in_sids varchar(255)) begin declare sql_1 varchar(255); declare sql_2 varchar(255); declare out_uid varchar(255); set sql_1 = concat('select min(uid) out_uid social sid in (', in_sids, ')'); prepare sth_1 sql_1; execute sth_1; deallocate prepare sth_1; if found_rows() > 0 set sql_2 = concat('update social set uid=', out_uid, ' sid in (', in_sids, ')'); prepare sth_2 sql_2; execute sth_2; deallocate prepare sth_2; else insert users(created, stamp, medals, green, red) values (unix_timestamp(), unix_timestamp(), 0, 0, 0); select last_insert_uid() out_uid; end if; select out_uid; end $$$ unfortunately, above code prints syntax error message:
error 1064 (42000): have error in sql syntax; check manual execute sth_1; deallocate prepare sth_1; if foun' @ line 8
Comments
Post a Comment