Subscribe for automatic updates: RSS icon RSS

Login icon Sign in for full access | Help icon Help
Advanced search

Pages: [1]
  Reply  |  Print  
Author Topic: Insert into Database Table with Specified Serial Value  (Read 8720 times)
Sean H.
Posts: 29


« on: July 01, 2022, 11:01:33 am »

Hello everyone,

I was wondering if it is possible to allow inserts into a Serial Column under certain conditions where the value is known (specifically PostgreSQL).

Under most cases I am doing an insert like the below, which works fine:

define
   sql string,
   col2 like sometable.col2,
   col3 like sometable.col3

let sql = "insert into sometable values(default, ?, ?)"
prepare generalInsert from sql

let col2 = "Col 2"
let col3 = "Col 3"
execute generalInsert using col2, col3


The issue is when trying to do inserts into the same table under certain conditions when the Serial Value is known:

define
   sql string,
   col1 like somtable.col1, # serial column
   col2 like sometable.col2,
   col3 like sometable.col3

let sql = "insert into sometable values(?, ?, ?)"
prepare generalInsert from sql

let col1 = 1
let col2 = "Col 2"
let col3 = "Col 3"
execute generalInsert using col1, col2, col3


What's strange is that if the sql's execute is wrapped in a try catch then it does do the insert but is raising an error:

currval of sequence "sq_sometable" is not yet defined in this session
Sebastien F.
Four Js
Posts: 509


« Reply #1 on: July 01, 2022, 11:54:33 am »

Hello,

Please always provide environment details (Genero and DB engine type/version), FGLPROFILE settings and provide sample code (with SQL DDL so we can see the table structure)

Inserting a specific value into a SERIAL/BIGSERIAL and having the DB engine fix the underlying serial generator is an Informix feature.
PostgreSQL does not reset the underlying sequence when doing this.

The following "fix" (workaround) should solve your problem:
https://4js.com/support/issue/?id=FGL-05500#startissue

Ideally, you should use SEQUENCEs to produce the new auto-incremented columns yourself, so you can use it as regular value in the INSERT, on a table defining a regular INTEGER or BIGINT column.

https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_sql_programming_076.html

Seb
Reuben B.
Four Js
Posts: 1046


« Reply #2 on: July 04, 2022, 02:23:22 am »

Sean,

I think also make sure you have seen this page https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagpgs_008.html

All the various database adaption guides https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_section_odi_adaptation_guides.html are similar in format, and so for some thing like serial on postgres you would find the data dictionary entry https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagpgs_data_dictionary.html and then in there the serial entry https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagpgs_008.html

The format of the pages is then to explain how Informix does something, how the alternate database does something, and then provide details of our various solution(s).  The page Seb referred to is that first Note in the Solution section

Reuben


Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Sean H.
Posts: 29


« Reply #3 on: July 05, 2022, 01:17:12 pm »

I think there is something that I am missing but I have made changes and am getting some strange results.

Just including the environment info:

Genero FGL 03.10.23
PostgreSQL 10
FGLPROFILE dbi.default.driver = "dbmpgs"


Database Table Schema

CREATE TABLE testtable (
    idno INTEGER NOT NULL,
    col01 VARCHAR(50) NOT NULL,
    col02 VARCHAR(50) NOT NULL
);
ALTER TABLE testtable ADD CONSTRAINT pk_testtable PRIMARY KEY(idno);
CREATE SEQUENCE sq_testtable;
ALTER TABLE testtable ALTER COLUMN idno SET DEFAULT NEXTVAL('sq_testtable');


Example Code

import fgl database
schema bigfatnothing

main

   define testtable record like testtable.*

   call database.connect()
   call database.begin()

   # insert a row with automatic primary idno
   let testtable.idno = null
   let testtable.col01 = "Col01 Row01"
   let testtable.col02 = "Col02 Row01"
   insert into testtable values testtable.*

   # insert a row with manual primary idno
   let testtable.idno = 10
   let testtable.col01 = "Col01 Row02"
   let testtable.col02 = "Col02 Row02"
   insert into testtable values(testtable.*)

   call database.commit()
   call database.disconnect()

end main


The code above is working, it is inserting one row with an automatic primary idno and then another with a specified primary idno but it doesn't work without doing the automatic primary idno insert first.

For example this doesn't work

import fgl database
schema bigfatnothing

main

   define testtable record like testtable.*

   call database.connect()
   call database.begin()

   # only insert a row with manual primary idno
   let testtable.idno = 10
   let testtable.col01 = "Col01 Row02"
   let testtable.col02 = "Col02 Row02"
   insert into testtable values(testtable.*)

   call database.commit()
   call database.disconnect()

end main


With the error

currval of sequence "sq_testtable" is not yet defined in this session

Sebastien F.
Four Js
Posts: 509


« Reply #4 on: July 05, 2022, 02:55:08 pm »

Sean,

You are creating the table and your sequence "by hand".
In this case, you must always reset the new sequence by hand as well, after inserting a value explicitly.
And since you do all of this by hand, you should disable Informix serial emulation (see documentation)

Automatic PostgreSQL serial sequence reset is only supported, if the table is created with the PostgreSQL SERIAL type.
The ODI driver is able to find the underlying sequence of the SERIAL column.
But if you create the sequence by hand, it's not possible.

To see what happens behind the scene, export FGLSQLDEBUG=3 env var, and run you program again.

Then drop and re-create the table with SERIAL column, and try again with FGLSQLDEBUG set.

Search for "Find serial info":

Code
  1. SQL: INSERT INTO tab1 (pkey,name) VALUES (100,'aaaa')
  2. | 4gl source      : pgs-serial.4gl line=16
  3. | ../ODI_common.h:00816(3)  : adaptStatement: stmt type = 2
  4. | ../ODI_common.h:00821(3)  : adaptStatement: ifxemul = 1
  5. | pgs.c:00869(3)  : Find serial info: [select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 'nextval.''([^'']*)') seqname , c.attname from pg_class p join pg_attrdef a   on (p.oid=a.adrelid) join pg_attribute c   on (p.oid=c.attrelid and a.adnum=c.attnum) join pg_namespace ns   on (p.relnamespace=ns.oid) where upper(p.relname)=upper('tab1') and pg_get_expr(a.adbin,0) like 'nextval%' and (ns.nspname=current_schema()  or ns.oid=pg_my_temp_schema())]
  6. | pgs.c:01853(3)  : Prefetch rows = 0
  7. | pgs.c:01755(3)  : Nat stmt1 = insert into tab1 (pkey,name) VALUES (100,'aaaa') returning tab1.pkey, (select case when tab1.pkey>=(select last_value from public.tab1_pkey_seq) then setval('public.tab1_pkey_seq',tab1.pkey,true) else 0 end)
  8. | sqlcode         : 0
  9. |   sqlerrd3      : 1
  10. | curr driver     : ident='dbmpgs'
  11. | curr connection : ident='test1@localhost:5436+driver='dbmpgs',resource='test1'' (dbspec=[test1@localhost:5436+driver='dbmpgs',resource='test1'])
  12. | Timestamp       : 2022-07-05 14:53:07.01594
  13. | Execution time  :         0 00:00:00.00650
  14.  

Seb
Sean H.
Posts: 29


« Reply #5 on: July 05, 2022, 04:25:41 pm »

That sounds very promising, how do I disable the Informix Serial Emulation?
Sebastien F.
Four Js
Posts: 509


« Reply #6 on: July 05, 2022, 04:59:55 pm »

Please take the time to read the manual, we did a lot of efforts to document.

https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagpgs_008.html

Seb
Reuben B.
Four Js
Posts: 1046


« Reply #7 on: July 06, 2022, 12:16:38 am »

That sounds very promising, how do I disable the Informix Serial Emulation?

This page https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_Connections_015.html might give a better understanding

Essentially the ODI layer parses SQL statements and emulates Informix features  in other databases.  This parsing process takes time and you can enable/disable with various FGLPROFILE parameters.   Globally with dbi.database.dsname.ifxemul  or by datatype dbi.database.dsname.ifxemul.datatype.typename

Reuben

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Sean H.
Posts: 29


« Reply #8 on: July 06, 2022, 08:56:11 am »

Thank you very much for the assistance, I have been adding all the settings to the FGLPROFILE and testing one by one

dbi.default.driver = "dbmpgs"
dbi.database.bigfatnothing.ifxemul = false
dbi.database.bigfatnothing.ifxemul.datatype.serial = false
dbi.database.bigfatnothing.ifxemul.datatype.serial8 = false
dbi.database.bigfatnothing.ifxemul.datatype.bigserial = false
dbi.database.bigfatnothing.ifxemul.datatype.serial.sqlerrd2 = false


But I am still getting the same error with the following code, I just think I am missing something in the documentation

import fgl database
schema bigfatnothing

main

   define
      sql string,
      testtable record like testtable.*

   call database.connect()
   call database.begin()

   let sql = "insert into testtable values(?, ?, ?)"
   prepare testtable_ins from sql

   let testtable.idno = 10
   let testtable.col01 = "Col01 Row02"
   let testtable.col02 = "Col02 Row02"
   execute testtable_ins using testtable.idno, testtable.col01, testtable.col02

   call database.commit()
   call database.disconnect()

end main


currval of sequence "sq_testtable" is not yet defined in this session
Sebastien F.
Four Js
Posts: 509


« Reply #9 on: July 06, 2022, 08:47:21 pm »

Seems you are using Genero FGL 03.10.23

Please try with latest 3.20 and 4.01 from our download site.

I have created a table and sequence as you did and have no issues when inserting with an explicit value for the auto-incremented column.
Code
  1. CREATE TABLE testtable (
  2.    idno INTEGER NOT NULL,
  3.    col01 VARCHAR(50) NOT NULL,
  4.    col02 VARCHAR(50) NOT NULL
  5. );
  6. ALTER TABLE testtable ADD CONSTRAINT pk_testtable PRIMARY KEY(idno);
  7. CREATE SEQUENCE sq_testtable;
  8. ALTER TABLE testtable ALTER COLUMN idno SET DEFAULT NEXTVAL('sq_testtable');
  9.  
  10.  
  11. MAIN
  12.    DEFINE rec RECORD
  13.        pkey INT,
  14.        col01 VARCHAR(50),
  15.        col02 VARCHAR(50)
  16.    END RECORD
  17.  
  18.    CONNECT TO "test1@localhost:5436+driver='dbmpgs',resource='test1'" USER "pgsuser" USING "fourjs"
  19.  
  20.    DELETE FROM testtable
  21.  
  22.    INSERT INTO testtable VALUES (1000,'aaa','bbb')
  23.    --DISPLAY sqlca.sqlerrd[2]
  24.  
  25.    INSERT INTO testtable (col01,col02) VALUES ('aaa','bbb')
  26.    --DISPLAY sqlca.sqlerrd[2]
  27.  
  28.    DECLARE c1 CURSOR FOR SELECT * FROM testtable
  29.    FOREACH c1 INTO rec.*
  30.        DISPLAY rec.*
  31.    END FOREACH
  32.  
  33. END MAIN
  34.  
  35.  
  36. sf@toro:~/genero/devel/fgl/tests$ fglcomp -M pgs-serial.4gl && fglrun pgs-serial.42m
  37.       1000aaabbb
  38.       1003aaabbb
  39.  


Seb
Sean H.
Posts: 29


« Reply #10 on: July 07, 2022, 12:13:50 pm »

Ok I will upgrade to 4.01 and try again, I just want to check though, is the License Manager (5.20.17) compatible with Genero 4.01?

After installing 4.01 I set my license vars in FGLPROFILE the same as on 3.10 but I'm getting an error

flm.license.number = "XXX#XXXXXXXX"
flm.license.key = "XXXXXXXXXXXX"
flm.server = "XX.XXX.XXX.XX"
flm.service = 6399

fglWrt -a info
(FLM-31): No license installed.
Sean H.
Posts: 29


« Reply #11 on: July 07, 2022, 01:53:31 pm »

I wasn't able to get Genero 4.01 working but just tried with Genero 3.20 instead and it is working, although without any of the ifxemul settings being set


FGLPROFILE
dbi.default.driver = "dbmpgs"
flm.license.number = "XXX#XXXXXXXX"
flm.license.key = "XXXXXXXXXXXX"
flm.server = "XX.XXX.XXX.XX"
flm.service = 6399


Testing with code similar to the original

main

   define
      sql string,
      testtable record like testtable.*

   call database.connect()
   call database.begin()

   let sql = "insert into testtable values(?, ?, ?)"
   prepare testtable_ins_serial from sql

   let sql = "insert into testtable values(default, ?, ?)"
   prepare testtable_ins from sql

   ##
   # insert a row with manual primary idno
   ##

   let testtable.idno = 10
   let testtable.col01 = "Col01 Row02"
   let testtable.col02 = "Col02 Row02"
   execute testtable_ins_serial using testtable.idno, testtable.col01, testtable.col02
   display sqlca.sqlerrd[2] using "<<<<<<<<&"
   # correct database table sequence
   call updateSequence(sqlca.sqlerrd[2] + 1)

   ##
   # insert a row with automatic primary idno
   ##

   let testtable.idno = null
   let testtable.col01 = "Col01 Row03"
   let testtable.col02 = "Col02 Row03"
   execute testtable_ins using testtable.col01, testtable.col02
   display sqlca.sqlerrd[2] using "<<<<<<<<&"

   call database.commit()
   call database.disconnect()

end main


The first display shows 10 (after manual insertion)
The second display shows 11 (after automatic insertion once the sequence has been corrected)

Does this mean it is just a bug with Genero 3.10 and we must start making plans to upgrade Customers to Genero 3.20?
Olivier E.
Four Js
Posts: 197


« Reply #12 on: July 07, 2022, 03:34:25 pm »

Hello,

FLM 5.20 doesn't work with FGL/GRE 4.0x.

In order to work with Genero 4.0x you need FLM 6.0x.

If you install FLM 6.0x -> your FGL version prior 4.01 will no more work without an update of the fglWrt inside the FGL and GRE 3.10 and 3.20.

Please have a look at the FLM 6.00 documentation: https://4js.com/online_documentation/fjs-flm-manual-html/#flm-topics/t_flm_compat_g320.html

If you get in trouble open a support case.

Olivier - Four Js Support
Sebastien F.
Four Js
Posts: 509


« Reply #13 on: July 08, 2022, 09:59:04 am »

Sean,

If you check carefully https://4js.com/support/issue/?id=FGL-05500#startissue, you will see that this is fixed in 3.20.14 and 4.00.01, so yes, you should plan to upgrade to latest 3.20 or 4.01

Seb
Sean H.
Posts: 29


« Reply #14 on: July 08, 2022, 03:03:21 pm »

Thanks very much Sebastian, I think I misunderstood, I read the First seen in version 3.20.11 and thought that meant it wasn't in 3.10 but thanks to everyone for their patience.

We will start making plans to upgrade to 3.20.
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines