Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

how to check the resultset contains records or not ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    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
    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

    Comment


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

      Thank you :)

      Comment


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

        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

        Comment


          #5
          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.

          Comment


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

            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--

            Comment


              #7
              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))

              Comment


                #8
                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.

                Comment


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

                  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"

                  Comment


                    #10
                    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.


                    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"

                    Comment


                      #11
                      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, 04:51 PM.
                      Steve Wood
                      See my profile on IADN

                      Comment


                        #12
                        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.

                        Comment


                          #13
                          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.

                          Comment


                            #14
                            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
                            [email protected]

                            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.

                            Comment


                              #15
                              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!

                              Comment

                              Working...
                              X