B4J Question MySql - get records BETWEEN

[atiaust]

Hi All,

I have a Mysql question.

I have a line of code to select MySql records between 2 values.

Dim cursor As ResultSet = gSql.ExecQuery("SELECT * FROM "&dbTable&" WHERE sumID BETWEEN 500 AND 600 ")

This only returns 10 records.

If I use the same line in the Mysql server console
mysql$ SELECT * FROM table1 WHERE sumID BETWEEN 500 AND 600;

I get 100 records..

It appears that B4J is not passing the full request to MySql.

Any one have any clues?

Thanks

 

[OliverA]

If I use the same line in the Mysql server console

Have you actually tried the same line with B4J and seen if the result is different?

DimcursorAsResultSet = gSql.ExecQuery("SELECT * FROM table1 WHERE sumID BETWEEN 500 AND 600")

Don't use the variable dbTable, but the actual table name just as in your server console query, leave out the space after 600 (just for good measure) and see what happens. Also make sure your connected to the same MySQL server and the same database as with the console.

 

[atiaust]

Thanks OliverA,

It looks the space after the second value was causing the problem although I have no idea why.
The table variable works fine.

Thanks.

 

[OliverA]

I tried to replicate your issue, but could not. I used both an INT and a VARCHAR(4) for the sumID and still could not reproduce the issue (with the added space in the query). So
1) If the code was copied and pasted, maybe the space was actually not a space, but a character code that displayed as a space. To see if that may have been the case, add a space back in (using space bar), re-run the query and see what happens.
2) I'm (most likely) using a different B4J (5.82), a different JVM (version 1.8.0_144), a different MySQL connector version (mysql-connector-java-5.1.40-bin) and a different MySQL server version (5.6.14) with different encoding (UTF8 for server connection, UTF8 for server char set and UTF8-bin for tables), which may all lead to me not being able to reproduce your issue.

 

[atiaust]

OliverA,

Thanks for following up.

I remember this problem ages ago and it was caused by Win 10 cut & paste adding (I think) an end of line character or something.

I copied the block of code from an old app and this is when the problem became evident.
The problem was fixed by Erel ages ago but I had forgotten it completely.

Thanks again.