SQL INSERT ERROR FROM WD

[Former Member]

Hi experts!!

I have created several tables from the NWDS and un-marked the "Not-null" checkbox.

In my WD application i am using the following SQL statement to insert values to one table BUT when i try to insert an empty field (NOT a primary key) i am getting an SQL Exception.

Any ideas on how i can actually insert empty fields???

con.createStatement().executeUpdate(

"insert into TMP_HEADER_PRT_DB(PRWT,AFM,KINISI, DATUM)"

+ "values(" + prt2 + ",'" + user + "','u0395u039Au0394. u0391u0394u0395u0399u0391u03A3 u0395u039Cu03A0u039Fu03A1u0399u039Au039Fu03A5 u0391u039Du03A4u0399u03A0u03A1. u0395u03A4u0391u0399u03A1u0395u0399u0395u03A3','" + datum +"')") ;

con.createStatement().executeUpdate(

"insert into TMP_EMP_ANT_ET(PRWT,EPWNYMIA,EKPROSWPOS,DIEFTHINSI,THL,FAX,EMAIL, DATUM)"

+ "values(" + prt2 + ",'" + epwnymia +

"','" + ekproswpos +"','" + address+"','" tel "','" + fax +"','" + email + "','" + datum +"')") ;

[Former Member]

The wdComponentAPI.getMessageManager().reportSuccess( insertst); give me this u03A3u03A4u0395u03A6u0391u039Du039Fu03A3 u039Bu0391u0396u0391u03A1u0399u0394u0397u03A3 which is the value of the sting epwnymia (the first element)

And the error is

The SQL statement "u03A3u03A4u0395u03A6u0391u039Du039Fu03A3 u039Bu0391u0396u0391u03A1u0399u0394u0397u03A3" contains the syntax error[s]: - 1:1 - SQL syntax error: an illegal character "u03A3" has been found in the statement text

What is SQL *???

[Former Member]

Hi Ayyapparaj!!

this in what i wrote

String insertst = "insert into TMP_EMP_ANT_ET ( PRWT, EPWNYMIA, EKPROSWPOS, DIEFTHINSI, THL, FAX, EMAIL, DATUM)" +

" values (" + prt2 + ",'"

+ epwnymia != null ? epwnymia : null + "','"

+ ekproswpos != null ? ekproswpos : null + "','" + address != null ? address : null + "','"

+ tel != null ? tel : null + "','"

+ fax != null ? fax : null + "','"

+ email != null ? email : null + "','"

+ datum != null ? datum : null +"')";

prt2 is only for internal use and will never be empty..

I still get errors..

Thank you so much!!!!!

[Former Member]

Hi,

Print this string and try executing this in SQL * plus you will be able to see the actual issue.

Regards

Ayyapparaj

[Former Member]

Hi Ayyapparaj!!

Thank you for your reply

I am looking at my insert statement but i cannot find where the double quotes come from.

This is the code

String insertst = "insert into TMP_EMP_ANT_ET(PRWT,EPWNYMIA,EKPROSWPOS,DIEFTHINSI,THL,FAX,EMAIL, DATUM)"

+ "values(" + prt2 + ",'" + epwnymia + "','" + ekproswpos + "','" + address+ "','" tel "','" + fax +"','" + email + "','" + datum +"')" ;

In the previous example the field address was empty.

[Former Member]

Please try this code in your application. Don;t let the qry execute but print it and see how the values come in query

I removed few + signs from the query. Try this.and let us know the result

String insertst = "insert into TMP_EMP_ANT_ET ( PRWT, EPWNYMIA, EKPROSWPOS, DIEFTHINSI, THL, FAX, EMAIL, DATUM) values (" + prt2 + ",'" + epwnymia + "','" + ekproswpos + "','" + address + "','" + tel + "','" + fax + "','" + email + "','" + datum +"')";
wdComponentAPI.getMessageManager().reportSuccess( insertst);

Regards

Vinod

[Former Member]

Hi,

use as follows

String insertst = "insert into TMP_EMP_ANT_ET(PRWT,EPWNYMIA,EKPROSWPOS,DIEFTHINSI,THL,FAX,EMAIL, DATUM)"

+ "values(" + ( prt2 != null ? prt2 : null

+ ",'" + epwnymia + "','" + ekproswpos + "','" + address+ "','" tel "','" + fax +"','" + email + "','" + datum +"')" ;

use as follows while creating the statement

prt2 != null ? prt2 : null

Ex"

String insertst = "insert into TMP_EMP_ANT_ET(PRWT) values ('" + prt2 != null ? prt2 : null + "')"

Regards

Ayyapparaj

[Former Member]

After trying the "prepared statement" i still get the same error.

The values i ma inserting are the context values, so i do not know at design time which values are gonna be empty..

This is the error

The SQL statement "INSERT INTO "TMP_EMP_ANT_ET" ("PRWT","EPWNYMIA","EKPROSWPOS","DIEFTHINSI","THL","FAX","EMAIL","DATUM") VALUES (16,'u03A3u03A4u0395u03A6u0391u039Du039Fu03A3 u039Bu0391u0396u0391u03A1u0399u0394u0397u03A3','null','','678','6667','mailsasad','9/7/2008')" contains the semantics error[s]: - 1:123 - type check error: a string literal must not be the empty string literal ('')

[Former Member]

Hi,

If you look closely to the error

The SQL statement "INSERT INTO "TMP_EMP_ANT_ET" ("PRWT","EPWNYMIA","EKPROSWPOS","DIEFTHINSI","THL","FAX","EMAIL","DATUM") VALUES (16,'u03A3u03A4u0395u03A6u0391u039Du039Fu03A3 u039Bu0391u0396u0391u03A1u0399u0394u0397u03A3','null','','678','6667','mailsasad','9/7/2008')"

Following line you will se '' after the null

(16,'u03A3u03A4u0395u03A6u0391u039Du039Fu03A3 u039Bu0391u0396u0391u03A1u0399u0394u0397u03A3','null','','678','6667','mailsasad','9/7/2008')"

make sure that if the attribute is null populate null

change the above to

(16,'u03A3u03A4u0395u03A6u0391u039Du039Fu03A3 u039Bu0391u0396u0391u03A1u0399u0394u0397u03A3','null','null','678','6667','mailsasad','9/7/2008')"

Regards

Ayyapparaj

[Former Member]

Iria,

Can your please try by specifying a null in the forth value in query?

I think there it catch the exception.

Regards

Vinod

[Former Member]

Iria,

the query must be like this for empty values

"insert into table ( id, name, location ) values ( 1, 'vinod', '')"

remember: the database field attribute not null must be set accordingly.

Use of PreparedStatement will avoid all the problems with null values.

see the link.

http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html

Thanks

Vinod

[Former Member]

Hi,

Can you post the exception?

To insert empty fields

INSERT INTO <TABLE NAME>( <COLUMNANME>) VALUES(NULL);

Regards

Ayyapparaj