5 replies to this topic

[mith]

Hello again !
i have another problem with JDBC, how can i make a SQL statement with one of the value = null? i mean i want give one of foreign key a null value and after i run the statement, application just hang i mean i cant click on GUI buttons, when i try to close frame nothing happens
any tips?

[gregwarner]

Impossible to tell what's going on in your application just by what you described. Would have to see some code to know for sure.
Are you trying to set a value of null to a foreign key? Or is this in a 'where' clause? Did you declare the column 'not null' when you created the table? Are you sure you don't have any syntax errors in your SQL query?

[mith]

i create table like:

Create table copy (
      id_copy Integer NOT NULL ,
      id_purchase Integer NOT NULL ,
      id_publisher Integer ,
      id_book Integer,
      id_map Varchar2 (20),
      id_comix Varchar2 (20),
      id_painting Varchar2 (20),
      id_autor Integer NOT NULL ,
      price Varchar2 (20),
      condition Varchar2 (20),
  PRIMARY KEY (id_copy ),
  FOREIGN KEY (id_purchase ) REFERENCES purchase(id_purchase ),
  FOREIGN KEY (id_publisher ) REFERENCES publisher(id_publisher ),
  FOREIGN KEY (id_book ) REFERENCES book(id_book ),
  FOREIGN KEY (id_map ) REFERENCES map(id_map ),
  FOREIGN KEY (id_comix ) REFERENCES comix(id_comix ),
  FOREIGN KEY (id_painting ) REFERENCES painting(id_painting ),
  FOREIGN KEY (id_autor ) REFERENCES autor(id_autor )
) ;

General idea is that i can make copy of only 1 object (from book,comix,map, painting). When i enter only id_book value everything is ok. But when i try to enter only id_map or id_comix or id_painting ive got exceptions, bellow i ll paste the way i use statements:

String insertString1 = "insert into egzemplarz values("+i+","+purchase+", "+s3+", "+s4+", '"+s5+"', '"+s6+"', '"+s7+"', "+s8+", "+s11+", '"+s10+"')";                    stmt = aa.conn.createStatement();
                    stmt.executeUpdate(insertString1);

where i,s1,...,s10 are strings which i obtain by get.Text() method from textfield.

i CRY for some help ://

ps. sorry for my english

edit 1: pools id_book, id_map, id_comix and id_painting are NULLABLE

[wim DC]

That's because to insert null, SQL expects " NULL " and you give the String " 'null' " which is probably no valid foreign key.

Fix it by either using a lot of if-statements to insert " NULL " (without single quotes around it), or else use preparedStatements (<- pls take this solution)

Prepared statement: Using Prepared Statements (The Java™ Tutorials > JDBC™ Database Access > JDBC Basics)

[mith]

wim DC your help is priceless right now i used null without ' and it works great, after i will back from university i will check preparedStatement, once again thank you very much, you are the master !

[gregwarner]

WimDC's suggestion is a good one. It would have been the one I'd have made too. Prepared statements offer many advantages over regular statements like yours above, one being that you don't have to worry about the quotes when you're inserting NULL values. The data types themselves take care of that. The other advantage being that prepared statements are immune to SQL injection, which should be your biggest concern actually.