All my attempts fail, can we talk serial generator methodologies

Paul Mulroney pmulroney at logicaldevelopments.com.au
Wed Jan 31 04:35:18 EST 2018


Hi Das,

This is how we use the nextval() function in Postgres:

In our insert routine, we have the following code:

We have an instance var ioStatement which is a Postgres statement object.  We also have a method called $checkForError that logs the method stack etc so we can report any SQL errors.

vnRecordID is a 32-bit integer

	Calculate vsQuery as con("SELECT nextval('",vsFileName,'_',vsSeqName,"_seq')")     ;; eg SELECT nextval('fContractItem_seq_seq') -> all sequence fields in postgres are labelled this way
	Do ioStatement.$execdirect(vsQuery)
	Calculate vsErr as ioStatement.$errorcode
	If vsErr<>kDAMNoError     ;; We got an error
		Do method $checkForError ($cmethod().$name,ioStatement)     ;; This does detailed handling - it will log it to the debug log etc.
	End If
	Do ioStatement.$fetchinto(vnRecordID)     ;; Remember it locally

We call this to get a sequence number, and then we call the main insert to save the record.  Note the row variable vrNewRow contains all the values to insert for the record. vsFileName is the table name.

Calculate vrNewRow.[vsSeqName] as vnRecordID     ;; Save in the row we're about to insert
Calculate vsQuery as con('INSERT INTO ',vsFileName,' ',replaceall(clSession.$insertnames(vrNewRow),'"',''))     ;; -- PRH - In Studio8 mixed case column names will be wrapped in "" but postgres is all lowercase so get rid of the "" and postgres will treat it like all lower case
Do ioStatement.$execdirect(vsQuery)
Calculate vsErr as ioStatement.$errorcode
If vsErr<>kDAMNoError     ;; We got an error
	Do method $checkForError ($cmethod().$name,ioStatement)     ;; This does detailed handling - it will log it to the debug log etc.
End If

Hope this helps!

Regards,
Paul.


> On 30 Jan 2018, at 6:00 pm, omnisdev-en-request at lists.omnis-dev.com wrote:
> 
> Message: 16
> Date: Mon, 29 Jan 2018 17:36:14 -0800
> From: Das Goravani <das at Goravani.com <mailto:das at Goravani.com>>
> To: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>>
> Subject: All my attempts fail, can we talk serial generator
> 	methodologies
> Message-ID: <3264CA36-7206-4AC8-8C2F-ADAE1D3883A9 at Goravani.com <mailto:3264CA36-7206-4AC8-8C2F-ADAE1D3883A9 at Goravani.com>>
> Content-Type: text/plain;	charset=utf-8
> 
> 
> In a generalized way of answering, what are the methods of doing serial generators because following the Postgres Manual and what I have heard on the board I can?t get to work
> 
> Specifically I cannot get an INSERT INTO statement to work. I keep getting ?unrecognized attribute? from Omnis who will not do the $prepare statement.  It will not even try, it stops execution and complains.  This is true Inside a Table as well.
> 
> Is there a way to do serial generation with the simple $insert command that comes with the row.
> 
> I tried everything that?s been said to me.  Kelly caught that my code was only Table compatible, so I got it into a table, called it, and got the same error message.  I get why he moved me there? and I handled everything right? still. ?Unrecognized custom attribute?
> 
> The ONLY ways I?ve seen of doing serial generation is to package it into an insert into statement.
> 
> But I can?t get one to work. 2 days now I?ve been struggling with this.  I know what I?m doing to some degree.. don?t think me daft completely. 
> 
> This is really odd and baffling?should be such a simple thing
> 
> I am running code successfully, for example I have session pools implemented successfully
> 
> When I create the sequence generators and column defaults for the serial number, I get positive flags on my code step by step
> 
> I have other SQL statements in both Sta commands and exec direct and they are working.  
> 
> I get it, but I?m having a really odd one.  INSERT INTO? cannot get it to work
> 
> How else can I do serial generators and get the number into the data and saved


I was going to look for my missing watch, but I could never find the time.
(http://onelinefun.com)
-- 
Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au       Trading as Logical Developments
www.logicaldevelopments.com.au                   ACN 161 009 374 
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102







More information about the omnisdev-en mailing list