Alpha Video Training
Results 1 to 12 of 12

Thread: sql query

  1. #1
    Member
    Real Name
    Jeff Emery
    Join Date
    May 2001
    Posts
    70

    Default sql query

    Has anyone figured out how to query an SQL database via ODBC with Alpha 5? Thanks in advance for any help or suggestions.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: sql query

    We do copy from ODBC linked tables (Oracle), append from ditto, can browse an ODBC table and perform query by table, query genie works, etc.

    Not understanding "SQL Database". SQL Server is an ODBC driver. You need to know the native type of the tables to be accessed.

    Are you having trouble linking to the ODBC tables? Querying once linked?

  3. #3
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: sql query

    Jeff and Stan,

    Stan, SQL database makes perfect sense (MS SQL I presume). But, Jeff, I tried and failed and gave up when I figured out (possibly erroneously) that it ain't possible. Maybe if Alpha supported SQL queries, but otherwise I don't think so. A5 ODBC with SQL just doesn't seem to give much in the way of power.

    But remember, I gave it up, so don't try to take this to the bank.

    Ray Lyons

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: sql query

    If I understand what is "supposed" to happen, and that's not all that often anymore, it is that the .ini file you use to link to the ODBC table performs translations of Alpha query syntax into the appropriate SQL syntax.

    If you look at the mssqlsrv.ini you find:

    AppendAll = INSERT INTO %s ( %s ) %s SELECT %s FROM %s

    I'm no rocket scientist, not even much of a programmer, but the second part looks like SQL to me.

    So if you need to tinker with Alpha's capabilities, wouldn't you modify this .ini? The header for the .ini file calls it a generic driver, suggesting that it could be tailored to more specific needs.

    I know such modification is beyond me. Who's our SQL expert out there?

  5. #5
    Member
    Real Name
    Jeff Emery
    Join Date
    May 2001
    Posts
    70

    Default RE: sql query

    Ray and Stan -

    Thank you for your responses. Ray, you are correct, the SQL Database is MS SQL running on a Win 2000 Server. Our link thru ODBC "works". Here is the interesting thing. When I give a query a specific part # to find, the query works. When I give the query a variable, it does not. For example:

    Let's say I want to find our Product Code A111 from SQL table dbo_stone_product.

    This works:

    tbl = table.open("dbo_stone_product")
    query.description = "SQL Query"
    query.filter = "stone_id = 'A111'"
    query.options = ""
    indx = tbl.query_create()

    This fails:

    DIM SHARED prod AS C
    prod = "A111"
    tbl = table.open("dbo_stone_product")
    query.description = "SQL Query"
    query.filter = "stone_id = var->prod"
    query.options = ""
    indx = tbl.query_create()

    Variables seem to throw SQL into a tizzy and I have not been able to find a way around it. So how does one accept a product code from, say, a ui_get_text and find the product from the SQL database?

    Jeff

  6. #6
    Member
    Real Name
    Jeff Emery
    Join Date
    May 2001
    Posts
    70

    Default RE: sql query

    Stan -

    You are correct that the "AppendAll" is SQL syntax. I used SQL many years ago in a UNIX environment, so I'm going to have to dig out my old manuals and refresh my SQL knowledge. I'm also going to take a look at mssqlserv.ini, which I haven't done yet.

    Thanks for the pointer.

    Jeff

  7. #7
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default RE: sql query

    Jeff,

    You need to concatonate text strings to build your filter. Modifying your second example, it would be;


    DIM SHARED prod AS C
    prod = "A111"
    tbl = table.open("dbo_stone_product")
    query.description = "SQL Query"
    query.filter = "stone_id = '"+var->prod+"'"
    query.options = ""
    indx = tbl.query_create()

    The above yields a query.filter equal to

    "stone_id = 'A111'"

    Don't try to use Alpha constructs in SQL is the general suggestion. Instead convert them to something that SQL will accept.

    Regards,

    Ira J. Perlow
    Computert Systems Design & Associates
    csda@mediaone.net

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: sql query

    Thanks,

    I needed that!

  9. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: sql query

    Actually I need more than that.

    I use the following filter in a saved copy operation against an Oracle table. (manually editing the "Wk_num" each week.

    Sku_Num0.or.Tot_Cst0.or.Tot_Qty0).and.(Yr=2001.and.between(Wk_Num,14,18))

    I'd like to create global variables for the form containing the button used to run the operation, use the variables in the operation, but I get an Oracle, missing expression when I try...

    Sku_Num0.or.Tot_Cst0.or.Tot_Qty0).and.(Yr=2001.and.between(Wk_Num,var->beg_wk,var->end_wk))

    where beg_wk and end_wk are global form variables.

    Any thoughts?

    Thanks in advance.

  10. #10
    Member
    Real Name
    Jeff Emery
    Join Date
    May 2001
    Posts
    70

    Default RE: sql query

    Ira -

    Thank you, thank you, thank you. That did the trick!

    Jeff

  11. #11
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default RE: sql query

    Stan Mathews wrote:

    This isn't rocket science, but here it is,

    query.filter="Sku_Num0.or.Tot_Cst0.or.Tot_Qty0).and.(Yr=2001.and.between(Wk_Num,"+LTRIM(STR(var->beg_wk))+","+LTRIM(STR(var->end_wk))+"))"

    Basically, just concatonating strings that look like constants in the expression string. This is a really important concept that is used throughout A5, so try to understand what it is I'm doing.

    Regards,

    Ira J. Perlow
    Computert Systems Design & Associates
    csda@mediaone.net

  12. #12
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: sql query

    Ira,

    As always I appreciate your help. I understand the concept in general. Applying it specifically is not always as clear.

    Also I was trying to use an expression in the filter builder for a saved operation. This may or may not be possible. I've converted the saved operation to xbasic via the script recorder and it's runnning now -- no Oracle errors.

    Now it has completed and the results are as desired...

    Thanks again.

    Stan

Similar Threads

  1. Need query help
    By carterweslock in forum Alpha Five Version 5
    Replies: 8
    Last Post: 12-15-2003, 04:23 PM
  2. Blank Dates in a Query Within a Query
    By Raymond Lyons in forum Alpha Five Version 4
    Replies: 1
    Last Post: 10-19-2000, 04:03 PM
  3. Query on a query - SOLVED
    By Bill Warner in forum Alpha Five Version 4
    Replies: 0
    Last Post: 10-19-2000, 08:06 AM
  4. More on Query
    By nick marodis in forum Alpha Five Version 4
    Replies: 3
    Last Post: 09-29-2000, 06:21 AM
  5. Query.run() never returns accessible query
    By csda1 in forum Alpha Five Version 4
    Replies: 14
    Last Post: 08-10-2000, 12:34 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
  •