SQL SELECT - xbasic statement to assign a variable

[CharlesParker]

Having some trouble, been working on this off and on for 3 days thinking I would "get it" eventually.
I have read, researched,etc and I am finally asking for some help because I know I am close but ready to drown the keyboard in the bathtub.

I have a button that does an AJAX callback to do several things, geocode, save, run a function to INSERT into jqcalendar (one of my tables)
then I thought man it sure would be fun to do another INSERT into a different table grabbing the pk of the last INSERT
So I added in an AJAX callback to run another xbasic function - mostly because I kept getting a zero for the pk.

I have a guuid that gets submitted into jqcalendar so I thought I will just grab the pk from the row that matches the guuid rather than look for the lastinserted identity which seemed troublesome to me anyways.
so here my code to SELECT the Id WHERE the guuid matches the guuid in the table

Can someone tell me why they think this is a no go?

function create_task as c (e as p)'debug(1)
dim cn as sql::Connection
dim args as sql::arguments
dim mynewpk as C


	args.Add("subject",e._currentRowDataNew.Full_Name)
 	args.Add("jobguid",e._currentRowDataNew.taskguid)
	args.Add("mynewpk",mypk)




sqlget = "SELECT Id 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")
cn.Execute(sql3,args)
cn.close()
end if
end function

Looks good to me, lol
I could not use the debugger because I am getting img errors for the buttons that I have but i can verify that the field names are all accurate so there must be some sorta syntax/code snot.

[Davidk]

Re: SQL SELECT - xbasic statement to assign a variable

Charles, you must debug through this to see what's wrong. Take your code and put it into a new UX, make your callbacks and see what's going on. Hard code whatever you need to in order for statements to run in a test.

Maybe it's your cut and paste... but don't do this...

function create_task as c (e as p)'debug(1)

Next, use args.set... not args.add

Next, where is mypk coming from? Nevermind... I see it now. But, you're using mypk before it's assigned.

Next, there is no error checking in your code at all. How can you hope to know what's going on if you don't debug through it, or at least have error checking code sending back information.

[GGandhi]

Re: SQL SELECT - xbasic statement to assign a variable

I probably won't do the way you do to get the primary id, however since you are experimenting, my feeling is
when you set the
args.Add("mynewpk",mypk)

mypk = rs.data("Id") does not exist. so it is not assigning I think.
if you put that line after you assign this line then the args.add will add probably. ( args.set is better than args.add)
I normally get the primary key from the connection object and use it.
I do not plan to play with this so I am just thinking out loud.

[CharlesParker]

Re: SQL SELECT - xbasic statement to assign a variable

By "exists" do you mean there isn't a value in the database yet? I think there should be at this point. The previous Ajax callback runs a function to create the row in jqcalendar with that guuid value. If on the other hand you think it isn't in the .data yet you might need to explain it to me better, also I am trying to get the primary key from the record that was created in the other previous function and since after all of my reading on it, it seemed there's a bit of unreliability so like I said I just made a unique key and look for a match, but I can't seem to set a variable from a sql lookup in xbasic.
I thought this would be easier but I am still pretty new to xbasic and alpha, the documentation is very abrupt with not enough examples or working code to help me understand.
It's a journey!

[Davidk]

Re: SQL SELECT - xbasic statement to assign a variable

Your code should blow up because...

args.Add("mynewpk",mypk)

is using mypk which does not exist... where you're using it.

[CharlesParker]

Re: SQL SELECT - xbasic statement to assign a variable

Thanks guys, and David your words are well spoken. Error checking is left out because I was trying to strip out everything except what was required - for me and maybe this is stupid but I write error checking AFTER I get it working because I beleive that errors are just possibilities...

Good idea on creating a test component to step through the debugging process - thanks!
I cannot honestly say I know what I am doing, with zero training I cut copy paste and observe other peoples code and that's probably taken out of context - yes that is a recipe for disaster -
Thanks for taking to the time to comment and whatnot. I'll work on this some more, setup a test component and see what I can do with the advice so far.

[lvasic]

Re: SQL SELECT - xbasic statement to assign a variable

if you put a debug(1) above this line "mypk = rs.data("Id")" do you get a value?
Also, this syntax is more like this:
INSERT INTO alphanwcopro.company_jobs_tasks
(tasks_jobs_id,Subject)
values(:mynewpk,:subject)

[Davidk]

Re: SQL SELECT - xbasic statement to assign a variable

Errors are certainties... unless you prepare for them... and then, sometimes, they won't appear.

The one major error in your code is that you're using mypk before you create it. XBasic will break on this.

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

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

[[email protected]]

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.

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

[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

[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

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