Bug #7754 REPLACE INTO SELECT PreparedStatement fails

• Submitted: 10 Jan 2005 3:53
• Modified: 12 Jan 2005 14:07
• Reporter: Test Test
• Status: Can't repeat
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 3.1.6
• OS: Linux (Linux)
• CPU Architecture: Any

[10 Jan 2005 3:53] Test Test

Description:
A "REPLACE INTO <table> VALUES () SELECT <values>" statement fails if executed within a PreparedStatement. It throws the following exception:

SQLException: This command is not supported in the prepared statement protocol yet

Note that a simple REPLACE or SELECT works fine. It's only when combined in the one statement it causes a problem.

How to repeat:
Using mysql 4.1.7 on Linux issue the following Java code (windows sun jdk 1.5):
       
 String statement = "REPLACE INTO my_table VALUES (my_col) SELECT my_col FROM my_table2";
 PreparedStatement stmt = connection.prepareStatement(statement);
 
throws SQLException on the prepareStatement() call. 

where

CREATE TABLE `my_table` (
  `balance` decimal(10,2) NOT NULL default '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `my_table2` (
  `balance` decimal(10,2) NOT NULL default '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
            

Suggested fix:
Not sure if this problem is in the connector or at the database level?

[12 Jan 2005 14:07] Aleksey Kishkin

the command 'replace into ... values () select ...'  has syntax error - 'values' must not be here.

without 'values' (and with 'my_col' replaced to 'balance'), this command does not produce any exceptions. I tested on C and on java (files attached)

[12 Jan 2005 14:14] Aleksey Kishkin

test on java

Attachment: bug7754.java (text/x-java), 850 bytes.

[12 Jan 2005 14:14] Aleksey Kishkin

test on C

Attachment: prep_test.c (text/x-csrc), 1.32 KiB.