SQL SELECT - xbasic statement to assign a variable

[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

[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

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.

[-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.

[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.

[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.

[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?

[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

You guys are all awesome, and no I am not going to get confused (probably)
As far as using a trigger, I do not want to use one because it's up to the end user to decide if he wants to create the additional INSERT by clicking a button to perform the different action, in other words it's an option.
I know what your saying about writing it down in plain English, my nemesis is not knowing exactly what I want up front. I have found that so far I over think things, then trim back to what is really important. I give too many options then later realize that all my customer/end user really uses is let's say 5 of the 15 fields - OR - I suddenly realize that I should really have another table for rollbacks/history.
I am still working on this insert but it is now working as expected! One issue was not running things synchronously, and that goes back to what Ghandi was saying about only giving you guys partial info, but in the end the advice you all have provided has helped me figure out not only what I needed to do but also what SHOULD be done to make the code better.
Thanks, I will post more as I progress through it. So far so good though!
THANKS A MILLION!

[lvasic]

Re: SQL SELECT - xbasic statement to assign a variable

Your Code:
INSERT INTO alphanwcopro.company_jobs_tasks
(tasks_jobs_id,Subject)
SELECT Id,subject FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid

[lvasic]

Re: SQL SELECT - xbasic statement to assign a variable

You can do this in 1 SQL statement: I've created a test...

create table test1
(
tID int,
tSubject varchar(50),
tLocation varchar(50)
)

Insert into Test1
(tID,tSubject,tLocation)
Select ID,Subject,Location from jqcalendar where ID = 164

Select * from test1

[GGandhi]

Re: SQL SELECT - xbasic statement to assign a variable

Syntax of an Update Statement
Update customer set lastname = :newLastname, firstname = :newFirstname where customer_id = :oldCustomer_Id

Syntax of an Insert Statement
Insert into customer (firstname, lastname, company) Values (:newFirstname, :newLastname, :NewCompany)

yes these two ways are acceptable.
in one method you are explicitly stating which field will get what value and the other which field will get what value in the enumerated list.
you are giving portions of the code, and expecting to solve the puzzle in bits and pieces.
if you go back to your code as david said there is no error trapping. a common method will be when result is dimmed as logical value

result = cn.open(.........)
if result then
  result = cn.execute(sql statement and arguments)
  if result then
    do something
  else 
   do something
  end if 
else
do something
end if

also you need to trap to see if there were any records returned before you assign the result set data to a variable.

and finally regarding thought process
if you sit down and write in plain english what you want to accomplish before you write a single line of code you might be
able to solve the whole thing with few ajax calls than many as i infer from the post. and possibly ask the sql back end to do the job for you as suggested by pieter rather than come back to alpha and
you write some more code and ask alpha to do the work.

edit:
i just tested single field query in mySql and it does return correct value for the primary key without any problem. harvesting all the fields only to use a single field is wasting the resources. not that the computer will cross its hands and refuse to do any more work for you.

[computoria]

Re: SQL SELECT - xbasic statement to assign a variable

Charles,

I hope I am not going to confuse you. And I didn't read all the details.
But if based on an insert in a table A, there should be another insert in a table B. Then I would solve that with a trigger/procedure in the database and not with xbasic.

[Davidk]

Re: SQL SELECT - xbasic statement to assign a variable

That type of Insert works in MySQL (and maybe it's derivatives) but it's non-standard SQL. Unless it's widely adopted I would consider it a bit dangerous. If you ever decided to switch to SQL Server your statements would fail.

There's no reason why "Select Id" and rs.data("Id") cannot be used... as long as Id is actually the field name. Try rs.data(1) since you're only returning 1 column. But... returning all fields is a waste.

[CharlesParker]

Re: SQL SELECT - xbasic statement to assign a variable

Well I am still working on this, I did get an insert based on the SQL lookup by moving mypk AND instead of
"SELECT Id FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid"
I changed it to
"SELECT * FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid"

This seems weird to me, I mean why did it fail when I was being so specific as to say I just wanted the ID column?
if I used the sql genie, it returned the correct value...


SO this is an excerpt from the working code (minus the args, etc.)

sqlget = "SELECT * FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid"	
sql3 = "INSERT INTO alphanwcopro.company_jobs_tasks set tasks_jobs_id = :mynewpk, Subject = :subject"	
	
cn.open("::Name::Connection1")
cn.Execute(sqlget,args)
dim rs as sql::ResultSet
rs = cn.ResultSet
mypk = rs.data("Id")
args.set("mynewpk",mypk)
cn.Execute(sql3,args)
cn.close()

David I am sure your vomiting a little with my lack of error trapping...I did read your posts about using flags for the conn string, etc on the msg board - honestly I did, lol I am just trying to get this thing working!


Also while we are on the subject of xbasic code and crud operations with xbasic, Lee brings up a question for me - should I be doing as he suggests or as I have done. My method works - but is what he does better/preferred? I find it difficult to read when you list all the fields, then the values in order like that - does it matter? I saw here
http://www.downloads.alphasoftware.c...SQLTables.html

BOTH methods used, with INSERTS one way (like Lee says) and UPDATE another way (like I am doing)
to quote:

Syntax of an Update Statement
Update customer set lastname = :newLastname, firstname = :newFirstname where customer_id = :oldCustomer_Id

Syntax of an Insert Statement
Insert into customer (firstname, lastname, company) Values (:newFirstname, :newLastname, :NewCompany)

To me it seems interchangeable...is it?