Java Prepared Statement with multiple arguments

Thread view:

johnfofawn@hotmail.com - 03 Aug 2005 15:47 GMT

Hi,

I need to insert data into a table only if that data does not already
exist.

So I want to do something like

IF NOT EXIST (SELECT * FROM MyTable WHERE MyColumn = '?') INSERT INTO
MyTable (MyColumn) VALUES ('?')

In this example there is only one column, but in my real table I have
about 10 columns.

So I can create a prepared statement to do this, but since I use two
?'s for each column (one for the select and another for the insert) I
have to set each column value twice.

Am I using this right? Is there a better way to do this?

THANKS!
John

Reply to this Message

joeNOSPAM@BEA.com - 03 Aug 2005 17:42 GMT

Hi. Yes you have to have a '?' for every variable in both clauses. If
you make a stored procedure like:

create procedure myInsertProc int @foo, VARCHAR(30) @bar ...
AS
BEGIN
IF NOT EXIST (SELECT * FROM MyTable WHERE MyColumn = @foo
AND MYOtherColumn = @bar ) INSERT INTO
MyTable (MyColumn) VALUES (@foo, @bar)
END

then your JDBC would only need one '?' for each column:

p = c.prepareStatement("{ call myInsertProc( ?, ? ) }");

If you can create an index for your table that enforces that
each row is unique, then you don't need to do the check. If
the row already exists the insert will fail.

HTH,
Joe Weinstein at BEA

Reply to this Message

johnfofawn@hotmail.com - 03 Aug 2005 18:29 GMT

Joe,

That's perfect. Thank you!!!!

John

Reply to this Message

Jim Harrington - 05 Aug 2005 18:48 GMT

If you want to avoid the stored procedure, you could do the following
as well:

INSERT INTO MyTable (MyColumn)
SELECT '?' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM MyTable
WHERE MyColumn = '?');

> Joe,
>
> That's perfect. Thank you!!!!
>
> John

Reply to this Message

Java Prepared Statement with multiple arguments

Free Magazines