Databases
StackOverflow2013
Postsdbo
POMultiple prepared statements disrupt a transaction using DBD::Sybase
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple prepared statements disrupt a transaction using DBD::Sybase
    primarykey
    data
    text
    <p>In my Perl script, I use DBD::Sybase (via DBI module) to connect to a SQL Server 2008. The base program as below runs without problem:</p> <pre><code>use DBI; # assign values to $host, $usr, $pwd my $dbh = DBI-&gt;connect("dbi:Sybase:$host", $usr, $pwd); $dbh-&gt;do("BEGIN TRAN tr1"); my $update = $dbh-&gt;prepare("UPDATE mytable SET qty = ? where name = ?"); $update-&gt;execute(100, 'apple'); $dbh-&gt;do("END TRAN tr1"); </code></pre> <p>however, if I insert one more <code>prepare</code> statement right before the existing <code>prepare</code> statement, to have the program look like:</p> <pre><code>... my $insert = $dbh-&gt;prepare("INSERT INTO mytable (name, qty) VALUES (?, ?)"); my $update = $dbh-&gt;prepare("UPDATE mytable SET qty = ? where name = ?"); ... </code></pre> <p>and the rest is all the same, then when I run it, I got:</p> <pre><code>DBD::Sybase::db do failed: Server message number=3902 severity=16 state=1 line=1 server=xxx text=The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. </code></pre> <p>So looks like the additional <code>prepare</code> statement somehow disrupted the entire transaction flow. I had been running the same code via the <code>DBD::ODBC</code> driver with no problem against a SQL SERVER 2005. (But my firm upgraded to 2008 and I had to use the <code>DBD::Sybase</code> to get around some other problems.)</p> <p>Any help / suggestion on how to resolve this issue would be much appreciated. In particular, using a different db handle for the other <code>prepare</code> is not a desired solution since that will beat the purpose of having them in a single transaction.</p> <p><strong>UPDATE:</strong> Turns out if I execute at least once on the additional insert, then the program is again run fine. So looks like every prepared statement needs to be run under Sybase. But that isn't a requirement with ODBC and isn't a reasonable requirement in general. Anyway to get around it?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZhang18
    1. USZhang18
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you executing the first prepared statement before creating the next one?
      singulars
      1. POMultiple prepared statements disrupt a transaction using DBD::Sybase
      1. USRawheiser
    2. COTurns out the order doesn't matter. If I prepare both statements and execute both afterwards (or prepare each and execute immediately), the script will run fine. The issue is that if I prepare both and only execute one of them, then the `commit tran` doesn't work. I do not know how many statements I need to prepare beforehand so there is a real chance that in the actual script some prepared statements will *not* be executed.
      singulars
      1. POMultiple prepared statements disrupt a transaction using DBD::Sybase
      1. USZhang18
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload