Hi, is it possible with ElevateDB to insert or update as it is possible with Advantage like this:

MERGE INTO mytable
using (select * from testtable where testfield='ABC') u1 on u1.testfield=mytable.testfield

WHEN NOT MATCHED THEN
insert (testfield, otherfield) value s('ABC', 'DDDD')

WHEN MATCHED THEN
update set otherfield='DDDD'
Yusuf Zorlu
MicrotronX

Yusuf


No. What you've posted, to one not familiar with Advantage, seems to be a mixture of INSERT and UPDATE. The nearest you could get would be a script which would run an INSERT then an UPDATE.

Roy Lambert

Roy Lambert wrote:

No. What you've posted, to one not familiar with Advantage, seems to be a mixture of INSERT and UPDATE. The nearest you could get would be a script which would run an INSERT then an UPDATE.


Hi Roy,

this script checks if there is already a record with "ABC" in the table, if a row is found, it updates it and if there is no row, it insert's a new record. We can do this in delphi with two scripts but i wanted to know if there is already similar functionality available in elevatedb

Yusuf Zorlu
MicrotronX

Yusuf

I think you should spend a bit of time looking at Stored Procedures in EDB. These allow you to perform a lot of complex functions that would otherwise have to be coded in Delphi.

Stored Procedures have the concept of a CURSOR which can be opened using a STATEMENT.

 DECLARE Crsr CURSOR FOR Stmt;
PREPARE Statement FROM
' SELECT * FROM MyTable WHERE Field = ''ABC'' ';
OPEN Crsr;
IF Crsr.ROWCOUNT . 0 THEN
 --DoSomething
 EXECUTE IMMEDIATE
 ' INSERT INTO MyTable ... ';
 ELSE
 --DoSomethingElse;
 EXECUTE IMMEDIATE
 ' UPDATE MyTable ... ';
 END IF;

You would need to iterate through a dataset to make this work if you had multiple records (which would need a second CURSOR).

Adam

Yusuf


Pretty much anything you can do in DBIASM you can do in ElevateDB, sometimes there's different syntax. Sometimes you can do a lot more.

DBISAM scripts are simple queries executed one after the other according to the delimiter. You can achieve the same in ElevateDB by using EXECUTE IMMEDIATE inside a script, or you can run two queries.

Better you can write a script, or stored procedure using SQL/PSM (ElevateDB's programming language) to do essentially what you would do using table navigation methods in Delphi but as an SQL function.

If you post the DBISAM code you have I'll try and turn it into ElevateDB for you.


Roy Lambert

Yusuf ,

We don't but MERGE has been in the "wish list" for some time.
Let's hope that Tim can make it soon

--
Fernando Dias
[Team Elevate]