SQL question

Sun Jul 11 18:48:55 EDT 2004

Hershel,

Maybe I'm really losing it here, or am just not understanding...BUT...you
said you execute:

put the time into tTime
revdb_execute(dbid,"INSERT INTO sales_db time VALUES"& tTime&"')"

...then you say:

then the sales_db field sales_pk automatically generates the next
unique serial number.
then I need to do
put revQueryDatabase( myDbid,"SELECT sales_pk FROM sales_db") into tCs

...I'm not understanding why you just don't do this instead:
revQueryDatabase(myDbid,"SELECT sales_pk FROM sales_db where time =
:1","tTime")

and get the exact "sales_pk" from the record you just inserted.  If you're
worried that (in doing it this way) 2 kiosks might insert at the exact
second, then you simply need to add a column to your sales_db of something
like "unique_kiosk_ID"....and on your first insert, you insert the "tTime"
and a unique kiosk ID # (for example, stored in a variable called
"tKioskID"), so that on your subsequent query, you can use:
revQueryDatabase(myDbid,"SELECT sales_pk FROM sales_db where time = :1 and
unique_kiosk_ID = :2","tTime","tKioskID")

...to then retrieve the *absolutely correct* newly created sales_pk.....no??

Paul Kocsis

----- Original Message ----- 
From: <hershbp at verizon.net>
To: "How to use Revolution" <use-revolution at lists.runrev.com>
Sent: Sunday, July 11, 2004 4:44 PM
Subject: Re: SQL question

Ok, I have a kiosk app.
the user puts in all line items.
item1    x.xx
item2    x.xx
and so on
now when the user clicks on "ok" it creates a new record in the sales_db
put the time into tTime
revdb_execute(dbid,"INSERT INTO sales_db time VALUES"& tTime&"')"
then the sales_db field sales_pk automatically generates the next
unique serial number.
then I need to do

put revQueryDatabase( myDbid,"SELECT sales_pk FROM sales_db") into tCs
--so I have all sales_pk's then I go to the last record to identify the
one I just created and I put it into a var
revMoveToLastRecord(tCs)
put revFldByName(tCs,"sales_pk) into tPk-- so now I got the pk created
last , so
set the itemDelimiter to tab

loop for each line in fld line items
   put item 2 of fld line items into tPrice-- and the same for items
   put revdb_execute(dbid,"INSERT INTO line_items
(sales_fk,(item_description,price) VALUES ('"&tPk&"'"&,"item1,x.xx)
end repeat
Now if after I created the insert, the next register (or client ) did
the same between my insert and select, I'll get the next last pk. and
not the one I created.
I know there is a way to get the last record created by a certain
connection. something like record set from the insert just don't how to
get it
Thanks,

On Sunday, July 11, 2004, at 04:58 PM, Paul Kocsis wrote:

> What does your sql INSERT statement actually look like?  I guess I'm
> not
> fully understanding the dilemma...I remember "way back" when using
> Informix,
> there was a software system where it was important to obtain an
> Informix
> "internally generated" column called 'rowid'.  (before Informix
> recommended
> that one *not* utilize 'rowid'....or before I actually read that
> recommendation ;)
>
> ...anyway, I seem to recall some dilemma in wanting to know the rowid
> of a
> newly inserted record....so in that case, and maybe in yours, Andre's
> suggestion of SELECTing for all the columns that you inserted is
> certainly
> an avenue...provided that the collection of data from that record, as
> a set,
> is guaranteed to be unique...if the collection of data columns is not
> guaranteed to be unique...then you might have to add another column
> that is
> either guaranteed to be unique itself, or at least make the collection
> of
> all the inserted fields unique...for your subsequent SELECT...
>
> ...are we getting anywhere?
>
> Paul Kocsis
> ----- Original Message -----
> From: <hershbp at verizon.net>
> To: "How to use Revolution" <use-revolution at lists.runrev.com>
> Sent: Sunday, July 11, 2004 3:37 PM
> Subject: Re: SQL question
>
>
>
> On Sunday, July 11, 2004, at 04:07 PM, Andre Garzia wrote:
>
>>
>> Hershel,
>>
>> since you're doing the INSERTS you have access to the inserted data,
>> can't your refine your SELECT query so that you SELECT WHERE and put
>> all the inserted data as refinements, this way you'll retrieve the
>> correct record. Thats what I use here.
>
> I don't think this will work in my case because basically what I'm
> inserting is just any piece of info e.g. the time  to create a new
> record (in an e.g.sales table ). The db auto creates a pk , then I need
> to take this pk from the sales table and insert it for a fk in a
> line_items table to have all line items (every transaction related to
> its sale number ) and then I take out the fk from the line_items
> (SELECT") and go back to the sales_db add the sales amount to the db
> with an "ALTER db SET sale_amount ='xx.xx' WHERE pk_auto = 'thepk'
> Sales_db , pk_auto, sale_amount,date,time
> Line_items_db, item_name,item_price,sales_fk
> Thanks , Hershel
>
>>
>> Cheers
>> andre
>>
>>
>> On Jul 11, 2004, at 4:48 PM, hershbp at verizon.net wrote:
>>
>>> HI ,
>>> How do I "INSERT" A SQL statement and return some of  the record or
>>> field info  immediately ?
>>> I'll try to elaborate. A database app. running many clients, I create
>>> a new record and want to get the primary key of that newly created
>>> record. If I'll do an "INSERT" and then go the last record via
>>> "SELECT" then between the INSERT and the SELECT somebody else from a
>>> different location can insert a new record then when I do the select
>>> to get the last as mentioned above I'll get the wrong pk. How do I
>>> overcome that ?
>>> Thanks , Hershel.
>>>
>> -- 
>> Andre Alves Garzia  2004  BRAZIL
>> http://studio.soapdog.org
>>
>> _______________________________________________
>> use-revolution mailing list
>> use-revolution at lists.runrev.com
>> http://lists.runrev.com/mailman/listinfo/use-revolution
>>
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>

_______________________________________________
use-revolution mailing list
use-revolution at lists.runrev.com
http://lists.runrev.com/mailman/listinfo/use-revolution