Greeting! I am trying to convert an application from a DB2 based
program to MySQL. We are still evaluating MySQL and learning.
We are using Java and the JDBC driver mm.mysql.jdbc-1.2c to
create the database. The Java version of this program using this
driver is not creating some of the tables. The errors indicate a
syntax problem in the prepare statement in the Java code. The
original code ran on OS/2 and the sql was embedded something
similar to the following sample:
EXEC SQL CREATE TABLE Patient_Info_Table
(medical_record_#CHAR(16) NOT NULL PRIMARY
KEY,
date_of_birth CHAR(10) NOT NULL,
patient_nameCHAR(48) NOT NULL,
addr_line_one CHAR(48),
addr_line_two CHAR(48),
cityCHAR(24),
state CHAR(2),
country_codeCHAR(3),
zip_codeCHAR(10),
telephone CHAR(48),
t_and_d_stamp TIMESTAMP,
commentsCHAR(128),
previous_addr INTEGER,
guardian_info INTEGER,
emergency_info INTEGER,
total_hpps INTEGER,
supp_data INTEGER,
appt_time TIME,
appt_date DATE,
appt_location CHAR(48),
office_code CHAR(16),
fsdstatus_flag INTEGER,
galaxy VARCHAR(512)
);
This was rewritten in Java so that all tables for the application could
be created and static data inserted for a customer. The Java
program was tested on OS/2 and Windows some years ago using
a driver from IBM and the DB2 product. The sample Java code to
create the above table follows:
import COM.ibm.db2.app.*;
import COM.ibm.db2.jdbc.app.*;
import java.util.*;
import java.text.*;
import java.io.*;
import java.sql.*;
import java.sql.Types.*;
public class CreateDB
{
public PreparedStatement createPITStmt; /*
Patient_Info_Table */
public CreateDB()
{
try
{ // lines below deliberately wrapped for this E-mail
createPITStmt = con.prepareStatement(
"CREATE TABLE Patient_Info_TABLE (
medical_record_# CHAR(16) NOT NULL PRIMARY
KEY,date_of_birth CHAR(10) NOT NULL, patient_name CHAR(48)
NOT NULL,addr_line_one CHAR(48), addr_line_two CHAR(48),city
CHAR(24),state CHAR(2), country_code CHAR(3),zip_code
CHAR(10),telephone CHAR(48), t_and_d_stamp
TIMESTAMP,comments CHAR(128),previous_addr
INTEGER,guardian_info INTEGER,emergency_info
INTEGER,total_hpps INTEGER,supp_data INTEGER,appt_time
TIME,appt_date DATE, appt_location CHAR(48),office_code
CHAR(16),fsdstatus_flag INTEGER, galaxy VARCHAR(512))" );
System.out.println("Try Creating Patient_Info_Table.");
createPITStmt.executeUpdate();
SQLWarning warning = createPITStmt.getWarnings();
if ( warning != null )
{
System.out.println("Message: " + warning.getMessage());
System.out.println("SQLState: " + warning.getSQLState());
System.out.println("ErrorCode: " +
warning.getErrorCode());
}
createPITStmt.close();
System.out.println("Patient_Info_Table creation successful.");
}catch( SQLException e ) {
System.out.println("Creation of Patient_Info_Table failed.");
System.out.println("Message: " + e.getMessage() );
System.out.println("SQLState: " + e.getSQLState() );
System.out.println("ErrorCode: " + e.getErrorCode() );
}
}
public static void main(String argv[])
{
.
.
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
.
.
String url = "jdbc:db2:PEDSHPP";
.
.
con = DriverManager.getConnection(url);
.
.
CreateDB tc = new CreateDB();
}
The test program above ran perfectly and created the table on
Win9x/NT/W2K on the DB2 installation. We modified the test
program by commenting out the IBM specific import directives.
And, of course changed/used the following:
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
String url = "jdbc:mysql://localhost/PEDSHPP";
This was nothing fancy as far as MySQL installed as a standalone
workstation configuration. Is my problem with some of the data
types? If so, any comments would be appreciated with regard to
interpretation of the definition of creating tables. The following
output was captured from a Win2000 system:
Try Creating Patient_Info_Table.
Creation of Patient_Info_Table failed.
Message: Error during query: Unexpected Exception:
java.sql.SQLException
message given: Syntax error or access violation: You have an error
in your
SQL syntax near '' at line 1
SQLState: S1000
E
