Hello, i have a strange problem when i try to write down to a Sybase IQ db, using django 1.7 Using the .save() method does not write to the underlying database as expected from the official django documentation.

Looking at the source code in this repo (https://github.com/sqlanywhere/sqlany-django) i have seen 'AUTOCOMMIT' listed as one of the key that can solve this problem, but I have no way to tell which value should i set.

Digging around i have seen that you can set any string value to get into the function

base.py:

self.set_autocommit(self.settings_dict['AUTOCOMMIT']) -> _set_autocommit( self, autocommit )

and consequently set

base.py:

curs.execute( "SET TEMPORARY OPTION chained='%s'" % ('Off' if autocommit else 'On') )

to Off, which is what i want.

This is something that another user https://github.com/faxioman/sqlany-django solved by forcing the chained option to Off, but it's not working.

What am i doing wrong here?

asked 24 Feb '15, 08:56

Flavio's gravatar image

Flavio
15112
accept rate: 0%

edited 24 Feb '15, 08:59


Be extremely careful when using chained mode as an auto commit alternative. Many people believe that auto commit and chained mode are the same thing; but they are radically different. Auto commit is an explicit commit request initiated by either the driver or the server after a REQUEST is complete whereas chained mode issues an explicit commit after EVERY STATEMENT.

In general, an application should not use auto commit nor chained mode. Instead, the application should issue explicit commits at appropriate times. Relying on auto commit or chained mode introduces unnecessary performance degradation and can lead to some very unexpected behaviour. I realize that with IQ it might make most sense to have auto commit turned on to ensure you are always getting the most current snapshot; but it is still much better to code your application such that it issues the commits directly when appropriate.

However, if you must resort to auto commit, then be advised that chained mode is not auto commit. The Django driver does not support auto commit so you may think that using chained mode will be sufficient or equivelent. However consider the following (very contrived) example:

CREATE FUNCTION test_salary_increase( IN emp_cat char(10), IN temp_increase numeric(4,3) )
    RETURNS numeric(12,3)
BEGIN
    DECLARE temp_total numeric(12, 3);
    DECLARE over_under numeric(12, 3);

    UPDATE salary_table SET salary = salary * temp_increase WHERE employee_category = cat;
    SELECT SUM(salary) INTO temp_total FROM salary_table;
    ROLLBACK;
    SET over_under = 10000000 - temp_total;
    RETURN( over_under );
END

Suppose the application now does something like SELECT test_salary_increase( 'dev', 1.015 ). If auto commit is off and chained mode is on then no harm is done. If the Django driver supported auto commit and auto commit was set to on while chained was also set to on, the again there would be no harm done since the driver would issue the explicit commit AFTER the SELECT query returns. HOWEVER, if chained was set to off in any of the situations, then the server would issue the commit AFTER EACH STATEMENT in the procedure resulting in the UPDATE statement above getting committed immediately after completion. The ROLLBACK would therefore do nothing.

So final thoughts, yes the Django driver does not support auto commit and you may think that setting chained to off is the equivalent or next best thing; but I would strongly advise against it. It would be better (both from a performance as well as a proper/expected behaviour standpoint) if you coded your application to issue explicit commits at appropriate times.

permanent link

answered 24 Feb '15, 10:19

Karim%20Khamis's gravatar image

Karim Khamis
5.7k53870
accept rate: 40%

As a developer, using "chained off" mode in that particular sample looks nice - Now I'll have to find out how to implement that in-house:)

Of course, it won't be appropriate for other employee categories.

(24 Feb '15, 11:04) Volker Barth

Solved by putting cursor.execute("SET TEMPORARY OPTION chained='Off'")

permanent link

answered 24 Feb '15, 09:17

Flavio's gravatar image

Flavio
15112
accept rate: 0%

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:

×41
×21
×5

question asked: 24 Feb '15, 08:56

question was seen: 3,239 times

last updated: 24 Feb '15, 17:00