Up to now we used the previous Servoy standard database SQL Anywhere 11. Beside our application databases, of course also the servoy_repository was running on SQL Anywhere 11. Now we have set up SQL Anywhere 12. As the dbsrv12 process can also run version 11 (and 10) created database files, we just used the existing (version 11) databse files and start these with the dbsrv12 process. Everything starts fine.
But the behaviour of our application is now very strange. After days of debugging we can reproduce some behaviour. When Servoy loads a foundset for a given form, there is 1 record missing (it seems always to be the first one)! And for example in a tree view, this leads to a missing of the first record for every node.
We think it has something to do with the LIMIT clause in a SQL statement and using ORDER BY . Servoy uses this clause as well in various scenarios as we understand.
We use the SQL Editor in Eclipse side by side with the Sybase Interactive SQL tool.
The problem only occurs in our Servoy application, but can also be reproduced in the SQL Editor, with the “advantage” that both are using the same db server connection, i. e. the same JDBC driver.
Example:
If we enter
select * from menus order by sort_order
``` in SQL Editor (Eclipse), having checked the Limit Rows: checkbox, we get one record less compared to having the Limit Rows: checkbox NOT checked!? This is of course an unacceptable behaviour. When Servoy is loading a foundset, there is also 1 record missing! It seems that Servoy always uses the ORDER BY (id) clause when loading a foundset if it's not a given one by a programmers query.
We also found that if the ORDER BY attribut has an index, the problem doesn't occur (may be for the reason that the LIMIT clause is then disabled?).
In the SQL Anywhere 12 documentation we found that the LIMIT keyword is disabled by default. This would explain the behaviour. There seems to be a way to enable the LIMIT keyword, but we haven't found out where and how. The statement is: SET OPTION reserved_keywords = 'LIMIT'; but when applying it in Sybase Interactive SQL we get an error because we are not in the PUBLIC group.
I very much hope Servoy guys can confirm and help how to overcome this problem. For understandable reasons, we were/are also afraid about the servoy_repository database file.
Best regards,
This seems to be an issue with sybase asa 12, not Servoy, reproducing the same in SQL Editor proves that.
Please file a case with sybase so they can fix.
The limit keyword seems to be something new they support in their sql dialect, they should still be backward compatible with existing sql.
But, as far as we recognize, it is NOT backward compatible!
I haven’t found where to set the option persistent, which is absolutly necessary when working with Servoy - otherwise one run’s into severe problems. I found the option is to set by: SET OPTION PUBLIC.reserved_keywords = ‘LIMIT’;
Usually, for a given database, this option can be set in Sybase Central, when making a right click on the given database and selecting properties (last context menu line). On the window, one selects tab Extended Information, and there all database parameter appear. But this one seems to be missing. Of course one would expect this only on a rebuilt database file or a newly created one with version 12.
Can you give an answer what happens when using the servoy_repository database file (as version 11 file)? I assume on INSERTS and UPDATES there are no problems, but what happens when running an application? I assume then SELECTS are executed and the “LIMIT trap” as I call it bites!?
What I want to say is using the servoy_repository database file with SQL Anywhere 12 seems dangerous?
What I am also wondering is has nobody yet changed to SQL Anywhere 12? Because anyone must be bitten by the “LIMIT trap”.
Regards,
rgansevles:
Robert,
This seems to be an issue with sybase asa 12, not Servoy, reproducing the same in SQL Editor proves that.
Please file a case with sybase so they can fix.
The limit keyword seems to be something new they support in their sql dialect, they should still be backward compatible with existing sql.
last week, we had the EXACT same issue, but not with Sybase 12, but Sybase 11!!!
We had updated Sybase 11, to the latest version, and we came across the same issue!
Every first record, of every related foundset, the first record disappeared!! (and only when the related foundset was sorted, DESC)
Every other (non-related) foundset worked just fine.
After discussion this with Jan Blok (we also first thought, that it was Servoy, because we also upgraded from 5.2.6 to 5.2.7) we rolled back the sybase 11 version, and ALL was fine again.
Strange, that Sybase 12 shows the same issue…
I hope Servoy finds some time, to dive into this, it is easy the reproduce, with the latest Sybase version (will create a ticket, with the latest Sybase libs)
I can not yet say where the final problem is to be located - in Servoy or SQL Anywhere 12. But we got a bit closer to what’s happening.
Prerequisits:
Startup the dbsrv12 process (with either a version 11 or 12 database file doesn’t matter)
Start Servoy Developer
Open SQL Editor (in Servoy Developer) for the given database
For comparison reason one can start Sybase Central and ISQL as well
What we found so far:
Run a SELECT statement in SQL Editor, for example SELECT * FROM roles ORDER BY sort_order (you have to use ORDER BY clause)
Result: If you have the Limit Rows: checkbox set (in SQL Editor), you get 1 row LESS than the number you get if you uncheck Limit Rows: checkbox. Surely not what one would expect!
There are 2 ways to limit the number of rows.
Prerequisit:
SET OPTION PUBLIC.reserved_keywords = ‘LIMIT’; for the session or set it for the database in Sybase Central under Options → reserved keyword LIMIT
With the LIMIT keyword. The SQL statement then looks like: Run in SQL Editor: SELECT * FROM roles ORDER BY sort_order LIMIT 10
Result: You get 10 records, independent of how the Limit Rows: checkox is set
With SET ROWCOUNT ; (Servoy seems to limit records this way)
Run SET ROWCOUNT 10; and SELECT * FROM roles ORDER BY sort_order in sequence (in SQL Editor)
Result: You only get 9 records instead of 10
If you run the above statements with a dbsrv11 database server, you get 10 records (as expected)
In this configuration, the The Limit Rows: checkbox overrides the SET ROWCOUNT statement, i. e. is executed after the SET ROWCOUNT.
Rob, have you got an idea what that means. I don’t think it’s a database problem, as Version 12 is running for several months already and someone would have found the very same problem. It seems to occur with Servoy.
One possibility could be the driver. How can I verify where the Driver com.sybase.jdbc3.jdbc.SybDriver in the server connection of Servoy really points to?
Regards,
rgansevles:
Robert,
This seems to be an issue with sybase asa 12, not Servoy, reproducing the same in SQL Editor proves that.
Please file a case with sybase so they can fix.
The limit keyword seems to be something new they support in their sql dialect, they should still be backward compatible with existing sql.
With SET ROWCOUNT ; (Servoy seems to limit records this way)
Run SET ROWCOUNT 10; and SELECT * FROM roles ORDER BY sort_order in sequence (in SQL Editor)
so how does this make sense?
You say set ROWCOUNT 10;
but you get 9 back (and yes there are more)
All this just in the SQL Editor so it looks like a sybase bug to me…
Don’t get me wrong, the Servoy application is showing the weird behaviour (for whatever reason). We debugged it for 3 days now. Talking about the SQL Editor in Servoy Developer is just to allow anybody to test with their own application. But the behaviour is in the Servoy application of course, you just can “simulate” it exactly in the SQL Editor - when using the SQL created by Servoy, seen in the Performance Data menu in Servoy Server Admin. And, it uses exactly the same database server connection as the Smart Client started by the developer.
How are you limiting the rows in Servoy? Is my assumption correct, that you do it with the SET ROWCOUNT and not with the LIMIT clause?
I would really like to exclude the possibility of a JDBC driver problem, but how can I explicitly verify which driver is used by the database server connection?
Regards, Robert
jcompagner:
Robert Huber:
With SET ROWCOUNT ; (Servoy seems to limit records this way)
Run SET ROWCOUNT 10; and SELECT * FROM roles ORDER BY sort_order in sequence (in SQL Editor)
so how does this make sense?
You say set ROWCOUNT 10;
but you get 9 back (and yes there are more)
All this just in the SQL Editor so it looks like a sybase bug to me…
It was a shock for us as well, and has paralyzed the whole development for the whole week. We still use (for the SQL Anywhere 11) the version 11.0.1.2045. That’s why we never had a problem up to now with Servoy applications running. But last week we started to migrate to SQL Anywhere 12, so the problems appeared.
We still can’t figure out where the problem lays, but it surely has to do with Servoy’s use of the SET ROWCOUNT - may be in conjunction with changed default settings in the SQL Anywhere setting. But it’s unlikely a database problem, as lot’s of other SQL Anywhere db users would have stumbled over this, as an ORDER BY in a SQL statement is not very rare. And, further proof, if I run the Servoy generated SQL statement (copied from Performance Data) through Interactive SQL, the problem does not appear.
Regards,
Harjo:
Hi there,
last week, we had the EXACT same issue, but not with Sybase 12, but Sybase 11!!!
We had updated Sybase 11, to the latest version, and we came across the same issue!
Every first record, of every related foundset, the first record disappeared!! (and only when the related foundset was sorted, DESC)
Every other (non-related) foundset worked just fine.
After discussion this with Jan Blok (we also first thought, that it was Servoy, because we also upgraded from 5.2.6 to 5.2.7) we rolled back the sybase 11 version, and ALL was fine again.
Strange, that Sybase 12 shows the same issue…
I hope Servoy finds some time, to dive into this, it is easy the reproduce, with the latest Sybase version (will create a ticket, with the latest Sybase libs)
What I am wondering is, has nobody ever updated SQL Anywhere 11 to version 11.0.1.2569??? Not even Servoy? Can that be? Because the problem is absolutly reproducable, so anybody who has updated to that version must have this problem. Very strange.
Servoy always delivered a much older version/build of Sybase 11
So I think nobody, did that
We had to upgrade once, because of a performance issue, with a VERY large DB.
We reported that to Sybase, and some days later, there was an update.
Because of that, we checked once in a while, of there where new versions, so with a new version of our solution, we shipped also the latest Sybase libs.
Until now…
Robert Huber:
Run SET ROWCOUNT 10; and SELECT * FROM roles ORDER BY sort_order in sequence (in SQL Editor)
Result: You only get 9 records instead of 10
If you run the above statements with a dbsrv11 database server, you get 10 records (as expected)
Robert, there you have a nice small reproducable case for sybase.
You should simply get 10 records, this is a bug in that database release.
The limit-thing is a new keyword they introduced, they have added a setting just to prevent apps to break that have used that word as a column, for example.
It does not fix the issue.
I am not sure whether it’s a bug or whether SET ROWCOUNT n (by design) is not always deterministic and then you can’t use it the way it is used yet in Servoy - as it obviously breaks any application developed. I posted an entry in the SQL Anywhere forum, please see here: http://sqlanywhere-forum.sybase.com/
After searching on the internet, it seems to me at the moment that you can’t use SET ROWCOUNT for predictable results, as at various places they point to non-deterministic results (under certain circumstances) when using SET ROWCOUNT.
I tried also SELECT TOP 10 * FROM roles in ISQL and I get a warning saying “The result returned is non-deterministic”. This warning disappears when I add the ORDER BY clause. This is somwhat understandable, as TOP seems to need an ordering of records to tell which are top.
Because that setMaxRows is also exactly the thing SQL Explorer sets when you enable that max row setting.
So this is really a bug in sybase, the max rows is somehow giving one less back
And yes “predictable results” is logical when you don’t order, because if you don’t order you don’t know which rows you get first, so 2 the same calls to the db at the same time could result in 2 different sets of rows.
If you order then it is predicable … Because then you will always get the same results (if ofcourse no udpate or insert did happen in the mean time)
I don’t know where to give feedback at Sybase for that bug, i. e. for any SQL Anywhere bug reporting. Help is very much appreciated. I think it would also be very good if you Servoy guys also could report it as an error to get this bug fixed as soon as possible, as it completely breaks all work with Servoy (for the updated version of SQL Anywhere 11 as said by Harjo and for the version 12 as I discovered).
jcompagner:
I could be wrong but as far as i know, servoy itself doesn’t call SET ROWCOUNT n …
that is what the driver does when we call:
Because that setMaxRows is also exactly the thing SQL Explorer sets when you enable that max row setting.
So this is really a bug in sybase, the max rows is somehow giving one less back
I agree to this.
jcompagner:
And yes “predictable results” is logical when you don’t order, because if you don’t order you don’t know which rows you get first, so 2 the same calls to the db at the same time could result in 2 different sets of rows.
If you order then it is predicable … Because then you will always get the same results (if ofcourse no udpate or insert did happen in the mean time)
Why does nobody seem to have a problem except we as Servoy users. I mean even SQL Anywhere 12 is already running for quite a while, not to talk about SQL Anywhere 11 (even with the EBF).
Seems that other IDE’s are limiting the number of rows somehow differently?
Regards,
jcompagner:
I could be wrong but as far as i know, servoy itself doesn’t call SET ROWCOUNT n …
that is what the driver does when we call:
i don’t know maybe there are not many users of that version or those users don’t use java or those do use java but don’t use setMaxRows()
But it is very simple, if you use SQL Explorer plugin and you limit the count of rows through that plugin and you do your query, you have also 1 less right?
that is then clear, then it is not a servoy problem but a sybase one.
But who knows maybe it is the driver? Did you also try the latest driver from sybase? Maybe they updated something on both ends?
Hmmm, sometimes a nice closed system would be a dream - only one responsible company … just dreaming
No feedback needed, I know allllll the great advantages of open systems
Ok, I called Sybase and there is a case opened, case # 11668828.
jcompagner:
i don’t know maybe there are not many users of that version or those users don’t use java or those do use java but don’t use setMaxRows()
So it seems none of Servoy developers ever updated the SQL Anywhere 11 or went with SQL Anywhere 12 (except us
jcompagner:
But it is very simple, if you use SQL Explorer plugin and you limit the count of rows through that plugin and you do your query, you have also 1 less right?
that is then clear, then it is not a servoy problem but a sybase one.
But who knows maybe it is the driver? Did you also try the latest driver from sybase? Maybe they updated something on both ends?
Yes, I tried all sorts of drivers, no luck. I still would like to know how I can see where the Driver entry string “com.sybase.jdbc3.jdbc.SybDriver” points to in the Database Servers connection? As there are quite a few jconn3.jar files on one machine.
So it seems none of Servoy developers ever updated the SQL Anywhere 11 or went with SQL Anywhere 12 (except us)
We are using SQL Anywhere version 11.0.1.2044 on our development computers (which I believe is the Servoy distribution). It’s an older version, but we haven’t had any problems with it.
We went back to version 11.0.1.2045 on Mac OS X 10.6.7, also for development. But we have to install SQL Anywhere 12 at one of our customers site, so I am desperately awaiting a fix for this problem.
kwpsd:
So it seems none of Servoy developers ever updated the SQL Anywhere 11 or went with SQL Anywhere 12 (except us)
We are using SQL Anywhere version 11.0.1.2044 on our development computers (which I believe is the Servoy distribution). It’s an older version, but we haven’t had any problems with it.