Please free me from this - I'll PAY

Bastiaan Olij bastiaan at basenlily.me
Tue Jan 30 16:30:59 EST 2018


Hey Das,

Hurray for not giving up!!

I think you're biggest issue with what you've been doing so far is using
a separate statement object because it screws up your scope. This
approach works because begin statement/end statement does one thing
different then calculating a string and sending that to $prepare or
$execdirect. Statement blocks retains the scope of where the "sta:"
command is executed.

So seeing your code runs in a method of your table class, ergo runs
within the scope of your list, $cinst points to the row you're trying to
insert into your database and you're query works.

When you do:
Calculate lvSQL as '<your insert statement goes here>'
Do tstaobj.$prepare(lvSQL)
The SQL is simply text being send to $prepare. It is now parsed when
inside of tstaobj and $cinst now points to tstaobj, your row has now
fallen outside of the scope of the executing method and you're query
can't be parsed, hense your error.

The special case here, as I've suggested in a few of my earlier emails,
is doing
Calculate lvSQL as '<your insert statement goes here>'
Do $cinst.$statementobject.$prepare(lvSQL)

The $statementobject property of your list contains a statement object
which is automatically created when you assign $session which retains
the scope of the row and it will parse your query correcty.

So why use lvSQL as a local variable over statement blocks? Because
statement blocks are global and once your program gets complex enough
there are situations where you're code can be interupted by events,
timers, etc. IF that happens in the middle of building your query in a
statement block, and you end up executing another query, funky things
start happening.
So while I love statement blocks for their easy of use and readability,
there are situations where their use can be dangerous especially when
you do loops or method calls to build a complex SQL query.

Lastly, your confusion about next_val I think is because many including
myself made the assumption that you had set next_val as the default for
the column. Hence leaving out "cust_id" from the query or using the
"default" keyword would have worked. But if you haven't specified it as
the default (you do this in your create table command when creating the
table) that obviously doesn't work.

Anyway, sounds like you're well on your way !

Cheers,

Bas


On 31/1/18 6:15 am, Das Goravani wrote:
>
> SUCCESS AT LAST
>
>
> Begin statement
> Sta: {INSERT INTO customers ("cust_id","cust_firstname","cust_lastname","cust_email","cust_phone","cust_address1","cust_address2","cust_city","cust_state","cust_zip","cust_balowed","cust_bizname") VALUES (nextval('seq_cust_id'),@[$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')}
> End statement
> Do tstaobj.$prepare() Returns lReturnFlag
> Do tstaobj.$execute() Returns lReturnFlag
>
>
> I SIMPLY HAD TO TURN THE CALLING ITEM FROM MY LIST TO THE STATEMENT OBJECT
>
> NOW THE ABOVE CODE GIVES THE SEQUENCE NUMBER IN THE NEW RECORD
>
> NOTE I’M USING NEXTVAL, a Postgres thing, and NOT DEFAULT, so I guess I can turn off my Default for that column
>
> PHEWY,  I finally am able to INSERT A RECORD WITH A SEQUENCE NUMBER
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at 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




More information about the omnisdev-en mailing list