Can not save boolean in sapdb using jdbc

[Former Member]

Hi,

I try to save a boolean in SAPDB by using JDBC but I get a sql error. The error is that I can not save a boolean in to a varchar field. The field type in the table in boolean.

Under you find the source I use.

Richard.

public void createAddress(

int contactId,

int addressId,

boolean show,

String location,

String street,

String postalCode,

String city,

String country,

String user)

throws SQLException {

// TODO Auto-generated method stub

Date date = new Date();

String dateFormat = "yyyy-MM-dd";

String timeFormat = "HH:mm:ss";

SimpleDateFormat ddf = new SimpleDateFormat(dateFormat);

SimpleDateFormat tdf = new SimpleDateFormat(timeFormat);

String creationDate = ddf.format(date);

String creationTime = tdf.format(date);

PreparedStatement newContact =

conn.prepareStatement(

"INSERT INTO BE_ADDRESS (CONTACT_ID, ADDRESS_ID, SHOW, STREET, CITY, ZIPCODE, COUNTRY, DATE_CREATE, TIME_CREATE, USER_CREATE) VALUES (?,?,?,?,?,?,?,?,?,?)");

try {

newContact.setInt(1, contactId);

newContact.setInt(2, addressId);

newContact.setBoolean(3, show);

newContact.setString(11, location);

newContact.setString(4, street);

newContact.setString(5, city);

newContact.setString(6, postalCode);

newContact.setString(7, country);

newContact.setDate(8, java.sql.Date.valueOf(creationDate));

newContact.setTime(9, java.sql.Time.valueOf(creationTime));

newContact.setString(10, user);

newContact.executeUpdate();

} finally {

newContact.close();

}

}

[Former Member]

Hi Kishor,

Here are the answer to questions:

I have directly created the table in the database with SQLstudio.

The source I use in the webdynpro to connect to the database:

DAO dao = null;

// create function

try {

//

dao =

(DAO) Class

.forName("com.bearingpoint.becontact.connextion.dao.jdbcDAO")

.newInstance();

dao.createAddress(

contactId,

wdContext

.currentContactPersonAddressElement()

.getAddressID(),

wdContext

.currentContactPersonAddressElement()

.getVisable(),

wdContext

.currentContactPersonAddressElement()

.getLocation(),

wdContext

.currentContactPersonAddressElement()

.getStreet(),

wdContext

.currentContactPersonAddressElement()

.getPostalCode(),

wdContext

.currentContactPersonAddressElement()

.getCity(),

wdContext

.currentContactPersonAddressElement()

.getCountry(),

wdContext.currentContextElement().getUser());

}

} catch (Exception e) {

throw new WDRuntimeException(e);

}

The Java source I use for JDBC connection:

public jdbcDAO() throws SQLException {

try {

InitialContext ctx = new InitialContext();

DataSource dataSource = (DataSource) ctx.lookup("jdbc/SAP/BC_JMS");

conn = dataSource.getConnection();

if (conn == null) {

throw new SQLException("conn = null");

}

conn.setAutoCommit(false);

} catch (NamingException ex) {

throw new SQLException("NamingException: " + ex.getMessage());

}

}

Connection conn;

// -

---

// Create Address

// -

---

public void createAddress(

int contactId,

int addressId,

boolean show,

String location,

String street,

String postalCode,

String city,

String country,

String user)

throws SQLException {

// TODO Auto-generated method stub

Date date = new Date();

String dateFormat = "yyyy-MM-dd";

String timeFormat = "HH:mm:ss";

SimpleDateFormat ddf = new SimpleDateFormat(dateFormat);

SimpleDateFormat tdf = new SimpleDateFormat(timeFormat);

String creationDate = ddf.format(date);

String creationTime = tdf.format(date);

PreparedStatement newContact =

conn.prepareStatement(

"INSERT INTO BE_ADDR (CONTACT_ID, ADDRESS_ID, SHOW, STREET, CITY, ZIPCODE, COUNTRY, DATE_CREATE, TIME_CREATE, USER_CREATE) VALUES (?,?,?,?,?,?,?,?,?,?)");

try {

newContact.setInt(1, contactId);

newContact.setInt(2, addressId);

newContact.setBoolean(3, true);

newContact.setString(4, street);

newContact.setString(5, city);

newContact.setString(6, postalCode);

newContact.setString(7, country);

newContact.setDate(8, java.sql.Date.valueOf(creationDate));

newConntact.setTime(9, java.sql.Time.valueOf(creationTime));

newContact.setString(10, user);

newContact.executeUpdate();

} finally {

newContact.close();

}

}

In SQLStudio I can insert rows to the table!

I use SQLstudio on my laptop but the date on my laptop current and the date on the server is older because of the license. Could this be the problem?

Richard

[Former Member]

Hi Kishor,

Thanks for your remarks. T think that I have found the problem. Before I changed the column to boolean it was a String.

For a test I created a table, but when I use the table in a webdynpro application It says that the table doesn't excist.

When I look with webstudio, the table excists.

Regards,

Richard

[kishorg]

Hi Richard Middelburg ,

how u created that tables ?. have u used Java Dictionary Project or directly created that table?.

How u got the connection to this database from WebDynpro ?. which code u used ?.

u looked up the data source and created connection using that data source?.

could u see the table from visual admin ?.

let me know these things ...

Regards

Kishor Gopinathan

[Former Member]

Hi all,

This is the message I get: com.sap.tc.webdynpro.services.exceptions.WDRuntimeException: Cannot assign an object of type boolean to host variable 3 which has JDBC type VARCHAR.

I tried to use Tanveer solution, but I get a java error that I can not use a char type in setBoolean().

Richard

[kishorg]

Hi Richard Middelburg ,

<<<<

PreparedStatement newContact =

conn.prepareStatement(

"INSERT INTO BE_ADDRESS (CONTACT_ID, ADDRESS_ID, SHOW, STREET, CITY, ZIPCODE, COUNTRY, DATE_CREATE, TIME_CREATE, USER_CREATE) VALUES (<b>?,?,?,?,?,?,?,?,?,?</b>)");

try {

newContact.setInt(1, contactId);

newContact.setInt(2, addressId);

newContact.setBoolean(3, show);

<b>newContact.setString(11, location);</b>

newContact.setString(4, street);

newContact.setString(5, city);

newContact.setString(6, postalCode);

newContact.setString(7, country);

newContact.setDate(8, java.sql.Date.valueOf(creationDate));

newContact.setTime(9, java.sql.Time.valueOf(creationTime));

newContact.setString(10, user);

>>>>

in the above code , u have given ...

just check ...

in that create statement , u have given exactly 10 fields to set the value..

so the expected parameter index can range only from 1- 10 ...

but u have set value in the 11 th parameter , which is not given there ...

thats one mistake .. just note this..

<b>the syntax of the setXXX(parameteIndex, value). this is the format of the set methods...</b>

here parameter index means values from 1- 10 only ... u have set value in the 11 th param also which is not a parameter right now...

for setting boolean values....

if the JDBC driver is properly working ... then ...

u can use setBoolean( method....

<b><prepareStatementObject>.setBoolean(<parameterIndex>, true);</b>

For example...

try{

boolean val = true;

String sql = "INSERT INTO <ur table name> (BOOLEAN_FIELDNAME) VALUES(?)";

PreparedStatement pstmt = <connection object>.prepareStatement(sql);

pstmt.setBoolean(1, val);

pstmt.executeUpdate();

}

catch(SQLException e)

{}

in ur code , u have not initialized ur boolean variable..

just check that also...

Regards

Kishor Gopinathan

[Former Member]

Hi,

SAPDB uses 'X' or ' ' for boolean whereas java uses 'true' or 'false'. That could be reason for your problem.

Based on the value of boolean, pass 'X' or ' ' to SAPDB.

data show value type char.

if ( show )

show_value = 'X'.

else

show_value = ' '.

....

....

newContact.setBoolean(3, show_value);

...

That shall solve the problem i think.

Regards,

Tanveer.

[Former Member]

Hi Richard:

Could you put in the message the Exception that throws you, put a catch sentence in your try

Joshua