SQL question

hershbp at verizon.net hershbp at verizon.net
Sun Jul 11 19:44:31 EDT 2004


On Sunday, July 11, 2004, at 06:48 PM, Paul Kocsis wrote:

> 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:
Keep in mind ,I'm talking all in standalones no data being stored to 
itself.
> revQueryDatabase(myDbid,"SELECT sales_pk FROM sales_db where time = :1 
> and
> unique_kiosk_ID = :2","tTime","tKioskID")
I'm thinking now that you brought this up, of something like this, 
every user with his own user Id stored into a container and use that 
for a "WHERE" user AND tTime, I think this might be good I'll give it a 
shot, but I know that there is something more sophisticated then this 
build into most databases. (I use Postgresql.)
Thanks,  I'll use your approach..
Thanks again, Hershel

>
> ...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
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>



More information about the use-livecode mailing list