table.external_record_content_get ~ for sql

[Dan Blank]

Is there an similar command for "table.external_record_content_get()" in MySql with out using Active Link tables?

It works if I use Active Link tables, but I would like to get away from using Active Link tables.

So is there another command to use for MySql?

Thanks,
Dan

[TheSmitchell]

If you're wanting to get away from active link tables and address your SQL database directly, then you should check out this tutorial: http://wiki.alphasoftware.com/Learni...20SQL%20Tables

I think sql_records_get gets pretty darn close. Additionally, the SQL::Connection object has a variety of functions that will put your sql query results into a more manageable format, including JSON, property arrays, and strings. It can be a bit tedious to write out the SQL to query your table, but I find it easier to get exactly what I want executing my own queries over trying to use functions like sql_records_get and sql_lookup.

Hope that helps you out! Good luck. :)
---
Sarah

[Mike Reed]

Depends on what you are looking for.
If you are looking for a single value then you can use the built in 'sql_lookup' command
I have a table called district which, among other things has the district code and district name. So if I wanted the district name for a code I could do this:

?sql_lookup("::name::yourconnectionname","district","district='ho'","districtname")
= "Houston"

I wrote a small "table.external_record_content_get()" substitute for mysql that looks like this:

FUNCTION sql_table_content AS C (table AS C, field AS C, filter AS C )
dim cn as SQL::Connection
cn.open("::name::ourconnectionname")
dim qry as c
if filter = "" then
qry = "SELECT "+ field + " FROM " + table
else
qry = "SELECT "+ field + " FROM " + table + " WHERE " + filter
end if

cn_result = cn.Execute(qry)
sql_table_content = cn.ResultSet.ToString()
cn.Close()
END FUNCTION

So using this new function I can do this: (note the filter is blank is this example)
?sql_table_content("district","district, districtname","")
= AD Administration
AU Austin
DA Dallas
HO Houston
LV Las Vegas
PH Phoenix
QQ Test District

Another example (this one includes a filter and I included the pipe symbol to separate the columns)
?sql_table_content("svc_type","typename, ' | ', rpt_1099","billing=1")
= Admin | T
Cleaning | T
Drywall | T
Maintenance | T
Painting | T
Shampoo | T

The difference between the 2 commands is that the built in sql_look-up only gives the first value it finds and only the first column. The function I built gives all values that fit the filter and allows multiple columns.

Mike

[Steve Workings]

Or, something like this:

cn = "::name::conn"
table = "Facilities"
filter = "Comp_id = " + convert_type(session.comp_ID, "N")
result = "concat(Facility, '|', Fac_ID)"

List_Facilities_Co = sql_get_values(cn, table, filter, result)

[TheSmitchell]

The difference between the 2 commands is that the built in sql_look-up only gives the first value it finds and only the first column. The function I built gives all values that fit the filter and allows multiple columns.

sql_lookup(conn,table,filter,result_expression,arguments,flagReturnAllValues)

Not so. If you pass in .t. for flagReturnAllValues, you get more than one result. The catch is that you have to specify a filter that is not empty/null, otherwise you get an error. At least, I get an error. This is potentially something I should report to alpha maybe.

Additionally, you can specify multiple columns for the return_expression with commas. EG:

result = sql_lookup("::Name::conn","my_table","1=1","FIELD_NAME",0,.t.)

'Multi-column
resultMultiColumn = sql_lookup("::Name::conn","my_table","1=1","FIELD_1, FIELD_2, FIELD_3",0,.t.)

sql_lookup returns a crlf() delimited string of records if you get more than one value back. If you have multiple fields, they are separated by spaces.

[Dan Blank]

Thank you everyone! Some good ideas here. I appreciate the help.

Thanks again!
Dan