How can enter / Handle Default value and Auto increment fields on MYSQL

asdfg · #1 10-Mar-2008, 06:41

I Am using MYSQL,

Database Created using this SQL COmmand

Code:

String CopiesTblCreate = "CREATE TABLE Copies (Access_No INT NOT NULL AUTO_INCREMENT,"+
		   "ISBN CHAR(10) NOT NULL,"+		                "Received_Date DATE,"+	
	                "Availabilitty BOOLEAN NOT NULL DEFAULT 1,"+ 	                "PRIMARY KEY (Access_No),"

While entering on Java PreparedStatement

I Gave the Values Like this

JAVA Code:

String Ins_Copy  = "Copies (Access_No,ISBN,Received_Date,Availabilitty),VALUES (?,?,?)";

PreparedStatement prpstmnt = conn.prepareStatement(Ins_Copy);
			prpstmnt.setInt(1,null);
			prpstmnt.setString(2,isbn);
			prpstmnt.setInt(3,1);
			//other will asign as default 1
			prpstmnt.executeUpdate();

How Can I assign the values,

It gives the error as follows

Quote:

java.sql.SQLException: Syntax error or access violation, message from server: "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 'Copies
(Access_No,ISBN,Received_Date,Availabilitty),VALUE S (0,'555555',1)' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:1977)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:11 63)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1272)
at com.mysql.jdbc.Connection.execSQL(Connection.java: 2236)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1741)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1588 )
at haleemLibraryForSEUSL.MenusystemCompleted1.inputDa taToCopiesBookDatabase(MenusystemCompleted1.java
: 444)
at haleemLibraryForSEUSL.MenusystemCompleted1.inputDa taToBookDatabase(MenusystemCompleted1.java:421)
at haleemLibraryForSEUSL.MenusystemCompleted1.librari anadministartiontask(MenusystemCompleted1.java:261 )
at haleemLibraryForSEUSL.MenusystemCompleted1.main(Me nusystemCompleted1.java:103)

Here My Target is,
1. I want to set Access_No automatically one added plus one with previous number
2. I want to automatically add 1 on the available field

Please help on that

TurboPT · #2 10-Mar-2008, 10:25

That is an improper SQL insertion statement.

The 'insert' command should look something like this:

Code:

INSERT INTO tbl_name (col1,col2) VALUES (2,15);

Also, since the first column is specified as 'AUTO_INCREMENT', that field does not need to be specified as a parameter. The DB will use the next number.
The insert for that table really only needs: (as Availabilitty also defaults to 1)

Code:

"INSERT INTO Copies (ISBN, Received_Date) VALUES (?,?)"

HTH

asdfg · #3 10-Mar-2008, 10:34

Small Correction on the above posts,

Access_No field is Auto incremented field

2. I want to automatically add 1 on the availablitty field (Which is boolean Type)

Please help on that, I need to increamented each new copies entering auto matically, While that time The Access number will want to increase

Normally We can Use PreparedStatement to that

But, stmt.setInt(1,-----) ???

How can I call this???

Regards,

TurboPT · #4 10-Mar-2008, 10:36

Double check post #2, I added a little more info.

Note that in MySQL, 'boolean' actually resolves [gets converted] to type 'tinyint'. MySQL calls it a 'synonym', as shown here.

asdfg · #5 10-Mar-2008, 10:41

I checked now and used the statement without assigning the auto incremented and default value field, But It gives the following error

Quote:

The data wold not be added to the copy data BaseNo value specified for parameter 1

java.sql.SQLException: No value specified for parameter 1
at com.mysql.jdbc.PreparedStatement.fillSendPacket(Pr eparedStatement.java:2264)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1715)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:158

at haleemLibraryForSEUSL.MenusystemCompleted1.inputDa taToCopiesBookDatabase(MenusystemCompleted1.java:4 44)
at haleemLibraryForSEUSL.MenusystemCompleted1.inputDa taToBookDatabase(MenusystemCompleted1.java:421)
at haleemLibraryForSEUSL.MenusystemCompleted1.librari anadministartiontask(MenusystemCompleted1.java:261 )
at haleemLibraryForSEUSL.MenusystemCompleted1.main(Me nusystemCompleted1.java:103)

TurboPT · #6 10-Mar-2008, 10:56

So what are your parameter values?
Here's my interaction:

Code:

mysql> show create table copies;
+--------+-------------------------------------------------+
| Table  | Create Table                                   |
+--------+-------------------------------------------------+
| copies | CREATE TABLE `copies` (
  `Accecss_No` int(11) NOT NULL auto_increment,
  `ISBN` char(10) NOT NULL,
  `Received_date` date default NULL,
  `Avail` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`Accecss_No`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------+
1 row in set (0.01 sec)

mysql> INSERT INTO copies(ISBN, received_date) VALUES ('100-20-300', NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from copies;
+------------+------------+---------------+-------+
| Accecss_No | ISBN       | Received_date | Avail |
+------------+------------+---------------+-------+
|          1 | 100-20-300 | 2008-03-10    |     1 |
+------------+------------+---------------+-------+
1 row in set (0.00 sec)

Note that the INSERT only uses two values, so I say again, what were the parameters?

asdfg · #7 10-Mar-2008, 11:15

Turbo

This is my method to inserting the Copies Inserting method

JAVA Code:


public static void inputDataToCopiesBookDatabase(Connection conn, Book agetBk) {
	System.out.println();
	for (int i = 0; i < agetBk.getnoOfCopies(); i++) {
		try {
			PreparedStatement prpstmnt = conn.prepareStatement(inserting [3]);
			prpstmnt.setString(1,"");
			prpstmnt.setString(2, agetBk.getisbn());
			prpstmnt.setInt(3,1);
			//other will asign as default 1
			prpstmnt.executeUpdate();
			
		} catch (SQLException e) {
				System.err.println("The data wold not be added to the copy data Base"+ e.getMessage());
			e.printStackTrace();
		}
		
	}

That my previous posts has the database creating structure,

Only I need to add following three data to the copies Table

But I will use the loop for entering the copies, in that case

Access_No -------------- integer Autoincrement

isbn ----------------- String

Availability ---------------- only take two values 0/1

But Above Code Giving error while executing the above method

TurboPT · #8 10-Mar-2008, 11:24

Quote:

Originally Posted by asdfg

PreparedStatement prpstmnt = conn.prepareStatement(inserting [3]);

What is inserting[3] ?? A string? If so, show the whole string.

asdfg · #9 10-Mar-2008, 11:30

Relace the inserting [3] with Ins_Copy

String Ins_Copy = "Copies (Access_No,ISBN,Availabilitty),VALUES (?,?,?)";

TurboPT · #10 10-Mar-2008, 11:33

As I said in post #2, that is invalid SQL to do an INSERT. Revisit post #2 please.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL

Re: How can enter / Handle Default value and Auto increment fields on MYSQL