#40 Inserted values are all "0" or NULL

[Anonymous]

Hi,

I'm a new comer of MySQL and its JDBC driver. Troubled
by my first use.

I create a table "USER" as below,

mysql> desc USER;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| USER_ID | varchar(20) | | PRI | | |
| PASSWD | varchar(20) | | | | |
| MOBILE_NO | bigint(15) unsigned | YES | | NULL
| |
+-----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

My problem is, when I try to insert a record to "USER" table
using JDBC dirver, the inserted values are all "0" or null as
below (No Exception, so my program to load the driver and
getConnection is ok. But when I try to insert second time
with different value, SQLException: duplicated key '0' in line
1),

mysql> select * from USER;
+---------+--------+-----------+
| USER_ID | PASSWD | MOBILE_NO |
+---------+--------+-----------+
| 0 | 0 | NULL |
+---------+--------+-----------+
1 row in set (0.00 sec)

Can anybody help? I have to submit my solution by this
weekend. Really emergent to me :(

Thanks & Best Regards,
Edward (edwardfan@singtel.com)

[George Smith]

Logged In: YES
user_id=271280

??? Not enough information to help... what is the method you
use to add the data to the database????

i use....
Connection C =
DriverManager.getConnection("jdbc:mysql://myHost/MyDB?user=MyUserName&password=MyPassword"
);
Statement stmnt = C.createStatement();
String command = new String();
String lockCommand = new String( "LOCK TABLES az_"+
outputFileName + " WRITE" );
String unlockCommand = new String( "UNLOCK TABLES" );
command = "INSERT INTO az_"+outputFileName
+ " VALUES( '"
+ key() + "','"
+ _aircraft_ident + "','"
+ _aircraft_cid + "','"
+ _message_type + "','"
+ _date + "','"
+ removeColons(_time) + "','"
+ _departure + "','"
+ _destination + "','"
+ _arrival_time + "','"
+ _physical_class + "','"
+ _user_class + "' )";
stmnt.executeUpdate( lockCommand );
stmnt.executeUpdate( command );
stmnt.executeUpdate( unlockCommand );
C.close();

}

catch( SQLException e ){
System.out.println( e.getMessage() );
System.out.println( e.getSQLState() );
//System.out.println( e.getErrorCode() );
}

}

and this seems to work just fine...

[Nobody/Anonymous]

Logged In: NO

Thx!

I have beaten the bug in time. Sorry for my post here so late.

The problem is mainly my mistake. I use wrong syntax for INSERT
statement like,

insert into USER values (USER_ID = ?, PASSWD = ?,
MOBILE_NO = ?)

After I changed to,

insert into USER (USER_ID, PASSWD, MOBILE_NO) values
(?, ?, ?)

then my program runs well.

Maybe it's still a mini bug of the mm JDBC driver. It should throw
a SQLException with error info like "syntax error" but not
insert '0' value into database. That's really confusing.

Thanks for your time!

[Mark Matthews]

Logged In: YES
user_id=116907

Maybe reading the MySQL manual might help?

http://www.mysql.com/doc/I/N/INSERT.html