xbasic replacement of lookupc() statement for SQL backend

[Scholin]

For the past several months, I have been converting my existing v9 web application to use SQL as the backend with v10 on the front end. I am nearing completion with the finer details remaining. For the most part I have not encountered too many problems and loving the overall results.

However I do have a several a5w pages with xbasic code running some background activities using lookupc() functions at various points.

I am not clear on how best to replace the lookupc() in xbasic to access SQL tables to lookup an indexed value which can then be inserted into a variable for the rest of the existing xbasic code to run.

I am sure there is no direct replacement that I could simply "drop into" my lookupc() statements. So my question is how best to deal with this?

I have quite a few such statements throughout several different a5w pages. I know that active linked tables won't work either because they don�t support indexes used in the lookup() statement (and I prefer to not use active linked tables anyway, if possible).

I know that there are quite a few others looking to make this leap and hopefully the answer here will help them as well.

Any thoughts? Thanks!

ps: Be sure to check out the new v10 action javascript "lookup and fill-in", which is wonderful for looking up sql or dbf tables with a grid!

[Scholin]

Re: xbasic replacement of lookupc() statement for SQL backend

I thought I best give an example to be more specific:

Here is one example of a lookupc() being used after the log on page to capture a session variable based on the user:

session.__protected__clientid = lookupc("F",session.__protected__ulink,"client_id","C:\USMON\child_web_user","Id")

So if the "child_web_user" is now an SQL table, how would I capture the "client_id" to be used in the xbasic code?

[Pat Bremkamp]

Re: xbasic replacement of lookupc() statement for SQL backend

What SQL does best is to select records, so a similar thing I do is

 
if eval_valid("session.__protected__clid")=.f.
dim GSVSelect as c
dim gsv as SQL::connection
dim grs as SQL::ResultSet
if gsv.open("::name::egw") 'connect to database 
 GSVSelect="SELECT cl_id FROM employees WHERE ( em_id ="+session.__protected__ulink )
if gsv.Execute(GSVSelect)
  grs=gsv.ResultSet
  dim session.__protected__clid as n
  session.__protected__clid=grs.data("cl_id")
gsv.close()
end if
end if
end if

There may also be an SQL::Lookup command, but I haven't found it.

Pat

[Scholin]

Re: xbasic replacement of lookupc() statement for SQL backend

Pat,

Thank you.

So much to replace for just one function!

As it is with many things, Alpha has spoiled us with certain functions that just won't be as convenient to replace using SQL (and vise versa I am sure).

I will use this - thanks again.

[mmaisterrena]

Re: xbasic replacement of lookupc() statement for SQL backend

Lookup() function does work with active linked tables, dont know if lookupc() works too.

[Scholin]

Re: xbasic replacement of lookupc() statement for SQL backend

Mauricio,

You are correct - lookup() did worked well when I created an active link. Because I have like 15 session variables to load upon login, I decided to go with your method due to the ease of coding (for a non-coder).

However, I did heard somewhere that with the web and SQL we should avoid active link tables if possible as it is an extra process (nothing is free). So I am hoping that I don't take a noticeable performance hit - but then again it is just one small table so we will see.

Thanks!

[mmaisterrena]

Re: xbasic replacement of lookupc() statement for SQL backend

Yes you'r right nothing is for free

But by programing in xbasic you'r allready adding some extra steps because as far as I know xbasic gets converted to native c++ but as computing power gets better this "extra steps" are less noticeable

it would be great if like Pat said an 'SQL::Lookup' function actualy existed