Alpha Video Training
Results 1 to 6 of 6

Thread: table.external_record_content_get ~ for sql

  1. #1
    VAR Dan Blank's Avatar
    Real Name
    Dan Blank
    Join Date
    Apr 2000
    Location
    Fort Worth, TX
    Posts
    995

    Default table.external_record_content_get ~ for sql

    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
    Dan

    Dan Blank builds Databases
    Skype: danblank

  2. #2
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,333

    Default Re: table.external_record_content_get ~ for sql

    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
    Alpha Anywhere latest pre-release

  3. #3
    VAR
    Real Name
    Mike Reed
    Join Date
    Apr 2000
    Location
    Phoenix, AZ
    Posts
    668

    Default Re: table.external_record_content_get ~ for sql

    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
    Mike Reed
    Phoenix, AZ

  4. #4
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,609

    Default Re: table.external_record_content_get ~ for sql

    Or, something like this:

    Code:
    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)
    -Steve


  5. #5
    "Certified" Alphaholic TheSmitchell's Avatar
    Real Name
    Sarah
    Join Date
    Apr 2012
    Posts
    1,333

    Default Re: table.external_record_content_get ~ for sql

    Quote Originally Posted by Mike Reed View Post
    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:
    Code:
    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.
    Last edited by TheSmitchell; 11-09-2013 at 02:40 PM.
    Alpha Anywhere latest pre-release

  6. #6
    VAR Dan Blank's Avatar
    Real Name
    Dan Blank
    Join Date
    Apr 2000
    Location
    Fort Worth, TX
    Posts
    995

    Default Re: table.external_record_content_get ~ for sql

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

    Thanks again!
    Dan
    Dan

    Dan Blank builds Databases
    Skype: danblank

Similar Threads

  1. Replies: 0
    Last Post: 08-15-2012, 02:31 PM
  2. Table.external_record_content_get()
    By netgeorger in forum Alpha Five Version 10 - Desktop Applications
    Replies: 6
    Last Post: 04-08-2011, 02:51 PM
  3. Table.external_record_content_get()
    By MikeC in forum Archived Wishlist
    Replies: 0
    Last Post: 02-10-2010, 10:55 AM
  4. bug with table.external_record_content_get()
    By Mike Wilson in forum Alpha Five Version 9 - Desktop Applications
    Replies: 5
    Last Post: 06-19-2008, 10:01 PM
  5. table.external_record_content_get
    By Blake in forum Alpha Five Version 5
    Replies: 5
    Last Post: 08-18-2003, 06:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •