CODEWITHSUNDEEP
javamysqljdbcprepared-statement
Sloppy
Sloppy

Reputation: 143

Why can't I set a variable in a prepared statement in MySQL?

I'm running a script to insert some data in a MySQL database, and it runs properly in the MySQL workbench. However, when I try to run it from Java via the JDBC, I get an error. The script is:

INSERT INTO `pa_record` (`username`, `pa_record_type`, `record_time`) VALUES (?, ?, CURRENT_TIMESTAMP);
SET @record_id := 1;
INSERT INTO `pa_crud` (`pa_record_id`, `table_name`) VALUES (@record_id, ?);

The error I get from the JDBC is

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 'SET @record_id := 1; INSERT INTO pa_crud (pa_record_id, table_name) VALUES' at line 2

Any ideas?

Upvotes: 0

Views: 545

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

Many (most?) database access libraries do not allow multiple statements in a single execute; those that do, usually need to have the feature activated with a setting change. It's likely not complaining about the SET, but that you had anything after the end of the first query at all.

Upvotes: 1

Related Questions