SQL SELECT - xbasic statement to assign a variable

[coleresources]

Re: SQL SELECT - xbasic statement to assign a variable

Charles,

I've used this function to handle this sort of thing and it has worked well for me.

pk = cn.LastInsertedIdentity()

[CharlesParker]

Re: SQL SELECT - xbasic statement to assign a variable

Yes, I looked into pk = cn.LastInsertedIdentity() and perhaps it would have worked as expected for me had I assigned my variable prior to calling it as was the case. I may re-visit that but again, there seems to be quite a bit of "may not return what you expect" in that so I devised the guuid field to match up the two records and get the pk from the original insert of the other record - plus when people reopen the record and decide that they want to create the additional record/option they will be able to match up the record even if it was already inserted...I think my thought process on that is reasonable...not sure but I think. I don't know if pk = cn.LastInsertedIdentity() will return the same value when you update the record, because it wasn't an INSERT.


Also, here is some more fodder for discussion relevant to this very topic and David specifically. David said that I should use Args.set rather than Args.add - is this when setting the variable mypk? OR do you mean likewise when setting the args from currentRowData.new?
I would like some clarification on when to use args.add vs set at least in this instance anyways... I am pretty sure I have seen that same thing said before about args.add - whats the difference?

[Davidk]

Re: SQL SELECT - xbasic statement to assign a variable

Just always use args.set. Have a look in the doc to see what each does and you'll see why.

[coleresources]

Re: SQL SELECT - xbasic statement to assign a variable

Charles,

I did not read your post fully. I'll post again once I have a little more time. Maybe there is more complexity then I originally thought. I was mainly addressing the second insert and needing the pk for the record of your prior insert. If it's as simple as that, then it works well.

[-Jinx-]

Re: SQL SELECT - xbasic statement to assign a variable

It just so happens that I tackled something very similar yesterday so I figured I'd throw in my two cents. I had to do some code to create a new record (actually a copy record function) using multiple tables. I needed the id field out of the first table for foreign keys in the secondary tables.

I tend to use SQL to do as much as possible to try to take some load off of the application server as an SQL server can handle it much better. My solution was to use an SQL transaction and get the ID during the transaction. I'm using MSSQL and this is a snippet of my code:

.........................
		if cn.open("::Name::MVWKS")	then
			dim args as SQL::Arguments
			args.Add("masteruid",masterid)
			args.Add("edit",upper(a5ws_getcurrentuser()))
			if revtest < 1 then
				args.Add("rev",0)
				args.Add("op",convert_type(e.dataSubmitted.OPNO,"N")+1)
			else
				args.Add("rev",1)
				args.Add("prty",convert_type(e.dataSubmitted.OPNO,"N"))
			end if
			dim SQL as c = <<%longstring%
				BEGIN TRANSACTION [CopyLayout]
				BEGIN TRY
				
				DECLARE @newID INT
				
				INSERT INTO machlayoutmaster
				(machno, machtype, partno, dwgno, mainprgno, layoutrev, opno, numparts, lastedby, [status], notes, [priority], hassubs, prgcall, lasteddt)
				SELECT machno, machtype, partno, dwgno, mainprgno, layoutrev+:rev AS layoutrev, opno, numparts, :edit AS lastedby, 'NEW' AS [status], notes, :prty AS [priority], hassubs, prgcall, GETDATE() AS lasteddt
				FROM machlayoutmaster
				WHERE unique_id = :masteruid
				
				SELECT @newID = SCOPE_IDENTITY()
				
				INSERT INTO MachLayoutDetail
				(layoutmaster_id, position, sequence, tool, toolinfo, notes, layoutord, desc2)
				SELECT @newID AS layoutmaster_id, position, sequence, tool, toolinfo, notes, layoutord, desc2
				FROM MachLayoutDetail
				WHERE layoutmaster_id = :masteruid
				
				INSERT INTO MachLayoutSubprograms
				(layoutmaster_id, mainprg, sequence, subprg, [description])
				SELECT @newID AS layoutmaster_id, mainprg, sequence, subprg, [description]
				FROM MachLayoutSubprograms
				WHERE layoutmaster_id = :masteruid
				
				COMMIT TRANSACTION [CopyLayout]
				
				END TRY
				BEGIN CATCH
					ROLLBACK TRANSACTION [CopyLayout]
					PRINT ERROR_MESSAGE()
				END CATCH
			%longstring%
...................

The key is creating the variable at the top: DECLARE @newID INT
and then setting that variable to the inserted identity directly after the first insert: SELECT @newID = SCOPE_IDENTITY()
After which you can use the variable @newID for your foreign key in your child tables.

As I understand it, if something goes wrong the transaction won't commit, but if it goes right, you have your identity for the subsequent inserts which will also cancel the transaction if they don't work properly.
This keeps the Xbasic to a minimum, which as I've been finding lately, is almost always a good thing.

Anyway, just another option.

[aburningflame]

Re: SQL SELECT - xbasic statement to assign a variable

We have used conn.LastInsertedIdentity() with very little issue over the past 4-5 years. There is also one conn.CallResult.LastInsertedIdentity and this one I find can be unreliable.
However, code like this should do what you expect (untested but just illustrating the idea).

dim conn as SQL::Connection
dim args as SQL::Arguments

if conn.open("::name::conn") then
     args.set("customer","John Smith")
    'assumes order table has a autoincrement PK
     sql="INSERT INTO order (customer) VALUES (:customer)"

     if conn.execute(sql,args) then
          dim order_id as N = conn.LastInsertedIdentity() 
          args.set("order_id",order_id)
          args.set("item_details", "test_item")

          sql="INSERT INTO order_detail (order_id,details) VALUES (:order_id, :item_details)"
          if conn.execute(sql,args) then
 
          else
              'handle order_detail insert failure
          end if
     else
         'handle sql execute error
     end if
     conn.close()
else
   'handle connection error
end if

Also, if you already know the guid you can do what lvasic suggested:
e.g.

args.set("jobguid","1294019-219401942-92142491")
args.set("subject", "my subject")
sql = "INSERT INTO alphanwcopro.company_jobs_tasks (tasks_job_id,subject) SELECT Id, :subject FROM alphanwcopro.jqcalendar WHERE taskguid=:jobguid"

Also if you are doing a 2+ table insert, you may want to look into transactions and the conn.begintransaction() and conn.committransaction() / conn.rollbacktransaction() methods.

[CharlesParker]

Re: SQL SELECT - xbasic statement to assign a variable

Thanks Scott, I am sure this thread will be viewed many times in the future. Everyone has been so helpful, this is the kind of stuff that documentation really cannot address in full due to so many variables but gives everyone insight into how it can be done.
Personally I have come across code blocks like this in other areas that helped me write my VERY FIRST case select statement from scratch and it worked first time! I was even able to write in error code, I was so pleased with my self I went and had an ice cream cone.
Cheers all ~

[aburningflame]

Re: SQL SELECT - xbasic statement to assign a variable

Not a problem Charles! Glad I could help give some insight. I will also go have an ice cream cone! :) Have a great weekend