On Sunday, July 11, 2004, at 06:48 PM, Paul Kocsis wrote:
Keep in mind ,I'm talking all in standalones no data being stored to itself.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 andI'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.)
unique_kiosk_ID = :2","tTime","tKioskID")
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: <[EMAIL PROTECTED]> To: "How to use Revolution" <[EMAIL PROTECTED]> 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: <[EMAIL PROTECTED]> To: "How to use Revolution" <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
_______________________________________________ use-revolution mailing list [EMAIL PROTECTED] http://lists.runrev.com/mailman/listinfo/use-revolution
