If Not Exists?

[Hempstock]

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

[skhanal]

Your SQL looks alright. Where are you running this query from?

[Hempstock]

// 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

[rmallamace]

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";

[Hempstock]

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

[rmallamace]

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

[Hempstock]

I'll give it a try!