SQL statement help

[shinra]

I have this statement

specs_name = e.datasubmitted.specs_name
sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = '" + specs_name + "'"
flag1 = cn.Execute(sqlCommand)

which the specs_name is passing correctly, but I'm not getting anything for id or total_len. I do have the necessary dims above. What am I missing?

Thanks in advance

[Steve Wood]

Re: SQL statement help

To help debug, add this to the bottom of your code:

result = cn.callresult.text + " | " + sqlCommand
save_to_file(result,"c:\somefolder\result.txt")

Run it, then go look at result.txt to see both the result of the SQL query and the final sql query that was submitted.

Also, try this syntax to ensure your where clause variable is properly formatted:


...
dim args as sql::arguments
specs_name = e.datasubmitted.specs_name
sqlCommand = "SELECT id, total_len FROM dbo.txd_specs WHERE Specs_Name = :specs_name"
args.add("specs_name",specs_name)
flag1 = cn.Execute(sqlCommand,args)

[shinra]

Re: SQL statement help

Thanks for the quick reply.. Here is the result from the txt file

Success | select id, total_len from dbo.txd_specs where Specs_Name = :specs_name

[shinra]

Re: SQL statement help

Also I forgot to mention that the args returns a 1. Like it should be.

[Steve Wood]

Re: SQL statement help

Is specs_name supposed to be numeric or string?

Add this to the debugging code.

sqlCommand = replace_arguments_in_string(sqlCommand,args)
result = cn.callresult.text + " | " + sqlCommand
save_to_file(result,"c:\somefolder\result.txt")

You will likely get:
sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = 1"

If specs_name is a character string, then DIM your variable as such, as in:
dim specs_name as c = e.datasubmitted.specs_name
And then you will get:
sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = '1'"

The real test is you take the resulting full sqlCommand string and attempt to fire it in whatever sql tool you use to manage your database. That usually gives a little more information about why the query is not working.

[shinra]

Re: SQL statement help

In one table it is numeric and in the other it is string.

[shinra]

Re: SQL statement help

That's the thing.. it's changed to this

sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = specs_name"

the results are correct in the db. When I try to do it via xbasic nope...

[shinra]

Re: SQL statement help

Here is my entire code...

function serverside_489226b2b8b44369853d818b3cb4049c as c (e as p)

serverside_489226b2b8b44369853d818b3cb4049c = "//Replace this with the javascript to be returned by the function."

dim id as n
dim specs_name as c = e.datasubmitted.specs_name
dim txd_specs_id as n
dim layout_pos as n
dim format as c
dim default as c
dim start_pos as n
dim end_pos as n
dim naaccr_item as n
dim naaccr_name as c
dim total_len as n
dim cmd as c
dim cmd1 as c
dim cmd2 as c
dim cmd3 as c
dim cmd4 as c
dim cmd5 as c
dim cmd6 as c
dim cn as sql::Connection
dim sqlCommand as c
dim sqlCommand1 as c
debug(1)
flag = cn.Open("::Name::demodata")
dim args as sql::Arguments
'specs_name = e.datasubmitted.specs_name
sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = specs_name"
'sqlCommand = replace_arguments_in_string(sqlCommand,args)
'"+specs_name+"'
'args.add("specs_name",specs_name)
flag1 = cn.Execute(sqlCommand)
'id = .e_set.ID = ("id")
'total_len = .e_set.Total_Len = ("total_len")
'result = cn.callresult.text + " | " + sqlCommand
'save_to_file(result,"c:\debug\result.txt")

debug(1)

sqlCommand1 = "select * from dbo.Txd_Specs_Mv where txd_specs_id = txd_specs_id"
if (flag .and. cn.Execute(sqlCommand1))
dim rs as SQL::ResultSet = cn.ResultSet
dim cnt as n = 0
txd_specs_id = rs.Data("txd_specs_id")

While (rs.NextRow() .and. (cnt < 20))

layout_pos = rs.data("layout_pos")
format = rs.Data("format")
default = rs.Data("default")
start_pos = rs.Data("start_pos")
end_pos = rs.Data("end_pos")
naaccr_item = rs.Data("naaccr_item")
naaccr_name = rs.Data("naaccr_name")
cnt = cnt + 1
row = convert_type(cnt,"C")

cmd = "e._set.Layout_Pos_a5instance"+row+".value = layout_pos"
cmd1 = "e._set.Format_a5instance"+row+".value = format"
cmd2 = "e._set.Default_a5instance"+row+".value = default"
cmd3 = "e._set.Start_Pos_a5instance"+row+".value = start_pos"
cmd4 = "e._set.End_Pos_a5instance"+row+".value = end_pos"
cmd5 = "e._set.Naaccr_Item_a5instance"+row+".value = naaccr_item"
cmd6 = "e._set.Naaccr_Name_a5instance"+row+".value = naaccr_name"

evaluate_template(cmd)
evaluate_template(cmd1)
evaluate_template(cmd2)
evaluate_template(cmd3)
evaluate_template(cmd4)
evaluate_template(cmd5)
evaluate_template(cmd6)
end while
end if
if flag then
cn.Close()
end if
end function

[Steve Wood]

Re: SQL statement help

In your original post there wasn't an "other table", so the question is evolving.

I think I confused you by throwing arguments in there, even though it is the preferred way to use variables in sql using xbasic.

These two statements will never work because of the parts I highlighted:

sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = specs_name"
sqlCommand1 = "select * from dbo.Txd_Specs_Mv where txd_specs_id = txd_specs_id"

There are three ways to use variables in an sql query:

Using Arguments
sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = :specs_name"
args.add("specs_name",value)
cn.execute(sqlCommand,args)
...

Using Variables
If var is numeric: sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = " + specs_name
If var is char : sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = '" + specs_name +"'"
cn.execute(sqlCommand)
...

Using {}
If var is numeric: sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = {specs_name}
If var is char : sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = '{specs_name}'
sqlCommand = evaluate_string(sqlCommand)
cn.execute(sqlCommand)
...

[shinra]

Re: SQL statement help

Sorry I thought I said that in my first post... I used this:

sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = '" + specs_name +"'"

and although my specs_name is correct the id and total_len are still 0.

[Steve Wood]

Re: SQL statement help

Confusing because your first example is incomplete, there is nothing in your code that will show you got or didn't get your results. Then your "full code" is completely wrong syntax and would not get any result.

These are all incorrect syntax (some were commented out, so maybe just examples):

sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = specs_name"
id = .e_set.ID = ("id")
total_len = .e_set.Total_Len = ("total_len")

So I don't know at what point you determine the query is not bringing back any value for ID, etc.
That's why I suggested running the query in your sql database tool, to see if it returns a result. If it does, then the problem points to the syntax of your code.

[DoronF]

Re: SQL statement help

Hello Shinra,

And you cannot use this kind of SQL statement and make it work as Steve pointed out, and use arguments.

I would add this kind of code to debug and see the results:
Remember to add your own fields before the ELSE..

IF Cn.Execute(SqlStr,Args)
	Session.PrintDoc = Cn.ResultSet.data("PrintDoc")
ELSE
	File.Append_Line("C:\Alpha Debug\ErrorLog.txt", "Document Err1: " + Cn.callResult.Text + " " + Now())	
END IF

Just create a folder where you can dump all your errors.

Regards,

Doron
The Farber Consulting Group, Inc.

Main Web Site: http://www.dFarber.com
Custom Software Development

[shinra]

Re: SQL statement help

Yes it was very confusing and after further investigation yesterday I found that I bound some information from the DB wrong.

sqlCommand = "select id, total_len from dbo.txd_specs where Specs_Name = '" + specs_name + "'"
sqlCommand1 = "select * from dbo.Txd_Specs_Mv where txd_specs_id = '" + txd_specs_id + "'"

There were the final SQL statements to make things work. Which I had at the beginning... Thanks for all the help on trying to figure this thing out.