PowerBuilder: Check record and insert into SQL table if not available

Question

I am fairly new to PowerBuilder Classic 12. I need to check whether a record is available and if not insert from a textbox. I would probably need a DataStore since someone suggested a preference to SQL statements. Thanks. this code is behaving funny, please where is the problem? at one time it works but running the program again it accepts a data that has already been inserted. the program is not giving any error but i can see the same data stored in the table.

string id, idno
 idno=trim(sle_idno.text)
 if idno="" then
    messagebox("EMPTY","Enter a record")
        return
end if
   SELECT employee.idnumber
    INTO :id  
    FROM employee  ; 
     if idno=id then
        messagebox("AVAILABLE","Record available")
        return
    end if
  INSERT INTO employee 
         ( idnumber )  
  VALUES ( :idno )  ;

Any specific reason for not usinf the `Merge` ? – Jul 16 '12 at 07:14 — , Jul 16 '12 at 07:14

score 2 · Answer 1 · edited Jul 16 '12 at 16:29

Fixing existing code

You're missing a WHERE clause; this is trying to stuff all idnumber values into id. That's why your existing code is failing.

Have a look at the help file that comes with PB. (The PDF manuals that come with it are good too, but the help file is right there on the menu of the IDE.) There are plenty of examples in there on how you should code embedded SQL statements. You should be checking the error attributes of the transaction object (in this case it is implicitly SQLCA) to determine if the transaction executed successfully or not, returned any rows, etc....

A new approach

There are several ways to approach this with a DataStore. The easiest is to make a DataWindow object against the employee table, that takes an argument and uses it in the SQL statement in the WHERE clause. In your script, set up a DataStore using this object and Retrieve() it using the idnumber as the argument, check the return value of Retrieve() (the number of rows returned if positive or zero, an error if negative) and if it's zero, InsertRow() on your DataStore, do your SetItem()'s on it (you'll want to load more than just idnumber, right?) and then do an Update() on it.

score 1 · Accepted Answer · 2012-07-15T10:54:01.930

You are looking for Merge.

I will explain it using an example. Just send the values in Database via Stored Proc? and use following technique.

Sample DDL

CREATE TABLE Employee
(
   EmployeeID INTEGER PRIMARY KEY,
   EmployeeName VARCHAR(15)
)

CREATE TABLE EmployeeSalary
(
    EmployeeID INTEGER ,
    EmployeeSalary INTEGER
)

Sample DML For Employee

INSERT INTO Employee
VALUES(1,'SMITH')
INSERT INTO Employee
VALUES(2,'ALLEN')
INSERT INTO Employee
VALUES(3,'JONES')
INSERT INTO Employee
VALUES(4,'MARTIN')
INSERT INTO Employee
VALUES(5,'JAMES')
Sample DML For EmployeeDetails

INSERT INTO EmployeeSalary
VALUES(1,23000)
INSERT INTO EmployeeSalary
VALUES(2,25500)
INSERT INTO EmployeeSalary
VALUES(3,20000)

Merge Query

MERGE EmployeeSalary AS stm
USING (SELECT EmployeeID,EmployeeName FROM Employee) AS sd
ON stm.EmployeeID = sd.EmployeeID
WHEN MATCHED THEN UPDATE SET stm.EmployeeSalary = stm.EmployeeSalary + 12
WHEN NOT MATCHED THEN
INSERT(EmployeeID,EmployeeSalary)
VALUES(sd.EmployeeID,25000);

References

First Reference

Second Reference

Third Reference

Fourth Reference

This is technically correct, but why not use the data & transaction management that comes with the PB environment using the datastore? — DaveE, Jul 17 '12 at 00:45

PowerBuilder: Check record and insert into SQL table if not available

2 Answers2

Sample DDL

Sample DML For Employee

Merge Query

References