Link to home
Start Free TrialLog in
Avatar of Vanavah Edwards
Vanavah Edwards

asked on

How to Fix Insert Statement into SQLite3 DB

I am trying to insert the fields from my JComponents into a table SOHEAD.  I keep getting the error below on line 49 which is the "Insert into SOHEAD" line.  The only JFormattedTextField in that line is SODATE.  The table column is DATE type size 10.  
Snippet ID=8241213 shows how I have that component delcared.
Snippet ID=8241224 shows the insert statement.

ERROR ON THE INSERT LINE -->
Clicked - Save
Table CELL_SOHEAD exists
Connection successful!
java.sql.SQLException: near "'javax.swing.JFormattedTextField[,114,54,80x20,layout=javax.swing.plaf.basic.BasicTextUI$UpdateHandler,alignmentX=0.0,alignmentY=0.0,border=javax.swing.plaf.BorderUIResource$CompoundBorderUIResource@1f0aecc,flags=296,maximumSize=,minimumSize=,preferredSize=,caretColor=sun.swing.PrintColorUIResource[r=51,g=51,b=51],disabledTextColor=javax.swing.plaf.ColorUIResource[r=184,g=207,b=229],editable=true,margin=javax.swing.plaf.InsetsUIResource[top=0,left=0,bottom=0,right=0],selectedTextColor=sun.swing.PrintColorUIResource[r=51,g=51,b=51],selectionColor=javax.swing.plaf.ColorUIResource[r=184,g=207,b=229],columns=0,columnWidth=0,command=,horizontalAlignment=LEADING]'": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.Stmt.executeUpdate(Stmt.java:102)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
      at MOCA$ActionPerformer.actionPerformed(MOCA.java:124)
      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
      at java.awt.Component.processMouseEvent(Unknown Source)
      at javax.swing.JComponent.processMouseEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)

public static JFormattedTextField sodate, delivdate;
		DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US);
		sodate = new JFormattedTextField(df);
		sodate.setValue(new Date());

Open in new window

// INSERT MEMORY VARIABLES FROM JComponents INTO TABLE
			    int rsInsert = st.executeUpdate("INSERT INTO SOHEAD " +
			    		"(sodate, buyerno, buyer) " +
			    		"VALUES '"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn");
		    	ResultSet rsKey = st.getGeneratedKeys();
		    	rsKey.next();
		    	System.out.println("Generated key - "+rsKey);

Open in new window

Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
there is no bracket open before values.

VALUES(

st.executeUpdate("INSERT INTO SOHEAD " +
                      "(sodate, buyerno, buyer) " +
                      "VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn");
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer) " +
	    		"  VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn");

Open in new window

Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn)");

Open in new window

Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
for howmany columsn you want to insert?

what is  keycolumn in VALUES ?Is it a column or value?
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

You were right.  Howevef, I am now getting a syntax error.  By the way, what is the purpose of the keycolumn in the insert line.

NEW ERROR -->
java.sql.SQLException: near "keycolumn": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.Stmt.executeUpdate(Stmt.java:102)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

It is the values from the fields in my form that I am inserting into the table.
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

The data from the fields in the form are of different data types.
Sodate is a JFormattedTexField Date; buyer, buyer are text; some are numeric
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image

add one more column in INSERT statement.
   
        st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer,keycolumn) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"'", keycolumn+")");

Open in new window

Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
if you are inserting 4 columns use above one;
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
as i told you instead of Statement use prepared statement.

st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer,keycolumn) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"'," keycolumn+")");

Open in new window

Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

I will take your advice and use prepared statements.  I removed the keycolumn as not in the table.  Below is the new error. The "'SOLAR DYNAMICS LTD.'" is the value in the '"+mimporter+"' section of the insert.

java.sql.SQLException: near "'SOLAR DYNAMICS LTD.'": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.Stmt.executeUpdate(Stmt.java:102)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
paste your code.

 st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer,keycolumn) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"')");

Open in new window

Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
sorry remove keycolumn
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
 pstmt.executeUpdate("INSERT INTO SOHEAD " +
                      "(sodate, buyerno, buyer,keycolumn) VALUES (?,?,?)");
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
pstmt.executeUpdate("INSERT INTO SOHEAD " +
                      "(sodate, buyerno, buyer) VALUES (?,?,?)");

Open in new window

Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

I changed to the prepared statement.  Same error -->

Clicked - Save
Table CELL_SOHEAD exists
Connection successful!
java.sql.SQLException: near "'SOLAR DYNAMICS LTD.'": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
      at org.sqlite.Conn.prepareStatement(Conn.java:231)
      at org.sqlite.Conn.prepareStatement(Conn.java:224)
      at org.sqlite.Conn.prepareStatement(Conn.java:213)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
PreparedStatement pst = connection.prepareStatement(
		    		  "INSERT INTO SOHEAD " +
		    		  "(sodate, buyerno, buyer) " +
		    		  "VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"'");
		    	int rsInsert = pst.executeUpdate();
			    ResultSet rsKey = pst.getGeneratedKeys();
		    	rsKey.next();
		    	System.out.println("Generated key - "+rsKey);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

i dont understand your last post.  You have values and pst.setSTring(1,sodate) etc.  You are saying I have to use both or use the setString syntax.
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

I change to your new code here is the new error -->

Connection successful!
java.sql.SQLException: near "pst": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
      at org.sqlite.Conn.prepareStatement(Conn.java:231)
      at org.sqlite.Conn.prepareStatement(Conn.java:224)
      at org.sqlite.Conn.prepareStatement(Conn.java:213)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:51)
      at MOCA$ActionPerformer.actionPerformed(MOCA.java:124)
PreparedStatement pst = connection.prepareStatement(
		    		  "INSERT INTO SOHEAD " +
		    		  "pst.setString(1,sodate);" +
		    		  "pst.setString(2,custno);" +
		    		  "pst.setString(3,mimporter);");
		    	int rsInsert = pst.executeUpdate();
			    ResultSet rsKey = pst.getGeneratedKeys();
		    	rsKey.next();
		    	System.out.println("Generated key - "+rsKey);

Open in new window

SOLUTION
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

I studied the examples and change the code.  But I am getting this error.
ERROR -->>
Clicked - Save
Table CELL_SOHEAD exists
Connection successful!
Exception in thread "AWT-EventQueue-0" java.lang.ArrayIndexOutOfBoundsException: 3
      at org.sqlite.PrepStmt.batch(PrepStmt.java:131)
      at org.sqlite.PrepStmt.setString(PrepStmt.java:186)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:55)
PreparedStatement pst = connection.prepareStatement(
			    		  "INSERT INTO CELL_SOHEAD " +
			    		  "(sonum, sodate, buyerno, buyer) " +
			    		  "VALUES (?, ?, ?, ?)");

				  		  pst.setString(2,"+sonum+");
						  pst.setString(3,"+sodate+");
						  pst.setString(5,"+custno+");
						  pst.setString(6,"mimporter+");

Open in new window

Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

I never heard back from you when you kenw I neded help.  I am new to Java.  However, I tried the code you posted and studied the code on the site you suggested.  By trial and error based upon all your recommendations, I am happy to say I have solved the problem.  I would like to close this session and award your points.  But there is one thing I need your help.  How can I use
 pst.setString(2, sodate.getText()); to store a date.  
Unless I set the date in the database to DATETEXT it wouldn't work.  If I say sodate.getDate() I get a String Error on that line.  Is it possible to set a real date instead of a DATETEXT.
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

Didi not get ever back to me after a while?
Avatar of chaitu chaitu
chaitu chaitu
Flag of India image
pst.setDate
or
pst.setTimestamp methods are there;check the methods in prepared statements
Avatar of Vanavah Edwards
Vanavah Edwards

ASKER

i tried pst.setDate(2, sodate);  and I got this error -->
The method setDate(int, Date) in the type PreparedStatement is not applicable for the arguments (int, JFormattedTextField)
My sodate is a JFormattedTextField
For the timestamp , I got this error --->>
The method setTimestamp(int, Timestamp) in the type PreparedStatement is not applicable for the arguments (int, JFormattedTextField)
Only pst.setString(2, sodate.getText()); works and I must set the DB to DATETEXT