Making "returning" give back the SERIAL

[Doug Easterbrook]

hi das.

best practice:   you need to be explicit in your field columns in the insert.

AND .. define your database with lower case field name otherwise you’ll hate yourself.
NOTE:   defining the database with lower case field names — still means that they can be camel case in Omnis or upper case.



example:

insert (val1,val2,val3) into fcustomers values (default, @[val2], @[val3] returning val1

is explicit.    sql knows which columns are to receive the values.

 leave out column names and DEFAULT will hurt you a lot.  why?

insert into fcustomers without the names of the columns means that the column order must be explicit and exactly match the same order in which the fields were defined.  and if you manage to get it to work, you are one extremely lucky dog.

and if you leave out DEFAULT a suggested by bas, then you’ve just shifted over the columns and assigned the wrong values to the wrong variables.


NOW…  in the insert statement, you can leave out variables explicitly and reorder them and the defaults for the column will be taken.

eg

insert (val3,val1) into fcustomers values (@[val3, default ] returning val1
or

insert (val2,val3) into fcustomers values ( @[val2], @[val3] returning val1

note that I’ve changed the order of the variables and changed the order of the bind variables — but the bid var order always matches the variables.

anything not supplied takes the default value for that field — and I can even supply the word DEFAULT to tel sql to explicitly use the default.  but if I include that field, I must include a value for it.




second topic.     variable case.


if you have mixed case table of database names in your database (I’m not talking omnis file or schema classes, but actually in the postgres database).   you will hate yourself.    and it will be the source of programmers errors.


pgadmin, if you type

select Var1,Var2,Var3 from fCustomers where CustId = 10

postgres sees it as  the following…  making yor variables case insensitive


select var1,var2,var3 from fcustomers where custid = 10


if you have case sensitive variuable names, you have to always enclose in “"

eg.

select “Var1”,”Var2”,”Var3" from “fCustomers" where “CustId" = 1

and yo really don’y want to do that .  because its so easy to make a case mistake.


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978




see you at the third annual users conference
https://tickets.proctors.org/TheatreManager/95/online?performance=29086 <https://tickets.proctors.org/TheatreManager/95/online?performance=29086>

> On Jan 28, 2018, at 3:23 PM, Das Goravani <das at goravani.com> 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