Hi Everyone I'm migrating from SQLAnywhere 7 to version 12 and came across this strange behaviour:

1) Using ODBC and trying to do a SELECT in a view that returns no rows. 2) It shows a GENERAL SQL ERROR. 3) When I order the view, this problem is solved, eg, SELECT * FROM view ORDER BY <field>.

The question is: is there any flag that could turn this behaviour back to the same as version 7? My concern is that I use views a lot and could spot this error but cannot afford to review every view to solve this issue.

Best

asked 10 Nov '11, 13:50

MarcosCunhaLima's gravatar image

MarcosCunhaLima
30691019
accept rate: 0%

retagged 11 Nov '11, 03:25

Daz%20Liquid's gravatar image

Daz Liquid
905202541

If you run a query to a "reserved" view does it create any issues for you ?

For example: the query below for me does not return any rows and does not create any issues:

select * from SYS.SYSEVENT

(10 Nov '11, 14:22) Derli Marcochi

Derli Remember that the issue only arises when I run the SELECT through ODBC (in my application). When I run this in ISQL, it returns no rows and no errors. But it has been working in version 7 flawlessly. Answering your question: it shows the same error with every view without distinction from users views and system views. Best

(10 Nov '11, 14:31) MarcosCunhaLima
Replies hidden

We're not going to get very far without some additional detail.

Does this occur with all views that return the empty set? Can you post the definition of a view which is causing the problem? Can you run request level logging or application profiling to try to pinpoint the real error returned by the server?

(10 Nov '11, 15:13) Glenn Paulley

Glen

As I said, any SELECT based on a view without ORDER BY which returns no rows and called through ODBC will generate this behaviour. I know that it's hard to know what's going on with just the general SQL error and my description but my hope was that it was something someone had already went through. Anyway, I will post the logging details and more information about the error as soon as possible. Thanks

(11 Nov '11, 06:43) MarcosCunhaLima
Replies hidden

Have you tried to use ODBC tracing to find out what exactly gets sent to the database engine?

(11 Nov '11, 08:28) Volker Barth

Volker Good suggestion. I'll give it a try and post the result ASAP.

(11 Nov '11, 08:35) MarcosCunhaLima

You don't mention whether the ISQL you are using is the Java version (dbisql) or the old non-Java version (dbisqlc). The Java version uses OBDC (though our JDBC bridge), so if it works with the Java version then the problem isn't due to using ODBC.

(11 Nov '11, 11:21) Graham Hurst

Graham I'm using the DBISQL Java version. I'll start a trace as soon as possible in order to see what's going on and post the results here. Thanks

(11 Nov '11, 12:47) MarcosCunhaLima

Then you should be able to do an ODBC trace for ISQL running the statement to compare with the ODBC trace of your application running the statement.

I couldn't find "GENERAL SQL ERROR" in any of our error messages. It seems that the error message is from your application and the problem only happens with your application.

I suspect your application relied on something in version 7 that has been subsequently fixed or otherwise changed. Might be easiest to find by running your application in a debugger against version 12 then against version 7.

(11 Nov '11, 13:50) Graham Hurst

Graham

I don't think that our application is generating this error but I will compare the traces between DbISQL and our application and post the difference here. Thanks

(11 Nov '11, 14:35) MarcosCunhaLima

FYI, you can reply to a comment by using the box-with-arrow link at the bottom right of the comment.

To clarify, SQL Anywhere 12 has no error messages that include the text "GENERAL SQL ERROR" (in any case), so if you saw that string in an error message it wasn't from SQL Anywhere. If that string wasn't in the error message, then please answer the last question in Glenn's answer.

(11 Nov '11, 16:19) Graham Hurst

Graham Yes, I undestood that SQLAnywhere didn't generate this error and I will figure out who is generating it and the exact message as soon as possible and post the information here.

(12 Nov '11, 07:39) MarcosCunhaLima

Does the view use any proxy tables to, say, SQL Server or some other database? SQL Server does issue "General SQL Error" messages.

(12 Nov '11, 09:14) Breck Carter
More comments hidden
showing 5 of 13 show all flat view

Is the statement returning a true error, or a warning?

A change in releases subsequent to version 7 was to issue a warning (+122) if a query contained SELECT TOP but the query did not contain an ORDER BY clause.

What is the precise error you are getting?

permanent link

answered 10 Nov '11, 13:56

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

Hi Glen First, congratulations for your blog, it's very useful for me!

It returns a true error in my application that uses ODBC. If I run the same query in ISQL, it works fine. The error unfortunately is not very useful: GENERAL SQL ERROR. The problem is specifically when I use the SELECT based on a view without order and the view returns no rows: SELECT * FROM Myview

There is no TOP clause involved. Best

(10 Nov '11, 14:06) MarcosCunhaLima
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×53
×13

question asked: 10 Nov '11, 13:50

question was seen: 3,350 times

last updated: 12 Nov '11, 09:14