RowSet Returning Only a Single Row

  1. last week

    Matthew M

    Dec 2 Pre-Release Testers, Xojo Pro

    I keep my hair cut short so that I don't pull it out in patches. This issue would do it to me.

    I am building a very simple quick and dirty (so I thought) web app using API 2.0 to demonstrate to my client what they could do with an upgrade to a VB6 program that I wrote for them 15 years ago. My only prior web app was under API 1.0 and didn't return multiple rows of data.

    The web page has a text box, a button, and a list box with one column. User enters a keyword, clicks button, and all results go into the list box. The backend database is MSSQL Express 2016. The test query should return 10 rows. The code:

    ' Show search results
    
    dim strQuery As String
    dim strInput As String
    
    dim rsDetails As RowSet
    
    
    ' First get the info
    if (cnMSSQL0 <> Nil) then
      strInput= txtSearchTerms.Text.Trim
      
      Try
        strQuery = "SELECT TOP(10) Detail FROM tDigitalCatalog WHERE PrimarySub = ?"
        rsDetails = cnMSSQL0.SelectSQL(strQuery, strInput)
        
        lstResults.DeleteAllRows
        lstResults.ColumnCount = rsDetails.ColumnCount
        
        while (not rsDetails.AfterLastRow)
          lstResults.AddRow(rsDetails.Column("Detail").StringValue.Trim)
          rsDetails.MoveToNextRow
        wend
        
        rsDetails.Close
        
      catch error as DatabaseException
        MessageBox("Error: " + error.Message)
      end
       
    end
    
    Return

    I have based my code on examples in the documentation, and I have used the prepared statement to directly query the database (where it returns 10 rows). When I step through the code I see it enter the While loop, add one row to the list box, move to the next row, and exit the loop. I get the same behavior if I use the RowSet class.

    I am fully convinced that this issue is a direct result of my ignorance (ie: I've overlooked some small thing), but I am at a loss to identify the culprit. I should also mention that I used the same exact code in a desktop app and got the same exact behavior.

  2. Matthew M

    Dec 2 Pre-Release Testers, Xojo Pro

    An update.

    When I opened the project in 2019r1.1 and changed to RecordSet, EOF, and SQSelect (API 1.x) it works as planned.

    Matthew

  3. Jay M

    Dec 2 Pre-Release Testers, Xojo Pro NC, USA

    @Matthew M When I opened the project in 2019r1.1 and changed to RecordSet, EOF, and SQSelect (API 1.x) it works as planned.

    You can use/run API 1 code in 2019r2+. Maybe try that to make sure it's not a plugin issue.

    If that works, then try a Select count(*) in your code above to verify the automatic prepared statement is working correctly.

  4. Markus R

    Dec 2 Pre-Release Testers, Xojo Pro Europe / Germany

    i think your filter is wrong
    try this

    WHERE PrimarySub LIKE ?

    and filter text with ms sql is "%abc%"
    you would find anything that contains abc

    % is a wildcard character

  5. Matthew M

    Dec 2 Pre-Release Testers, Xojo Pro

    Jay,

    The API 1.x code ran fine 2019r2.1. The COUNT statement returned 46 rows, as expected without the limiting TOP(10).

    Markus,

    LIKE is my preferred conditional and I generally use the & wildcards. In this situation I was trying to eliminate any variation from the documentation examples in the event said variance was the problem. I did, however, try this but the results were the same.

    Thanks to both of you for your suggestions.

  6. Jay M

    Dec 2 Pre-Release Testers, Xojo Pro NC, USA

    @Matthew M The API 1.x code ran fine 2019r2.1. The COUNT statement returned 46 rows, as expected without the limiting TOP(10).

    Maybe try it without the TOP limit, so see if that is an issue in API 2.

  7. Matthew M

    Dec 2 Pre-Release Testers, Xojo Pro

    No change.

  8. Derk J

    Dec 2 Pre-Release Testers, Xojo Pro
    Edited last week

    SELECT TOP(10) AS Detail ....
    Perhaps?

    "SELECT * FROM tDigitalCatalog WHERE PrimarySub = ? ORDER BY SomeField DESC LIMIT 10"

    Use Limit instead of top if it doesn't work for your db

  9. Markus R

    Dec 2 Pre-Release Testers, Xojo Pro Europe / Germany

    unfortunately it is not possible to see the generated query that SelectSQL made :(

    have you test this style if it makes a difference?

        For Each row As DatabaseRow In rsDetails
          lstResults.AddRow(row.Column("Detail").StringValue)
        Next
  10. Matthew M

    Dec 2 Pre-Release Testers, Xojo Pro

    Derk - The LIMIT parameter throws a syntax error with MSSQL.

    Markus - I did try the DatabaseRow format, but had the same problem.

    Thanks for both suggestions.

  11. Derk J

    Dec 2 Pre-Release Testers, Xojo Pro

    @Matthew M Derk - The LIMIT parameter throws a syntax error with MSSQL.

    Markus - I did try the DatabaseRow format, but had the same problem.

    Thanks for both suggestions.

    Try your query without () so ... TOP 10 ...

  12. Derk J

    Dec 2 Pre-Release Testers, Xojo Pro

    More here:

    In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP.

    https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15

  13. Matthew M

    Dec 2 Pre-Release Testers, Xojo Pro

    Still no change. It seems that I will have to use 2019r1.1 until such time that this is fixed.

  14. Jay M

    Dec 2 Pre-Release Testers, Xojo Pro NC, USA

    @Matthew M Still no change. It seems that I will have to use 2019r1.1 until such time that this is fixed.

    You'll need to submit a Feedback case if it is to be fixed.

  15. Jay M

    Dec 2 Pre-Release Testers, Xojo Pro NC, USA

    One other thought, what if you try it without a prepared statement? Just build the entire query string including the where value, like the first example code for SelectSQL .

  16. Matthew M

    Dec 2 Pre-Release Testers, Xojo Pro

    Funny you should mention that. I did just try that while preparing my Feedback case and it works fine. It seems to have something to do with the SQL statement preparation so I'm going to look into that a bit more.

    Since this is to be a web app I really want to use prepared statements for security reasons.

    Thanks.

  17. Jay M

    Dec 3 Pre-Release Testers, Xojo Pro NC, USA

    Matthew,

    I just created a test app in 2019R2.1 and ran it against Express 2012 and 2019 and both work correctly, using the automatic prepared statement like you show. So it must be something with your installation or setup.

  18. Scott C

    Dec 3 Pre-Release Testers, Xojo Pro Vancouver, Canada

    @Markus R unfortunately it is not possible to see the generated query that SelectSQL made

    For future reference, if you want to see the SQL text being sent to SQL Server from local or external applications (and you have admin privileges on the Server) try SQL Profiler . It is part of the MS SQL Server Management Studio install (free).

    Of course you'll have to install and run Profiler from a Windows machine.

    I hope that helps.

  19. 6 days ago

    Johann K

    Dec 4 Perth, Australia

    I have the same problem, simple select sql, works in 2019.1.1, the first row I get in the try catch is empty

  20. Newer ›

or Sign Up to reply!