How to resolve 'Update operation attempted on non-updatable query'?

[Former Member]

I am working on a sybase-iq server Sybase IQ/15.4.0.3014

I have a working query to update one field of a table as below

update  table1
set     a.field1= b.some_value
from    table1 a,
        table2 b
where   a.id = b.id

This is working fine when I execute it from a sql session. When it is being called from a high level application, I am getting the below error for the above query

SQL Exception code is 7301
Update operation attempted on non-updatable query

I am not able to find why I am getting this error. Is there any solution to amend the query. Searching on the internet is not helping much.

Have anyone come across such issue?

[former_SQLA_member1694878]

Are both tables local, or are either of them remote (proxy) tables?

[Breck_Carter]

This is a forum for SQL Anywhere so questions about IQ might go unanswered.

However, IQ's SQL syntax is similar to SQL Anywhere's so... show us exactly how the query is coded in the high-level application. There may be some aspect of that code that causes the error.

Also... is it possible the error is coming from a different operation located inside a trigger fired by the update? (presumably that would affect the update when executed in a sql session, but maybe the databases were different).

Here is the description of the error, which certainly doesn't seem to apply to your code: "You attempted an insert, update, or delete operation on a query that is implicitly read-only. Queries that contain DISTINCT, GROUP BY, HAVING, EXCEPT, INTERSECT or UNION, or that contain aggregate functions, or that involve a join, are implicitly read-only. If the query references a view then the view may be non-updatable."

[Former Member]

@Mikel They both are local tables.

@Breck The query above is a part of stored procedure. The stored procedure is being called from java with a normal jdbc call. - There is no trigger on the table. - The tables are in same database I have come across that error description, but can't relate it here and hence not able to solve. The stored procedure is executing fine from a sql session on commandline.

Besides, can you please let me know which would be the correct place to ask this question? I initially asked it on SO and was redirected to this site.

Thanks

[reimer_pods]

You might post your question here:
http://scn.sap.com/community/sybase-iq.

[VolkerBarth]

A wild guess: Is the value of the "ansi_update_constraints" option different between your DBISQL session and your application?

[Former Member]

Could you please restart your IQ server with -o out.txt and then connect using DBISQL.

Once connected, perform a SET OPTION PUBLIC.cis_option=7 and then execute your UPDATE statement. You should see in the server window some messages about the query being handled in either FULL/PARTIAL/NO PASSTHRU.

Now disconnect and run your application. You should now get additional messages in the server window about PASSTHRU when your application executes the UPDATE query.

Could you please post the two sets of messages (one from DBISQL and one from your application). Note, if you are concerned about posting the information, then feel free to email it to me directly. We should be able to help you further once we get the query decomposition output.

If you have trouble finding the information within the server window, then have a look at out.txt instead.

One final note, after you have done the above, it is probably best to log in using DBISQL and perform a SET OPTION PUBLIC.cis_option=0 in order to turn off the logging. You should also restart your IQ server without the -o out.txt.

[Breck_Carter]

FWIW error code 7301 does not appear to involve remote operations; the code is 7338 for that one (at least, it is in SA16).

[VolkerBarth]

That's what I had thought, too, and the message text for 7338 is different, as well: "Update operation attempted on non-updatable remote query", and the OP has made clear that remote queries are not involved.

But then I was uncertain: It's IQ, not (only) SA, so possibly things are different there... - that I simply won't know:)

[Former Member]

Note that although there are no remote tables involved; queries involving IQ tables still get decomposed into the IQ piece and the SQL Anywhere piece hence the PASSTHRU mode is still very relevant and useful to obtain.

[Breck_Carter]

Ah... hence the title of this website is not "IQ Forum" 🙂

But hey... we're not exactly overloaded here, IQ questions should be welcomed with open arms IMO.

[VolkerBarth]

Oh yes, please - and furthermore, us "IQ-less" folks will learn something, too:)