Alpha DevCon 2018
Results 1 to 15 of 15

Thread: how to check the resultset contains records or not ?

  1. #1
    Member
    Real Name
    Robert Brown
    Join Date
    Mar 2008
    Location
    Sydney
    Posts
    53

    Default how to check the resultset contains records or not ?

    Hi All

    In .NET after executing the SQL query we use rs.recordcount()>0 condition to check whether the resultset is empty or not.So In ALPHA FIVE how we can check whether the resultset contains records or not.

    For example

    t.execute("select * from empmaster where empno='007'")
    rs=t.resultset

    'now i have to perform certain operation if the above query returns value and have to do some other set of operation if the above query doesn't returns any value.


    Hope my question is clear

    Thank for any help
    --Robert--

  2. #2
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,162

    Default Re: how to check the resultset contains records or not ?

    There is a "RowCount" value for the result set, but it may be unreliable as not all platforms will return a value. However, when a result set is returned in AlphaDAO, the row pointer is not set. The result set function "NextRow()" will attempt to move the cursor to the next row, which initially is the first row row. If there is no first row, it returns false. If the result set has any values, it will always return true the first time it is run.

    This will always work to check for any returned values. A typical code snippet would be as follows.
    Code:
    dim cn as sql::connection
    dim FlagResult as l
    dim SelectStatement as c 
    SelectStatement ="SELECT * FROM Customers"
    FlagResult = cn.open("::name::nw")
    if FlagResult = .T.
    	cn.PortableSQLEnabled = .t. 
    	flagResult = cn.Execute(SelectStatement)
    	if flagResult = .T.
    		dim rs as sql::resultset
    		rs = cn.ResultSet
    		if rs.NextRow() = .T 
    			' something was returned, so process the result set 
    		else 
    			' nothing was returned, so do something else 
    		end if 
    	end if 
    	cn.close()
    end if

  3. #3
    Member
    Real Name
    Robert Brown
    Join Date
    Mar 2008
    Location
    Sydney
    Posts
    53

    Default Re: how to check the resultset contains records or not ?

    Thank you :)

  4. #4
    Member DOntquIT's Avatar
    Real Name
    Revolutionist-Jini
    Join Date
    Jun 2008
    Posts
    87

    Default Re: how to check the resultset contains records or not ?

    Quote Originally Posted by JerryBrightbill View Post
    if FlagResult = .T.
    cn.PortableSQLEnabled = .t.
    flagResult = cn.Execute(SelectStatement)
    if flagResult = .T.
    dim rs as sql::resultset
    rs = cn.ResultSet
    if rs.NextRow() = .T
    ' something was returned, so process the result set
    else
    ' nothing was returned, so do something else
    end if
    end if
    cn.close()
    end if
    [/CODE]
    Hi Jerry,
    Im also facing the same problem like robert.After readin this post i quickly replaced my codes with rs.nextrow() , i found one draw back here.
    lets say my sql query returns 10 records as my resultset.
    after i check that whether record existing or not using rs.nextrow() function.Then when i perform While loop it loop through 9 records only since cursor already pointing through 2nd row.so i have to replace while rs.nextrow() with some alternate codings.
    Thanks & Regards,
    RR


    Wonder rather than doubt is the root of all knowledge

  5. #5
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,162

    Default Re: how to check the resultset contains records or not ?

    The first time NextRow is run, you are left on the first record, so you can process the first row. Then you can use a while loop using NextRow() to process the rest of the rows after you process the first row.

    Depending on what you want to do with the values, there are alternative ways to get data from a result set if you have a number of rows. Typically, you want to populate a list or array. You can use rs.ToString() to place the values as character values in a crlf() delimited list, or you can use rs.ToPropertyArray() to populate an array. Unlike most array functions, you don't need to know how many records exist as the function will build an array of the correct size.

  6. #6
    Member
    Real Name
    Robert Brown
    Join Date
    Mar 2008
    Location
    Sydney
    Posts
    53

    Default Re: how to check the resultset contains records or not ?

    Quote Originally Posted by JerryBrightbill View Post
    The first time NextRow is run, you are left on the first record, so you can process the first row. Then you can use a while loop using NextRow() to process the rest of the rows after you process the first row.

    Depending on what you want to do with the values, there are alternative ways to get data from a result set if you have a number of rows. Typically, you want to populate a list or array. You can use rs.ToString() to place the values as character values in a crlf() delimited list, or you can use rs.ToPropertyArray() to populate an array. Unlike most array functions, you don't need to know how many records exist as the function will build an array of the correct size.
    I tried this way as per your sugession but still when we use rs.nextrow()=.t. the pointer is moving to second record. No i cannot access the first record. All Alpha five Programmers in my company are in search of solution for the problem mentioned above.


    Thanks
    Robert--

  7. #7
    Alpha Software Employee Selwyn Rabins's Avatar
    Real Name
    Selwyn Rabins
    Join Date
    Mar 2000
    Location
    Boston, MA
    Posts
    5,237

    Default Re: how to check the resultset contains records or not ?

    the following script shows how you can loop over all of the records in a resultset without skipping the first record.


    Code:
    dim cn as sql::Connection
    cn.open("::Name::sqlserver2012_northwind")
    sql = "select customerId from customers where country = 'mexico'"
    
    dim flag as l 
    flag = cn.Execute(sql)
    rs = cn.ResultSet
    
    
    txt = rs.tostring()
    
    showvar(txt,"" + line_count(txt))
    
    
    flag = cn.Execute(sql)
    rs = cn.ResultSet
    
    'if there are no records, then flag will be .f. and the loop will not execute
    flag = rs.nextRow()
    
    txt = ""
    while flag 
    	txt = txt + rs.data(1) + crlf()
    	flag = rs.nextRow()
    end while 
    
    showvar(txt,"" + line_count(txt))

  8. #8
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Re: how to check the resultset contains records or not ?

    I found that conn.resultset.CurrentRowIndex can be used to determine whether or not conn.Execute() found any rows. If not, CurrentRowIndex=0, if yes, CurrentRowIndex=1 right after the query is run. I have tested this on SQL Server 2008, and it works great every time.

    I must say, though, that the documentation when it comes to this is misleading at best. The example given in the documentation shows how you can (supposedly) list returned row values using rs.NextRow(), but if you follow this example you always skip the first row! Responses by Alpha Software engineers to rs.NextRow() -related questions here on the forum claim that rs.NextRow() will find the first row in the resultset when it is run for the first time, but this is simply not true. Selwyn FINALLY clarified this in his above post, but the documentation remains wrong. I don't know, maybe SQL Server 2008 resultsets are somehow different when it comes to NextRow() behavior, but I kind of doubt it. Do the people writing A5 documentation bother to check whether or not the examples they give actually work? It took me a while scratching my head with this one until I finally realize that I've been mislead by the A5 documentation.

  9. #9
    Alpha Software Employee Selwyn Rabins's Avatar
    Real Name
    Selwyn Rabins
    Join Date
    Mar 2000
    Location
    Boston, MA
    Posts
    5,237

    Default Re: how to check the resultset contains records or not ?

    Quote Originally Posted by nlights View Post
    I found that conn.resultset.CurrentRowIndex can be used to determine whether or not conn.Execute() found any rows. If not, CurrentRowIndex=0, if yes, CurrentRowIndex=1 right after the query is run. I have tested this on SQL Server 2008, and it works great every time.

    I must say, though, that the documentation when it comes to this is misleading at best. The example given in the documentation shows how you can (supposedly) list returned row values using rs.NextRow(), but if you follow this example you always skip the first row! Responses by Alpha Software engineers to rs.NextRow() -related questions here on the forum claim that rs.NextRow() will find the first row in the resultset when it is run for the first time, but this is simply not true. Selwyn FINALLY clarified this in his above post, but the documentation remains wrong. I don't know, maybe SQL Server 2008 resultsets are somehow different when it comes to NextRow() behavior, but I kind of doubt it. Do the people writing A5 documentation bother to check whether or not the examples they give actually work? It took me a while scratching my head with this one until I finally realize that I've been mislead by the A5 documentation.

    conn.resultset.CurrentRowIndex IS NOT RELIABLE AND SHOULD NOT BE RELIED ON TO TEST IF A RESULTSET HAS DATA.

    the value in this property will depend on the type of sql database you are connected to. for some backends, it might be populated, and for others it might not be.

    the only way to check reliably if a result set has data is to call the .nextRow() method.


    for example


    Code:
    dim cn as sql::connection 
    flag = cn.open("::Name::sqlserver2012_northwind")
    flag = cn.Execute("select * from customers where country = 'xx'")
    ?flag 
    = .T.
    dim rs as sql::ResultSet
    rs = cn.ResultSet
    ?rs.nextrow()
    = .F.

    .nextRow() does NOT 'consume' the first row of data as you might think. so the following pattern will reliably return all of the data in the resultset


    Code:
    delete cn 
    dim cn as sql::connection 
    flag = cn.open("::Name::sqlserver2012_northwind")
    flag = cn.Execute("select * from customers where country = 'france'")
    dim rs as sql::ResultSet
    rs = cn.ResultSet
    
    flag = rs.nextrow()
    
    dim cities as c 
    cities = ""
    while flag 
    	cities = cities +rs.data("city") + crlf()
    	flag = rs.nextrow()
    end while
    for a complete description of how to use sql tables with xbasic i suggest you read the excellent tutorial on the subject.
    open the documentation viewer, and search for "Learning Xbasic - Using Xbasic with SQL Tables"

  10. #10
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Re: how to check the resultset contains records or not ?

    I saw that warning about CurrentRowIndex in the documentation, but since my SQL Server 2008 seems to reliably return the appropriate value, I figured I could use this, given that I use SQL Server 2008 exclusively for my data.

    In my testing (again with SQL Server 2008) I see that the .nextRow() WILL skip the first result row every time.

    If I do something like this:

    Code:
    qry = "select EI_ID FROM EVENTITEMS WHERE EI_ID = 2000"  'this PK ID exists in the table
    conn.Execute(qry)
    rs = conn.ResultSet
    ' now ?rs.data("EI_ID") would show 2000
    success = rs.nextrow()
    ' now ?success would show .F., meaning that nextrow() actually DID skip the first result row
    Maybe it is a SQL Server 2008 -specific thing, although that would be quite surprising to me...

    The further problem here is that if the query doesn't return anything, testing for rs.data("EI_ID") before testing for .nextrow() will error out. So there is no real good way to simply see if a query returned anything. Hence the (desperate) grab for CurrentRowIndex.


    Quote Originally Posted by Selwyn Rabins View Post
    conn.resultset.CurrentRowIndex IS NOT RELIABLE AND SHOULD NOT BE RELIED ON TO TEST IF A RESULTSET HAS DATA.

    the value in this property will depend on the type of sql database you are connected to. for some backends, it might be populated, and for others it might not be.

    the only way to check reliably if a result set has data is to call the .nextRow() method.


    for example


    Code:
    dim cn as sql::connection 
    flag = cn.open("::Name::sqlserver2012_northwind")
    flag = cn.Execute("select * from customers where country = 'xx'")
    ?flag 
    = .T.
    dim rs as sql::ResultSet
    rs = cn.ResultSet
    ?rs.nextrow()
    = .F.

    .nextRow() does NOT 'consume' the first row of data as you might think. so the following pattern will reliably return all of the data in the resultset


    Code:
    delete cn 
    dim cn as sql::connection 
    flag = cn.open("::Name::sqlserver2012_northwind")
    flag = cn.Execute("select * from customers where country = 'france'")
    dim rs as sql::ResultSet
    rs = cn.ResultSet
    
    flag = rs.nextrow()
    
    dim cities as c 
    cities = ""
    while flag 
    	cities = cities +rs.data("city") + crlf()
    	flag = rs.nextrow()
    end while
    for a complete description of how to use sql tables with xbasic i suggest you read the excellent tutorial on the subject.
    open the documentation viewer, and search for "Learning Xbasic - Using Xbasic with SQL Tables"

  11. #11
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,787

    Default Re: how to check the resultset contains records or not ?

    Selwyn,

    It seems whether rs.nextrow() "consumes" the first record or not, depends on how you use the result set after you first fire rs.nextrow(). See the two scripts below (client_id's are 10000, 10001, 10002, etc.)

    This script is the same as your example and it does show that the first use of rs.nextrow() leaves the cursor on record 1.
    dim cn as sql::Connection
    dim rs as sql::ResultSet
    cn.open("::name::conn")
    vSql = "SELECT * FROM client"
    cn.execute(vSql)
    rs = cn.resultset
    flag = rs.nextrow()
    ?rs.data("client_id")
    = 10000
    dim client as c = ""
    while flag
    client = client + rs.data("client_id") + crlf()
    flag = rs.nextrow()
    end while
    ?client
    = 10000
    10001
    10002
    But this script, which does not use a while loop, shows that the first time rs.nextrow() is fired the cursor is on the SECOND record.
    dim cn as sql::Connection
    dim rs as sql::ResultSet
    cn.open("::name::conn")
    vSql = "SELECT * FROM client"
    cn.execute(vSql)
    rs = cn.resultset
    ?rs.data("client_id")
    = 10000
    rs.nextrow()
    ?rs.data("client_id")
    = 10001
    Note: it did not matter if I used "flag=rs.nextrow()" or just "rs.nextrow()" wherever either were used.


    Edit: Adding this script below. I note that this script does leave the cursor on record 1 after rs.nextrow() is fired, and that f1 below contains the full resultset, starting with record 1. This makes it seem like my script 2 on this page is an exception to the rule, or I am doing something wrong. Clearly scripts 1 and 3 leave the cursor on record 1, while script 2 leaves the cursor on record 2.

    delete rs
    dim cn as sql::Connection
    dim rs as sql::ResultSet
    cn.open("::name::conn")
    vSql = "SELECT * FROM client"
    cn.execute(vSql)
    rs = cn.ResultSet
    rs.nextrow()
    f1 = rs.tostring()
    ?f1
    = 10000
    10001
    10002
    Last edited by Steve Wood; 02-17-2013 at 03:51 PM.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  12. #12
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Re: how to check the resultset contains records or not ?

    Interesting... Maybe my situation is a bit unique: I was simply trying to find out if a query returned 0 or 1+ rows. So, first I executed the query. At that point I was wondering if anything was returned. Testing the zero return case, I queried for a PK value that doesn't exist in the table. I couldn't evaluate rs.data('EI_ID') because since there are no results that evaluation throws an error (I suppose I could find out if rs.data("EI_ID") exists, and base my decision on that -- if not, there were no result rows, if yes, there was at least one.) I can't use nextrow() because that doesn't really tell me if I found just one record. Apparently, I shouldn't rely on the CurrentRowIndex either, at least if I want to keep my code backend-agnostic, which is generally a good idea.

    dBase/Foxpro has the system variable _TALLY that contains the number of rows returned by the most recent query, and it is a breeze to work with (takes less than a minute to understand and deploy.) Is there a variable I can use in the resultset that reliably tells me the number of rows in that set? I couldn't find any.

    Thanks.

  13. #13
    Member nlights's Avatar
    Real Name
    pertti karjalainen
    Join Date
    May 2011
    Location
    California
    Posts
    217

    Default Re: how to check the resultset contains records or not ?

    OK, finally figured it out, and thought I would share the love with those who care

    As it turns out, .resultset.CurrentRowIndex is highly unreliable indeed. Sometimes it works, sometimes not. It seems that in ajaxCallBack launched from some events, such as onMouseOver, .resultset.CurrentRowIndex may or may not be accurate. What makes things even harder is that it is always accurate if the debugger is invoked, but if the debugger doesn't get focus until .resultset.CurrentRowIndex is evaluated, all bets are off. The same goes for .resultset.CallResult.Code -- if debugger is invoked, the code shoes 103 (= set has no rows) on an empty set and 0 (=success - set has row(s)) on a non-empty one. If the debugger is not invoked, however, both empty and non-empty sets produce code 0. Talk about aggravating discrepancies and difficult debugging!

    The most reliable way (at least that I've found) to determine whether or not there are 0 or 1+ result rows is by examining .resultset.data(1). If NO results were returned, .resultset.data(1) is unknown/not present. Using EVAL_VALID("conn.resultset.data(1)") will figure out reliably whether or not the query returned any results -- with or without debugger invokation. The code looks something like this:
    Code:
    	
    	qry = "select * from EventItems where EI_ID = "+vcEI_ID
            vFound = .F.
    
    	if conn.Execute(qry)
    	   vFound = EVAL_VALID("conn.resultset.data(1)")   'Returns .F. if query returned nothing, .T. if it returned at least one row
           end if
    ...
    ...
    ...
    This also bypasses one potential problem with some of the above code suggestions: If the query doesn't return anything, the statement conn.resultset.data("ID") will definitely crash the program because of the implicit assumption that there will always be at least one row in the resultset. Yeah, it's the outliers that get you almost every time...

    Using this hard earned knowledge I have now created a simple global getTally(vrsResultSet) -function that returns the number of rows in the resultset, including zero rows. It sort of mimics the very useful dBase/FoxPro _TALLY -global variable.

  14. #14
    "Certified" Alphaholic
    Real Name
    Scott
    Join Date
    Mar 2010
    Location
    Toronto,ON
    Posts
    1,030

    Default Re: how to check the resultset contains records or not ?

    If you need it before records process, execute a SELECT COUNT(*) (and the rest of hte sql statement)

    If you need the record count after you process records, just do this:
    Code:
    dim records_found as N=0
    while conn.ResultSet.NextRow()
           'do some processing
            records_found=records_found+1
    end while
    
    
    if records_found==0 then
        ?"NO RECORDS WERE FOUND"
    end if
    Scott Moniz - Computer Programmer/Analyst
    REA Inc.
    http://reainc.net
    (416)-533-3777
    scott@reainc.net

    REA INC offers consulting services, programming services, systems design, database design, third party payment gateway integration (CHASE, PAYPAL, AUTHORIZE.NET) and developer support.
    If you need custom code, or 1-to-1 mentoring in any facet of your database/web application design,
    contact us to discuss options.

  15. #15
    Member
    Real Name
    Morgan Cohen
    Join Date
    Nov 2009
    Location
    Switzerland
    Posts
    29

    Default Re: how to check the resultset contains records or not ?

    I wasted a huge amount of time trying to solve this issue, including wondering why it only worked with debug activated Then I came across your solution nlights - many thanks!

Similar Threads

  1. select multiple records with check boxes?
    By Peter.Greulich in forum Web Application Server v7
    Replies: 14
    Last Post: 08-08-2016, 05:45 PM
  2. Select and Delete Multiple records with a check box
    By robert brown in forum Application Server Version 8
    Replies: 13
    Last Post: 08-11-2008, 05:40 AM
  3. Check to see if records in an embedded browse
    By enstorms in forum Alpha Five Version 8
    Replies: 7
    Last Post: 04-20-2008, 07:44 AM
  4. using sql resultset
    By ravish in forum Application Server Version 8
    Replies: 0
    Last Post: 01-14-2008, 10:23 PM
  5. Lifting records from a browse using check boxs
    By andycaps in forum Alpha Five Version 6
    Replies: 4
    Last Post: 10-20-2005, 10:13 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
  •