Is there a better way to assign session variables ?

[madtowng]

There are times that I think I've been using xbasic too long.
The code below finds a specific sponsor, then populates the session variables,
but I think there is a better (more graceful) way to do it.

Any help would be appreciated.

Gregg

dim cn as sql::Connection
dim sqlcode as c
dim cn2 as sql::Connection
dim sqlfilter as sql::arguments
flag = cn.open("::Name::mysystem")
table = "sponsors"

filter = "url = :whaturl"
sqlfilter.set("whaturl",validate)

result_expression = "sponsor"
session.sponsor = sql_lookup(cn,table,filter,result_expression,sqlfilter)

result_expression = "email_address"
session.emailfrom = sql_lookup(cn,table,filter,result_expression,sqlfilter)

result_expression = "initial_role"
session.role = sql_lookup(cn,table,filter,result_expression,sqlfilter)

result_expression = "name"
session.sponsorname = sql_lookup(cn,table,filter,result_expression,sqlfilter)

result_expression = "phone01"
session.sponsorphone = sql_lookup(cn,table,filter,result_expression,sqlfilter)

result_expression = "phone02"
session.sponsorph2 = sql_lookup(cn,table,filter,result_expression,sqlfilter)

[Davidk]

Re: Is there a better way to assign session variables ?

Because you're using the SQL_Lookup function, you are opening and closing a connection as many times as you're calling it. Open the connection and establish a recordset and then assign your variable from that one recordset... therefore just one open and close.

dim conn as SQL::Connection
dim args as SQL::Arguments 'if you want to use Arguments
dim rs as SQL::ResultSet
dim sqlStmt as c

'Build your SQL statement
sqlStmt = "SELECT * FROM Sponsors WHERE " + filter

if conn.Open("::Name::YOURCONNECTION","USERNAME","USERPASSWORD") then
	 conn.PortableSQLEnabled = .f.
	 if conn.execute(sqlStmt) then
	 	rs = conn.ResultSet
               conn.close()
     else
         'could not execute redrirect to error
       	  yourfunctionname = "alert('Execution Problem');"
          conn.close()
          end
     end if
else
     yourfunctionname = "alert('Connection Problem');"
     end
end if

'Now use your recordset to set session vars.

session.sponsor =  rs.data("Sponsor")

If Sponsors is a table with a large amount of fields then include just those fields you need instead of *

[madtowng]

Re: Is there a better way to assign session variables ?

Thank you David.
I had a feeling my solution was less than efficient, although it was better than
what I previously had.

[bobalston]

Re: Is there a better way to assign session variables ?

Thanks very much. One question: Is the text

"::Name::YOURCONNECTION"

entered literally or are these placeholders. If so, how are they set?

Bob

[madtowng]

Re: Is there a better way to assign session variables ?

Hi Bob,

The "YOURCONNECTION" part is completely your choice.
The image attached is quite the exaggeration, but I think
it shows everything. Look at untitled.png first, then capture.png.
Hope this helps.

[Davidk]

Re: Is there a better way to assign session variables ?

You could try this as well... get the connection string already defined by the grid.

'get the connection string that is defined in this Grid component
dim cs as c 
cs = e.tmpl.cs.connectionString

[madtowng]

Re: Is there a better way to assign session variables ?

I'm confused.
This part works,
? cn.Execute("Select * from sponsors where url = 'tmev11.int-wilogistics.com'")
= .T.

But this fails,
gwstable = "sponsors"
cn.PortableSQLEnabled=.f.

validate = "tmev11.int-wilogistics.com"
sqlfilter.Set("sponsor",validate)
sqlcode = "Select * from "+gwstable+ " where url = :sponsor"
?sqlcode
= "Select * from sponsors where url = :sponsor"
?cn.Execute(sqlcode)
= .F.

[Pat Bremkamp]

Re: Is there a better way to assign session variables ?

try cn.execute(sqlcode,args) (if that's the name you defined in you args=SQL::Arguments statement.)

[madtowng]

Re: Is there a better way to assign session variables ?

HOT Diggity Pat, that worked!!!!

Thank you

[madtowng]

Re: Is there a better way to assign session variables ?

For the benefit of others, I personally seem to have a problem with the term args instead of filter, which is why I use

dim sqlfilter as sql::arguments

The line that worked for me, with is :

cn.Execute(sqlcode,sqlfilter)

I am doing this for people like me that have been using .dbf files for years, and are trying
to make the conversion to using a SQL backend.

[madtowng]

Re: Is there a better way to assign session variables ?

Slightly diffferent, but still related.

I use this code to find out how many records are returned by the query.

sqlcode = "Select count(*) from "+sqltable+ " where guid = :sponsor"
cn.Execute(sqlcode,sqlfilter)
sqlresults = cn.ResultSet

Assuming I get the number I'm looking for, I use the following code to rerun the query

sqlcode = "Select * from "+sqltable+ " where guid = :sponsor"
cn.Execute(sqlcode,sqlfilter)
sqlresults = cn.ResultSet

But I don't get results from the 2nd query.

[madtowng]

Re: Is there a better way to assign session variables ?

I missed a line.

dim sqlresults as sql::ResultSet

.

That one missing line seems to make quite a difference.

Fortunately, Alphasoftware/Selwyn does have a series of videos for this stuff at http://www.downloads.alphasoftware.c...sqltables.html.

[bobalston]

Re: Is there a better way to assign session variables ?

For anyone else struggling with this syntax, as I was (see above), I found this to be very helpful.

http://www.downloads.alphasoftware.c...SQLTables.html

Bob