查询在mysql和php中运行良好，但在java中运行不正常

问题描述：

I have the following mysql query in PHP and it works fine.

$strUpdate = "INSERT INTO  `batchfolder`.`newbatch` (`BatchID` ,`Batch` ,`barcode` ,`PG`)VALUES (NULL ,  '',  '1',  '')";

and is also fine when i run it directly in database.

However when I run it in java,

 try {
          conn = DriverManager.getConnection (url, userName, password);
          Statement st = conn.createStatement();
          st.execute("INSERT INTO  `batchfolder`.`newbatch` (`BatchID` ,`Batch` ,`barcode` ,`PG`)VALUES (NULL ,  '',  '1',  '')");            
          st.close();
}

It gives the following error.

Exception in thread "main" java.sql.SQLException: Field 'Pre' doesn't have a default value

Pre is the the next row in the database and it does not have a default value.

My question is, then how does this query run fine in mysql and php.

P.S BatchID is an int(10) autoincremented value in newbatch.

This is my table structure.

CREATE TABLE IF NOT EXISTS `newbatch` (
  `BatchID` int(10) NOT NULL AUTO_INCREMENT,
  `Batch` varchar(100) NOT NULL,
  `barcode` varchar(5) NOT NULL,
  `Ly` varchar(5) NOT NULL DEFAULT '0',
  `PG` varchar(5) NOT NULL,
  `Pre` varchar(5) NOT NULL,
  `Flu` varchar(5) NOT NULL,
  `FluID` varchar(100) DEFAULT NULL,
  `DateCreated` varchar(100) DEFAULT NULL,
  `Comments` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`BatchID`),
  UNIQUE KEY `FluID` (`FluID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000160 ;

我在PHP中有以下mysql查询，它运行正常。 p>

   $ strUpdate =“INSERT INTO`batchfolder` .newbatch`（`BatchID`，`Batch`，`barcode`，`PG`）VALUES（NULL，''，'1'，''）”; \  n  code>  pre> 
 
 在我直接在数据库中运行时也很好。 p> 
 
 
但是当我在java中运行它时， p  > 
 
 
  try {
 conn = DriverManager.getConnection（url，userName，password）; 
 Statement st = conn.createStatement（）; 
 st.execute（“INSERT INTO`batch folder  `.newbatch`（`BatchID`，`Batch`，`barcode`，`PG`）VALUES（NULL，''，'1'，''）“）;  
 st.close（）; 
} 
  code>  pre> 
 
 它会出现以下错误。   p> 
 
 
 线程“main”中的异常java.sql.SQLException：字段'Pre'没有默认值
  code>  pre> 
 \  n  Pre是数据库中的下一行，它没有默认值。   p> 
 
 
我的问题是，那么这个查询如何在mysql和php中正常运行。 p> 
 
 
 PS BatchID是一个 int（10）autoincremented  newbatch中的 code>值。 p> 
 
 
这是我的表结构。 p> 
 
 

  CREATE TABLE IF NOT NOT EXISTS`newbatch`（
  `BatchID` int（10）NOT NULL AUTO_INCREMENT，
`Batch` varchar（100）NOT NULL，
`barcode` varchar（5）NOT NULL，
`Li`varchar（5）NOT NULL DEFAULT'0'  ，
`PG` varchar（5）NOT NULL，
`pre`varchar（5）NOT NULL，
`Flu`varchar（5）NOT NULL，
`FluID`varchar（100）DEFAULT NULL，\  n`DateCreated` varchar（100）DEFAULT NULL，
`Reviews`varchar（500）DEFAULT NULL，
 PRIMARY KEY（`BatchID`），
 UNIQUE KEY`FluID`（`FluID`）
）ENGINE =  MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1000160; 
  code>  pre> 
  div>

答

Are you absolutely sure this actually runs in PHP? You may just not be seeing the error message.

Try this again with display_errors = 'on' and error_reporting(E_ALL | E_STRICT); This is a SQL error, it doesn't depend on the driver. The driver just displays the message to you.

UPDATE: It looks like Java is turning off MySQL's strict mode for some reason. The only thing that (to my knowledge) should be able to cause this behavior is MySQL strict mode being off.

If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

You can check the mode that your server is running on by SELECT @@GLOBAL.sql_mode;. Try that in both Java and PHP. If the results differ then that's your answer.

UPDATE2: Jep!

答

Looked at your table definition, you have Pre defined as not null, but you didnt specify a value for it in the insert, so it should show an error proper.

答

You have several database fields that are 'NOT NULL', so you must specify these in your INSERT statement. I couldn't imagine how this would work in PHP either.

查询在mysql和php中运行良好，但在java中运行不正常

相关推荐