Results 1 to 17 of 17
  1. #1
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default Sql insert into blank character

    Sorry for my English
    Hello,
    I have a trivial problem, but I can not get out of it:

    when I do a insert into xx (a, b) values ​​('string1','')...

    postgre from shell:
    Column: string1
    Column b:
    (b correct column)

    String string1 = "string1";
    String string2 = "";
    ......." insert into xx (a, b) values ​​('"+ string1 + "','''+ string2 +"');"



    Java:
    Column: string1
    Column B:''
    (column b wrong)

    Thanks!

  2. #2
    Junky's Avatar
    Junky is offline Grand Poobah
    Join Date
    Jan 2011
    Location
    Dystopia
    Posts
    3,755
    Rep Power
    7

    Default

    Try using a PreparedStatement instead.

  3. #3
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    same problem also with preare statement

  4. #4
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,792
    Rep Power
    19

    Default

    What is the difference between the two?
    Are you saying that the first one is putting NULL into column B and the second is putting something else?

    I can't quite make out what you're saying is different.

    Also, can you show us the code you used for using a PreparedStatement.

  5. #5
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    the second shows

    ''

    instead of

    no character



    Java Code:
    public class DataWriter {
    
    	public static int saveResults(Results results, DbConfig dbConfig){
    		/* Salvo i dati di collaudo in:
    		 * tabella tescol (header collaudi)
    		 * tabella rigcol (valori misurati) */
    		int result=-1;
    		DbConnector dbConnector = new DbConnector(dbConfig);
    
    		String testCode = "";
    		if(results != null){
    			testCode = results.getCode();
    		}
    		/* Verifico che il codice non sia gią inserito e che sia diverso da null */
    		if(!testCode.equals("")){
    			/* tabella tescol (header collaudi)*/
    			String sqlHeader = "INSERT INTO tescol (" +
    			"codice, " +
    			"codop, " +
    			"prog, " +
    			"ko, " +
    			"data, " +
    			"ora, " +
    			"idLinea, " +
    			"erroper, " +
    			"codoper, " +
    			"datiok, " +
    			"coddef, " +
    			"opnote)  " +
    
    			" VALUES ('" +
    
    			results.getCode() + "', '" +
    			results.getOdpCode() + "', '" +
    			results.getSerialNumber() + "', '" +
    			results.getKo() + "', '" +
    			results.getDate() + "', '" +
    			results.getTime() + "', '" +
    			results.getLineId() + "', '" +
    			results.getOperatorError() + "', '" +
    			results.getOperatorCode() + "', '" +
    			results.getDataOk() + "', '" +
    			results.getDefCode() + "', '" +
    			results.getOperationNote() + "'" +
    			");";
    			System.out.println(sqlHeader);
    			dbConnector.update(sqlHeader);
    
    			/* tabella rigcol (valori misurati) */
    			for(DataResults data : results.getDataResults()){
    				if(!data.getNameOfTest().equals("")){
    					try {
    pstmp ->						result = dbConnector.insert("INSERT INTO rigcol (" +
    								"codtes, " +
    								"sisprova, " +
    								"descprova, " +
    								"descv1, " +
    								"umv1, " +
    								"v1, " +
    								"descv2, " +
    								"umv2, " +
    								"v2, " +
    								"descv3, " +
    								"umv3, " +
    								"v3, " +
    								"descv4, " +
    								"umv4, " +
    								"v4, " +
    								"descv5, " +
    								"umv5, " +
    								"v5, " +
    								"codprova)  " +
    								
    								" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
    								new Object[]{
    								results.getCode(),
    								data.getNameOfTest(),
    								data.getAuxNameOfTest(),
    problem -> 								((data.getDescValue().size() >=1) ? data.getDescValue().get(0) : ""),
    								((data.getUnitValue().size() >=1) ? data.getUnitValue().get(0) : ""),
    								((data.getValue().size() >=1) ? data.getValue().get(0) : ""),
    								((data.getDescValue().size() >=2) ? data.getDescValue().get(1) : ""),
    								((data.getUnitValue().size() >=2) ? data.getUnitValue().get(1) : ""),
    								((data.getValue().size() >=2) ? data.getValue().get(1) : ""),
    								((data.getDescValue().size() >=3) ? data.getDescValue().get(2) : ""),
    								((data.getUnitValue().size() >=3) ? data.getUnitValue().get(2) : ""),
    								((data.getValue().size() >=3) ? data.getValue().get(2) : ""),
    								((data.getDescValue().size() >=4) ? data.getDescValue().get(3) : ""),
    								((data.getUnitValue().size() >=4) ? data.getUnitValue().get(3) : ""),
    								((data.getValue().size() >=4) ? data.getValue().get(3) : ""),
    								((data.getDescValue().size() >=5) ? data.getDescValue().get(4) : ""),
    								((data.getUnitValue().size() >=5) ? data.getUnitValue().get(4) : ""),
    								((data.getValue().size() >=5) ? data.getValue().get(4) : ""),
    								data.getTestCode()});
    					} catch (SQLException e) {
    						// TODO Auto-generated catch block
    						result = -1;
    						e.printStackTrace();
    					}
    					if (result < 0){
    						break;
    					}
    				}
    			}
    		}
    		return  result;
    	}

  6. #6
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,792
    Rep Power
    19

    Default

    Quote Originally Posted by robyf1 View Post
    the second shows

    ''

    instead of

    no character
    How are you determining that?
    This is what I don't understand.
    What are you using to see the data?
    Is what you're using actually printing out those single quotes?

  7. #7
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    How are you determining that?
    This is what I don't understand.
    What are you using to see the data?
    Is what you're using actually printing out those single quotes?
    sorry, use pgAdmin III

  8. #8
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,792
    Rep Power
    19

    Default

    You didn't answer all the questions.
    Is what you're using printing out the single quotes?
    If so then that implies there's an empty string in there...which is what you say you want.

  9. #9
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    I try to insert an image.
    the first, second, third, fourth row was inserted with postgre console
    the fifth row was inserted with java
    Sql insert into blank character-immagine.jpg
    Last edited by robyf1; 06-03-2011 at 01:55 PM.

  10. #10
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,792
    Rep Power
    19

    Default

    OK.
    So have you confirmed that setting the String to "" is doing that?
    Have you set up a test with a test table and done a simple insert into it using the normal JDBC (not whatever DbConnector is).
    Then that will show you whether it is the driver or whether something else is happenig.
    Java Code:
    PreparedStatement ps = connection.prepareStatement("INSERT INTO test (?)");  // test has a single VARCHAR column
    ps.setString(1,"");
    ps.executeUpdate();

  11. #11
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    I solved it:

    for(Object el : Arrays.asList(values)){
    if (el instanceof String){
    String elString = (String)el;
    if (elString.equals("")){
    el = null;
    }
    }
    pstmt.setObject(idx++, el);
    }

  12. #12
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,792
    Rep Power
    19

    Default

    Why do all that if you want it to be null?
    I asked you what the difference was between '' and blank.
    If you want it to be null then just
    Java Code:
    ((data.getUnitValue().size() >=1) ? data.getUnitValue().get(0) :[B] null)  // Set to null rather than "".[/B]

  13. #13
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    sorry, but, as said, I do not understand English well

  14. #14
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    Why do all that if you want it to be null?
    I asked you what the difference was between '' and blank.
    If you want it to be null then just
    Java Code:
    ((data.getUnitValue().size() >=1) ? data.getUnitValue().get(0) :[B] null)  // Set to null rather than "".[/B]
    as you wrote it is better

  15. #15
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,792
    Rep Power
    19

    Default

    NULL is a technical programming term, so should translate.

  16. #16
    robyf1 is offline Member
    Join Date
    Jun 2011
    Location
    Milano
    Posts
    12
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    NULL is a technical programming term, so should translate.
    sorry again

  17. #17
    danieland is offline Member
    Join Date
    Jun 2011
    Location
    Romania, Cluj Napoca
    Posts
    2
    Rep Power
    0

    Default

    I don't know if that's correct, but in postgresql you can put information in columns that you want.
    For example if I have the table test with columns: - test_id,
    - name,
    - pass
    and I want to put information only in columns name and pass I can do like that: insert into test (name,pass) values('test','pass');
    The 'test_id' column is filled with an empty string

Similar Threads

  1. How to insert blank lines
    By durdanto in forum New To Java
    Replies: 10
    Last Post: 02-17-2011, 06:10 AM
  2. Blank screen
    By dewitrydan in forum New To Java
    Replies: 14
    Last Post: 08-12-2010, 05:19 PM
  3. Read from a certain character to a certain character
    By blackstormattack in forum New To Java
    Replies: 0
    Last Post: 03-16-2009, 11:36 AM
  4. Replies: 3
    Last Post: 02-28-2009, 09:17 AM
  5. reading text character by character
    By bugger in forum New To Java
    Replies: 2
    Last Post: 11-09-2007, 08:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •