Making "returning" give back the SERIAL

[Bastiaan Olij]

Hey Das,

You need to skip the column for the default to work, so

-----
Insert into customers (
  Cust_FirstName, Cust_LastName, .....
) values (
  @[$cinst.Cust_FirstName], @[$cinst.Cust_LastName], ....
) returning "Cust_ID";
-----

Assuming you have put the next_val function as the default value for the
column.

Then in Omnis I have a table class called tBaseSQL, I use this as the
$superclass for all my table classes.

I have a function called tBaseSQL.$primarykey which looks something like
this:
-----
set reference lvSchema as $schemas.$findname($cinst.$sqlclassname)
if lvSchema
  set reference lvColumn as lvSchema.$objs.$first()
  while lvColumn
    if lvColumn.$primarykey
      ;; found our primary key
      quit method lvColumn.$name
    end if

    set reference lvColumn as lvSchema.$objs.$next(lvColumn)
  end while
end if

;; I guess we didn't find it
quit method ''
-----

Now I create my own tBaseSQL.$insert like so:
-----
calculate lvPrimaryKey as $cinst.$primarykey
if lvPrimaryKey = ''
  ;; Call $sqlerror just like Omnis does when there is an error
  do $cinst.$sqlerror(kTableInsertError, -1, 'No primary key set')
  return kFalse
end if

;; Tell omnis not to include the primary key in our insert statement
calculate $cinst.$cols.[lvPrimaryKey].$excludefrominsert as kTrue

;; Now let Omnis build the SQL query we want
calculate lvSQL as con('Insert into ',$cinst.$servertablename,'
',$cinst.$insertnames('$cinst'),' returning ',lvPrimaryKey)

;; And execute
if not($cinst.$statementobject.$execdirect(lvSQL))
  do $cinst.$sqlerror(kTableInsertError,
$cinst.$statementobject.$errorcode, $cinst.$statementobject.$errortext)
  return kFalse
end if

;; And fetch our new primary key (lvList is an undefined list!!)
Do $cinst.$statementobject.$fetch(lvList, kFetchAll)
if lvList.$linecount=0
  do $cinst.$sqlerror(kTableInsertError, -1, 'Couldn''t retrieve primary
key value')
  return kFalse
end if
calculate $cinst.[lvPrimaryKey] as lvList.1.C1

;; We did it!
return kTrue
-----

I do far more in both $insert and $update but this is a good starting point.
Also adding an $sqlerror method to tBaseSQL gives you a nice central
place to do error handling as all build in table class functions call
this method when an error is given. I follow the same design pattern up
above.

Forgive any typos I made :)

Cheer,

Bas


On 29/1/18 9:23 am, Das Goravani wrote:
> Omnis doesn’t like this statement
>
> INSERT INTO customers VALUES (DEFAULT,@[$cinst.Cust_FirstName],@[$cinst.Cust_LastName],@[$cinst.Cust_eMail],@[$cinst.Cust_Phone],@[$cinst.Cust_Address1],@[$cinst.Cust_Address2],@[$cinst.Cust_City],@[$cinst.Cust_State],@[$cinst.Cust_Zip],@[$cinst.Cust_BalOwed],@[$cinst.Cust_BizName])  returning “Cust_ID"
>
> Can you see anything wrong with it
>
> Note it contains a call to DEFAULT the first field, and a RETURNING clause at the end for the name of that field
>
> I have double and rechecked that I have a serial generator running and have set the default for that column to the serial generator
>
> The above should work but it’s not and I’m frustrated
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com


-- 
Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij