-
If Not Exists?
Hello all.
I am trying to use if not exists on a db update but am getting 'Invalid parameter binding(s).' errors.
Any ideas?
IF NOT EXISTS
(select mobile_number
FROM tblCustomer
WHERE mobile_number = 'nParam'
AND op_id = 'opParam'
)
BEGIN
INSERT INTO tblCustomer (
mobile_number, op_id
) VALUES (
'nParam',
'opParam'
)
END
Many thanks
-
Your SQL looks alright. Where are you running this query from?
-
I am running it as part of a prep stmt
// sql
String custsql;
custsql = "IF NOT EXISTS (select mobile_number FROM tblCustomer WHERE mobile_number = 'nParam' AND op_id = 'opParam' ) BEGIN INSERT INTO tblCustomer (mobile_number, op_id) VALUES (nParam,opParam) END";
String transsql;
transsql = "INSERT INTO tblTransaction (mobile_number, service_id, text_trigger, sms_sent, sms_received) VALUES (?,?,?,?,?)";
custps = con.prepareStatement(custsql);
custstmt = con.createStatement();
transps = con.prepareStatement(transsql);
transstmt = con.createStatement();
// inserting records
if(proceed) {
custps.setString(1, nParam);
custps.setString(2, opParam);
custps.executeUpdate();
transps.setString(1, nParam);
transps.setString(2, osidParam);
transps.setString(3, tParam);
transps.setString(4, sentParam);
transps.setString(5, receivedParam);
transps.executeUpdate();
Many thanks
-
Hi,
I'm not exactly sure what language your using but I'm guessing you need to replace "(nParam,opParam)"
with "(?,?)" for it to work like so: -
String custsql;
custsql = "IF NOT EXISTS (select mobile_number FROM tblCustomer WHERE mobile_number = 'nParam' AND op_id = 'opParam' ) BEGIN INSERT INTO tblCustomer (mobile_number, op_id) VALUES (?,?) END";
-
Yes I have tried that also.
No joy there.
I'm using a prepared statement in a Java servlet.
I think it is something to do with the insertion of nParam and opParam vraiables. I'm not sure the sytnax is correct.
Cheers
Chris
-
Come to think of it I think you may need to enter them in twice - try this: -
String custsql;
custsql = "IF NOT EXISTS (select mobile_number FROM tblCustomer WHERE mobile_number = '?' AND op_id = '?' ) BEGIN INSERT INTO tblCustomer (mobile_number, op_id) VALUES (?,?) END";
String transsql;
transsql = "INSERT INTO tblTransaction (mobile_number, service_id, text_trigger, sms_sent, sms_received) VALUES (?,?,?,?,?)";
custps = con.prepareStatement(custsql);
custstmt = con.createStatement();
transps = con.prepareStatement(transsql);
transstmt = con.createStatement();
// inserting records
if(proceed) {
custps.setString(1, nParam);
custps.setString(2, opParam);
custps.setString(3, nParam);
custps.setString(4, opParam);
custps.executeUpdate();
transps.setString(1, nParam);
transps.setString(2, osidParam);
transps.setString(3, tParam);
transps.setString(4, sentParam);
transps.setString(5, receivedParam);
transps.executeUpdate();
Good luck
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|