sql_lookup()

[Mwhite]

Hi.

What would I use to lookup information in a A5 data base, the example i'm using only talks of sql_lookup.


sql_lookup(cn, table, filter, result)



Thanks,

[Steve Wood]

Re: sql_lookup()

Find lookup() and lookupc() in the wiki help

[Mwhite]

Re: sql_lookup()

Thanks for the reply Steve, this is what I used to get the information from a a5 table, not sure if its correct, am I on the correct path?


dim tbl as c
dim userid as c

tbl = table.actual_filename_get("employees")

type = "F"
value = "userid"
expression ="userid = 'JD'"
name = "roll"


?lookupc(type,value,expression,tbl,name)
ERROR: Variable "name" not found.

Thanks in advance,

Melvin

[omagarc]

Re: sql_lookup()

Output_String as C = LOOKUPC( Match_Type as C, Key_Value as A, Lookup_Expression as C, Lookup_Table as C, Index_Name as C )

Argument
Description

Match_Type
A 1-character string that specifies which lookup table record should supply the value if more than one record matches the Key_Value.

"F" = First

"L" = Last

"C" = Closest match.

Closest match uses the first occurrence of the closest inexact match if an exact match does not exist, otherwise it uses the first occurrence of an exact match.

Key_Value
The value of a key to search for. The type of Key_Value must be the same as the type of the keys in the index specified by Index_Name.

Lookup_Expression
The name of a field in the Lookup_Table or a valid expression which may contain one or more fields.

Lookup_Table
The fully qualified name of a table. If you omit the drive and path, Alpha Five searches the directory of the current database.

Index_Name
The name of an index in the index lookup table that may contain the Key_Value.

[mmaisterrena]

Re: sql_lookup()

I think the simplest way is with lookup you dont have to open table with xbasic just put:

value="JD"
LOOKUP("[PathAlias.ADB_Path]\employees.dbf","userid ="+ quote(value),"roll")


Always use [PathAlias.ADB_Path] for table paths

[Mwhite]

Re: sql_lookup()

Thanks for the reply

I am building a log in processing page and i need to check the employee table to see if the logging in user is active, and if, what roll the employee has to redirect to the correct pages.


I tried this code:

value="PAT"

LOOKUP("[PathAlias.ADB_Path]\employees.dbf","userid ="+ quote(value),"roll")

msgbox(roll)



Thanks in advance

Melvin

[mmaisterrena]

Re: sql_lookup()

the msgbox function does not works in web apps if this is an a5 w page you just ad question mark at the begin to the variable or string you wanto show like:

?roll

[Mwhite]

Re: sql_lookup()

Thanks again MM

I attached the code editor view of what i'm trying to do.

there is a employee2 table with the indexed field of userid.
the userid PAT exist in the table
her roll in the table is collector

this is the result:

dim filterstring as c

filterstring = "PAT"


LOOKUP("[PathAlias.ADB_Path]\employees2.dbf",filterstring,"roll")
error: lookup([PathAlias.ADB_Path]\employees2.dbf,PAT,ROLL) No such field


Thanks in advance

Melvin

[mmaisterrena]

Re: sql_lookup()

you must specify "Field = Value" in the filter string you are just specifying the value
supposing you want to filter by the userid field the code should be:


this is the result:

dim filterstring as c

filterstring = "PAT"


LOOKUP("[PathAlias.ADB_Path]\employees.dbf","userid ="+ quote(filterstring),"roll")

[Mwhite]

Re: sql_lookup()

Thanks again MM, this is how i want to use the lookup, am i on the correct track?

<%A5

dim pUser a p
dim vGUID as c
dim vULINK as c

a5ws_logged_in _user_values(pUser,request,session)
group = a5ws_get_users_Assigments(pUser,userid)
vGUID = a5ws_logged_in_user(pUser,userid)
vUlink = a5ws_get_ulink_from_guid(vGUID)


LOOKUP("[PathAlias.ADB_Path]\employees.dbf","userid ="+ quote(vULINK),"roll")

select
case "manager" $ roll
response.redirect("index.a5w")
case "collector" $ roll
response.redirect("index.a5w")
case "admin" $ roll
response.redirect("index.a5w")
case "posting" $ roll
response.redirect("index.a5w")
case else
response.redirect("TabbedUI_TUI_collection_screen.a5w")
end select

end if


end if

%>

[mmaisterrena]

Re: sql_lookup()

Roll = LOOKUP("[PathAlias.ADB_Path]\employees.dbf","userid ="+ quote(vULINK),"roll")