Access / inserting binary data with PreparedStatement

Thread view:

znaps1@gmail.com - 27 Jan 2005 20:55 GMT

I've got an awkward situation with inserting binary encoded passwords
into an Access DB.

The previous code was calling a regular insert statement, and the value
for the password column, which is a binary datatype in the Access DB,
was a passed as a String, i.e.. "INSERT INTO USERS (username, password)
VALUES (Somebody, 0xbd0ff259ae634194d41c9673a29c35a9)"

This works fine. Now I'm improving performance by doing inserts with
PreparedStatements. When I call ps.setObject(2,
"0xbd0ff259ae634194d41c9673a29c35a9"), the data is stored directly as a
String. I've tried other methods such as setBinaryStream and setBytes,
but the data is not stored in the same manner.

What is the corresponding PreparedStatemnt code to insert the String as
a binary object?

Reply to this Message

Joe Weinstein - 27 Jan 2005 22:40 GMT

> I've got an awkward situation with inserting binary encoded passwords
> into an Access DB.
[quoted text clipped - 12 lines]
> What is the corresponding PreparedStatemnt code to insert the String as
> a binary object?

Hi. You will have to actually convert your string to a binary object.
"0xbd0ff259ae634194d41c9673a29c35a9" is the MS ascii string
representation of a byte array whose bytes have the hexadecimal
values BD, 0F, F2, ... etc. Your program would have to make a
16-byte byte array and set the bytes to their values. Then setBytes()
would work for the prepared statement parameter.

Otherwise, you could try:

p = c.prepareStatement("INSERT INTO USERS VALUES (?, convert(varbinary, ?)");
p.setString(1, "Somebody");
p.setString(2, "0xbd0ff259ae634194d41c9673a29c35a9" );

I hope this helps. The code above is MS SQLServer. I don't know if the Access
column type is supposed to be varbinary or if Access has the 'convert()' function.

Actually, I suspect that an Access DBMS may be too simpleminded to provide
any real performance win with prepared statements. Does access have stored
procedures? Unless the DBMS is functional enough to store and re-use
query plans in a way that makes them faster, prepared statements can't
go any faster than plain statements.
Good Luck,
Joe Weinstein at BEA

Reply to this Message

znaps1 - 28 Jan 2005 04:43 GMT

Thanks Joe.

I had my suspicions about Prepared Statements being any better also,
but it seems to be working a lot faster using them as opposed to
regular statements. I was using the ODBC Bridge but had to switch to
the JadoZoom driver to get the prepared statement calls to work.

Reply to this Message

Access / inserting binary data with PreparedStatement

Free Magazines