Writing multiple rows in one line, mysql statement. No strings are saved

[Former Member]

Hi, I dont know how to make multiple rows inputs into the table in one sentence. Ive got an error: "java.sql.SQLException: Statement parameter 2 not set" provided by code below. It works fine for one Date row (saves in the db corectly) however when I use for two different date types two single statements it will write databes two times putting 2x Null in the every other row. I dont want that, I need it to be written in one connection and statement.

try{
	String userName = "user";
		String password = "pass";
		String url = "jdbc:mysql://dbadress/dbname";
		Class.forName ("com.mysql.jdbc.Driver").newInstance();
		Connection con = DriverManager.getConnection (url, userName, password);

          Statement  st1=con.createStatement();
	  Date BirthDate = wdContext.currentPeopleElement().getBirthDate();  //People - context node, BirthDate - context attribute
	  Date FillDate = wdContext.currentContextElement().getFillDate();
	  String sql1 = "INSERT INTO table1 (BirthDate1,FillDate1) VALUES (?,?)"; //BirthDate1,FillDate1 - rows in the "table1" table
	  PreparedStatement stmt1 = con.prepareStatement(sql1);
	  stmt1.setDate(1, BirthDate);
	  stmt1.setDate(1, FillDate);
	  stmt1.executeUpdate();
	  stmt1.close();
} 
	catch(Exception e) {
	wdComponentAPI.getMessageManager() .reportWarning( e.toString() ); }

I would like to know how to fix it.

Other issue is that code below:

try{
	String userName = "user";
		String password = "pass";
		String url = "jdbc:mysql://dbadress/dbname";
		Class.forName ("com.mysql.jdbc.Driver").newInstance();
		Connection con = DriverManager.getConnection (url, userName, password);

          Statement  st1=con.createStatement();
	  Date BirthDate = wdContext.currentPeopleElement().getBirthDate();  //People - context node, BirthDate - context attribute
	  String sql1 = "INSERT INTO table1 (BirthDate1) VALUES (?)"; //BirthDate1 row in the "table1" table
	  PreparedStatement stmt1 = con.prepareStatement(sql1);
	  stmt1.setDate(1, BirthDate);
          stmt1.executeUpdate();
	  stmt1.close();
} 
	catch(Exception e) {
	wdComponentAPI.getMessageManager() .reportWarning( e.toString() ); }

works perfectly fine. It does write date in the database as shown on the monitor hoever it doesnt work if we change Date type with String. Of course context nodes are String type as well. Code below will write "Null" value in the db. Dont know why. Of course I write some string down in the input field and save after, just like do it with Date type which works fine. Please advice.

try{
	String userName = "user";
		String password = "pass";
		String url = "jdbc:mysql://dbadress/dbname";
		Class.forName ("com.mysql.jdbc.Driver").newInstance();
		Connection con = DriverManager.getConnection (url, userName, password);
        Statement  st2=con.createStatement();
	String signature = wdContext.currentContextElement().getsignature(); //signature - context
	String sql2 = "INSERT INTO table1 (signature1) VALUES (?)"; //signature1 - name of row in the table1
	PreparedStatement stmt2 = con.prepareStatement(sql2);
	stmt2.setString(1, signature);
	stmt2.executeUpdate();
	stmt2.close();
} 
	catch(Exception e) {
	wdComponentAPI.getMessageManager() .reportWarning( e.toString() ); }

Regards, Blamer.

[Former Member]

Hi,

Fix for your first issue

I dont know how to make multiple rows inputs into the table in one sentence. Ive got an error: "java.sql.SQLException: Statement parameter 2 not set" provided by code below. It works fine for one Date row (saves in the db corectly) however when I use for two different date types two single statements it will write databes two times putting 2x Null in the every other row. I dont want that, I need it to be written in one connection and statement.
try{
	String userName = "user";
		String password = "pass";
		String url = "jdbc:mysql://dbadress/dbname";
		Class.forName ("com.mysql.jdbc.Driver").newInstance();
		Connection con = DriverManager.getConnection (url, userName, password);
          Statement  st1=con.createStatement();
	  Date BirthDate = wdContext.currentPeopleElement().getBirthDate();  //People - context node, BirthDate - context attribute
	  Date FillDate = wdContext.currentContextElement().getFillDate();
	  String sql1 = "INSERT INTO table1 (BirthDate1,FillDate1) VALUES (?,?)"; //BirthDate1,FillDate1 - rows in the "table1" table
	  PreparedStatement stmt1 = con.prepareStatement(sql1);
	  stmt1.setDate(1, BirthDate);
	  stmt1.setDate(1, FillDate);
	  stmt1.executeUpdate();
	  stmt1.close();
} 
	catch(Exception e) {
	wdComponentAPI.getMessageManager() .reportWarning( e.toString() ); }
I would like to know how to fix it.

Change the line stmt1.setDate(1, FillDate); to stmt1.setDate(2, FillDate);

As a workarond for your following issue

works perfectly fine. It does write date in the database as shown on the monitor hoever it doesnt work if we change Date type with String. Of course context nodes are String type as well. Code below will write "Null" value in the db. Dont know why. Of course I write some string down in the input field and save after, just like do it with Date type which works fine. Please advice.
try{
	String userName = "user";
		String password = "pass";
		String url = "jdbc:mysql://dbadress/dbname";
		Class.forName ("com.mysql.jdbc.Driver").newInstance();
		Connection con = DriverManager.getConnection (url, userName, password);
        Statement  st2=con.createStatement();
	String signature = wdContext.currentContextElement().getsignature(); //signature - context
	String sql2 = "INSERT INTO table1 (signature1) VALUES (?)"; //signature1 - name of row in the table1
	PreparedStatement stmt2 = con.prepareStatement(sql2);
	stmt2.setString(1, signature);
	stmt2.executeUpdate();
	stmt2.close();
} 
	catch(Exception e) {
	wdComponentAPI.getMessageManager() .reportWarning( e.toString() ); }

Try as follows

PreparedStatement stmt2 = con.prepareStatement(sql2);
	stmt2.setString(1, (signature == null ? "Value missing" : signature));
	stmt2.executeUpdate();
	stmt2.close();

If the context attribute is null it will enter the text "Value Missing",

Regards

Ayyapparaj