next »

[Sean H.]

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.]

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.]

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

[Sean H.]

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.]

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

SQL: INSERT INTO tab1 (pkey,name) VALUES (100,'aaaa')
 | 4gl source      : pgs-serial.4gl line=16
 | ../ODI_common.h:00816(3)  : adaptStatement: stmt type = 2
 | ../ODI_common.h:00821(3)  : adaptStatement: ifxemul = 1
 | 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())]
 | pgs.c:01853(3)  : Prefetch rows = 0
 | 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)
 | sqlcode         : 0
 |   sqlerrd3      : 1
 | curr driver     : ident='dbmpgs'
 | curr connection : ident='test1@localhost:5436+driver='dbmpgs',resource='test1'' (dbspec=[test1@localhost:5436+driver='dbmpgs',resource='test1'])
 | Timestamp       : 2022-07-05 14:53:07.01594
 | Execution time  :         0 00:00:00.00650
 

Seb

[Sean H.]

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

[Sebastien F.]

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.]

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

[Sean H.]

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.]

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

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');
 
 
MAIN
    DEFINE rec RECORD
        pkey INT,
        col01 VARCHAR(50),
        col02 VARCHAR(50)
    END RECORD
 
    CONNECT TO "test1@localhost:5436+driver='dbmpgs',resource='test1'" USER "pgsuser" USING "fourjs"
 
    DELETE FROM testtable
 
    INSERT INTO testtable VALUES (1000,'aaa','bbb')
    --DISPLAY sqlca.sqlerrd[2]
 
    INSERT INTO testtable (col01,col02) VALUES ('aaa','bbb')
    --DISPLAY sqlca.sqlerrd[2]
 
    DECLARE c1 CURSOR FOR SELECT * FROM testtable
    FOREACH c1 INTO rec.*
        DISPLAY rec.*
    END FOREACH
 
END MAIN
 
 
sf@toro:~/genero/devel/fgl/tests$ fglcomp -M pgs-serial.4gl && fglrun pgs-serial.42m
       1000aaabbb
       1003aaabbb
 

Seb

[Sean H.]

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.]

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.]

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.]

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.]

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.