Alpha Video Training
Results 1 to 7 of 7

Thread: Issue with SQL Result Set and Merging Data into a template

  1. #1
    Member
    Real Name
    Keith Wessel
    Join Date
    Apr 2016
    Posts
    47

    Default Issue with SQL Result Set and Merging Data into a template

    I'm working on putting some graphs on a webpage and I was following Selwyn's video examples. The problem I've run into is that when I do the a5_mergedataintotemplate and pass in the resultset from my query, the first result is being left out. I've confirmed that the data is there, it's just not being merged in. I tried a couple of different tests, one of which consisted of running a loop and checking the data output as text in the debugger. It looks like the merge is starting with the second row of data. Is anybody else experiencing this or have a workaround. I'm not able to set the current row in the result to the first and I don't see any other properties to force it to merge the first row of data.

    I'm on Alpha Anywhere v4.5.5.0 build 5667

    'open a connection
    dim cn as sql::Connection
    cn.open("::Name::IQMS-IQORA")

    'execute the previously specified sql query
    dim flag as l
    flag = cn.Execute(sql)

    'save the results from the qeury in a result set variable to read
    dim rs as sql::ResultSet
    rs = cn.ResultSet

    debug(1)
    dim test as c
    test = "Name" + rs.data(1) + " " + rs.data(2) 'check if first row exists in results
    while rs.NextRow() 'loop through results to see what comes up
    test = "Name" + rs.data(1) + " " + rs.data(2)
    end while

    'declare a template to the save the data from the sql query for JSON
    dim template as c
    template = "['{js_escape(ds.data("mfgcell"))}', {ds.data("avgofoee_pct")}],"

    'merge the reults from the result set into a text variable using the template format
    dim txt as c
    txt = a5_mergedataintotemplate(template, rs)

    'trim the trailing comma off the result set
    txt = rtrim(txt, ",")

    'format the first row of the results with header names
    txt = "[['Plant', 'Average OEE']," + txt + "]"


    dim js as c
    js = "dialog.object}._data = " + txt + ";"

    e.javascript = js
    Last edited by KWessel; 05-09-2019 at 11:52 AM. Reason: add comments to debug code

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

    Default Re: Issue with SQL Result Set and Merging Data into a template

    The problem is that this line implicitly fetches the first row. It has to in order to get data:

    test = "Name" + rs.data(1) + " " + rs.data(2) 'check if first row exists in results

    And then this line moves to the second row with .NextRow():

    while rs.NextRow() 'loop through results to see what comes up

    Try this construct instead:

    Code:
    flag = rs.nextRow()
    while flag
    	' do stuff
    	flag = rs.nextRow()
    end while
    -Steve


  3. #3
    Member
    Real Name
    Keith Wessel
    Join Date
    Apr 2016
    Posts
    47

    Default Re: Issue with SQL Result Set and Merging Data into a template

    I was only using that line to check that the first row of data was actually fetched, my original code didn't have the while loop or the line before it. My only guess is that the template merge is skipping the first row of data somehow and reading everything after that.

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

    Default Re: Issue with SQL Result Set and Merging Data into a template

    Then you can do this:
    Code:
    flag = rs.nextRow()
    if flag = .f.
       ' this means no data
    else
      while flag
    	' do stuff
    	flag = rs.nextRow()
      end while
    end if
    -Steve


  5. #5
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,537

    Default Re: Issue with SQL Result Set and Merging Data into a template

    I've tested your code using SQL Server and MySQL. If I remove any action with the resultset, before the merge, then all is ok. If you touch the resultset, if you access it, then stuff gets messed up.

    If you include this in your code...

    Code:
    test = "Name" + rs.data(1) + " " + rs.data(2) 'check if first row exists in results
    Then the resultset is now positioned the 2nd record.

    If you get the resultset and immediately do the merge, then all is ok.

  6. #6
    Member
    Real Name
    Keith Wessel
    Join Date
    Apr 2016
    Posts
    47

    Default Re: Issue with SQL Result Set and Merging Data into a template

    Appreciate your replies.

    I figured something out regarding this, and I'm not sure if it's specific to the build I'm using. In my code I was saving the result of my query in a sql::resultset then I was passing that result set, without doing anything else with it, to the a5_mergedataintotemplate function. It would skip the first record. I found that if I skip the sql::resultset assignment altogether and deirectly pass in my ocn.ResultSet, the first record now appears. I'm using an Oracle database, but it did the same thing with MS SQL Server. My current code is below.

    Code:
    debug(1)
    
    dim osql as c
    osql = <<%txt%
    SELECT MFGCELL,  Avg(OEE_PCT)  AS AvgOfOee_Pct 
    FROM v_hpc_realtime_lite 
    where MFGCELL like 'PLANT 1'
    GROUP BY MFGCELL
    %txt%
    
    'declare a template to the save the data from the sql query for JSON
    dim template as c
    template = "['{js_escape(ds.data(\"mfgcell\"))}', {ds.data(\"avgofoee_pct\")}],"
    
    'open a connection to IQMS
    dim ocn as sql::Connection
    ocn.open("::Name::IQMS-IQORA")
    
    'execute the previously specified sql query
    dim flag as l
    flag = ocn.Execute(osql)
    
    dim txt as c
    txt = a5_mergedataintotemplate(template, ocn.ResultSet)
    
    'save the results from the qeury in a result set variable to read
    'dim ors as sql::ResultSet
    'ors = ocn.ResultSet
    
    ocn.close()
    
    'trim the trailing comma off the result set
    txt = rtrim(txt, ",")
    
    'format the first row of the results with header names
    txt = "[['Plant', 'Average OEE']," + txt + "]"
    
    dim js as c
    js = "{dialog.object}._data = " + txt + ";"
    
    e.javascript = js
    You can see my commented out lines for the ors resultset. If I use that code instead, it will skip the first record when I pass that in to the merge function. So it seems like just assigning the result set is manipulating it to go past the first record. I tried manually assigning the row before the merge on the data set, but that didn't do it either. I guess I'll leave it this way since it seems it's working.

  7. #7
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,537

    Default Re: Issue with SQL Result Set and Merging Data into a template

    I've felt, for a couple of years, that working with a resultset produces different results under certain builds and circumstances... I've just never be able to duplicate it. Sometimes, the resultset is positioned on the first record while debugging in Live or Working Preview. When I run the same code on a server I get different results.

    Same in this scenario. Your code works for me and I get all records. You don't. But it could be because of build or something else. I'm usually so exhausted by these random fluctuations in dependability that once it works I just move on to the next issue.

    Glad it's working for you. You shouldn't have to go through this for such a basic operation.

Similar Threads

  1. Merging Data into a PDF or a Word Document - End Result is a PDF with Merged Data
    By cpgood in forum Application Server Version 11 - Web/Browser Applications
    Replies: 2
    Last Post: 01-17-2014, 09:13 AM
  2. Error expanding Grid Part template: Column not found while getting result set data co
    By RhinoSmith in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 12-09-2012, 10:04 AM
  3. Error expanding Grid Part template: Column not found while getting result set data co
    By leetv in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 12-14-2011, 06:46 PM
  4. Error expanding Grid Part template: Column not found while getting result set data co
    By Atta in forum Application Server Version 10 - Web/Browser Applications
    Replies: 1
    Last Post: 10-24-2010, 07:54 PM
  5. Merging data
    By Buddy Lednum in forum Alpha Four Version 6 and Prior
    Replies: 0
    Last Post: 05-10-2001, 11:28 AM

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
  •