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?

asked 10 Oct '13, 16:08

mtk's gravatar image

mtk
45225
accept rate: 0%

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

(10 Oct '13, 16:14) Mikel Rychliski
Comment Text Removed

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."

(10 Oct '13, 16:40) Breck Carter

@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

(11 Oct '13, 00:54) mtk

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

(11 Oct '13, 03:29) Reimer Pods
1

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

(11 Oct '13, 07:11) Volker Barth

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.

(15 Oct '13, 08:33) Karim Khamis
1

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

(15 Oct '13, 09:01) Breck Carter

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:)

(15 Oct '13, 09:07) Volker Barth

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.

(15 Oct '13, 11:43) Karim Khamis

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.

(15 Oct '13, 14:47) Breck Carter

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

(15 Oct '13, 15:13) Volker Barth
More comments hidden
showing 5 of 11 show all flat view
Be the first one to answer this question!
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:

×90
×34
×31

question asked: 10 Oct '13, 16:08

question was seen: 7,495 times

last updated: 15 Oct '13, 15:13