next »

[Matthew F.]

My code is more or less like the following:

Code

define rec record like t.*
define f like t.f
 
prepare upd_cur from "select * from t for update"
prepare upd_stmt from "update t set f = ? where current of upd_cur"
 
foreach upd_cur into rec.*
 
   let f = rec.f
 
   # ... code here may change the value of f ...
 
   if (f != rec.f)
      execute upd_stmt using f where current of upd_cur
   end if
 
end foreach

On the first iteration through the FOREACH loop, on the UPDATE ... WHERE CURRENT statement, the result is:

      status      -6372 (General SQL error, check SQLCA.SQLERRD[2].)
      sqlcode     -6732
      sqlerrd[2]  -16916 (SQL Server Error: "A cursor with the name '%.*ls' does not exist.")

On the second iteration, the following line yields an error:

      let f = r.f

The error is:

      sqlca.sqlcode     -400 (Fetch attempted on unopen cursor.)

After this, our actual program entered an infinite loop. I don't want to re-run that scenario (in order not to wind up with another runaway process on our server), so unfortunately I can't report any further details about the infinite loop.

As a workaround, I tried changing the update statement to use the current record's primary key rather than "WHERE CURRENT OF":

Code

prepare upd_stmt from "update t set f = ? where t.key = ?"
 
foreach upd_cur into rec.*
 
   ...
 
   if (f != rec.f)
      execute upd_stmt using f, t.key
   end if
 
end foreach

This appears to work fine.

However, my team is cautious. They've never previously used FOR UPDATE cursors in a FOREACH statement. They're concerned that FOREACH might have other unexpected behavior when used with a FOR UPDATE cursor. Consequently, they prefer that I use an ordinary SELECT cursor, and explicitly lock/unlock records inside the FOREACH loop.

I googled this a bit, and found a few very old threads referring to Informix bug 31661, which also involves a cursor not found error. In the following thread, Jonathan Leffler of Informix explained that the problem had to do with name mangling by the compiler. His recommended workaround was to use the CURSOR_NAME() function to get the mangled name of the cursor.

https://groups.google.com/forum/#!topic/comp.databases.informix/-aBoIYsDVzE

Given that Leffler's recommendation is 20 years old, it may no longer be relevant. In addition, I'm not sure how the 4Js product relates to the Informix product. I assume there is a shared history, but am not sure.

I'd like to just test the CURSOR_NAME() function and see if it works, but there is no such function in the 4Js product.

What approach would you recommend?

Can we safely use a FOR UPDATE cursor in a FOREACH statement, provided the UPDATE statement in the body of the loop doesn't use WHERE CURRENT OF?

[Stephen T.]

I may be talking out of my hat (I usually am), but I thought that the end of the transaction (ie the update not in a transaction block) causes all open cursors to be closed, unless they're declared 'WITH HOLD'. So it may be that  you're inadvertently closing the FOREACH cursor when you update the first row that meets the criteria.

[Matthew F.]

That's an interesting point. I forgot to show where the transaction is started and finished.

In this case, we're using a single transaction to update a master record, and all of its related detail records. The FOREACH loop is for the detail records. The transaction starts somewhat before the FOREACH, and ends immediately after the FOREACH.

[Stephen T.]

OK.
Isn't there another issue with updating rows from an incomplete 'SELECT'? IE potentially modifying the rows of the cursor that's still being read? From recollection (and this is going back a few years) isn't one way round it to introduce a sort on the cursor select, so that the rows are read and sorted before their processed in the FOREACH? That seems to ring a bell with me.

[Sebastien F.]

Hi all,

WITH HOLD + FOR UPDATE cursors have different behaviors with Informix DB and other DBs.

According to the error message provided, seems your DB is SQL Server... correct?

Consider having a lock at the documentation:

http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_sql_programming_078.html

If this does not help, please provide a self-contained sample to let us reproduce and investigate.

Seb

[Matthew F.]

Hi Seb,

Yes, it is SQL Server. We're not using the WITH HOLD clause.

Do you have any additional advice based on that information?

As you recommended, I've just re-read the documentation for WITH HOLD FOR UPDATE. I don't think the sample code there fully addresses our situation, because we need to process the detail rows individually, and the sample code doesn't lock the detail rows. That's why I thought it made sense to use a FOREACH statement with a FOR UPDATE cursor to process the detail rows.

I'll prepare a self-contained example, and see if that helps to identify any possible problem in my code. If I'm still stuck, I'll submit the example for your review.

Matthew

[Reuben B.]

It maybe the way you have typed in your example, but you are missing a DECLARE.  The argument for WHERE CURRENT OF ... should be a cursor-id, not a statement-id.  So perhaps it should be something like ...

Code

...
 
prepare select_stmt from "select * from t for update"
declare select_curs for select_stmt
prepare upd_stmt from "update t set f = ? where current of select_curs"
 
 ...

Note the use of "sid" for statement-id and "cid" for cursor-id here
http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_DynamicSql_PREPARE.html
http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_positioned_updates_DECLARE.html
http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_positioned_updates_UPDATE_WHERE_CURRENT_OF.html

I also wondered if you could prepare an update statement that refers to a "where current of" and if you do, wether it should be before or after the FOREACH.  I would normally have coded ...

Code

foreach upd_cur into rec.*
 
  let f = rec.f
 
  # ... code here may change the value of f ...
 
  if (f != rec.f)
     update t set t.*= f.* where current of upd_cur
  end if
end foreach

... but that maybe habit rather than any reason. 

I also wondered if you should have exception handling in place to exit out after the first error, rather than doing a second iteration after some error has occurred.

[Matthew F.]

Good catch. This was a simplified example, and I did forget to type in the DECLARE statement here. The actual code does contain the DECLARE.

We do also have some error handling within the actual FOREACH loop, but not for the FOREACH statement itself.

I was admittedly a little careless in putting together the simplified code that I posted here. I thought perhaps there would be a well-known issue with FOREACH and SELECT ... FOR UPDATE, given the existence of Informix bug 31661.

I'll put together a standalone example.

[Matthew F.]

I put together a standalone program using Genero Studio's SQLite database (custdemo). Unfortunately I found that SQLite does not support SELECT ... FOR UPDATE.

The code below should demonstrate the problem when used with SQL Server, provided you configure the database connection yourself. As a new Genero developer, so far I have just relied on my company's existing FGLPROFILE and environment variables, and the database connection "just works".

Code:

-- Program to test behavior of a FOREACH loop when the cursor was declared
-- with a SELECT ... FOR UPDATE statement.

schema custdemo

main

   define
      dbms             string,
      rec record       like customer.*,
      contact_name     like customer.contact_name

   connect to "custdemo"

   let dbms = arg_val(1)

   begin work

   if dbms = "sqlite" then
      # SQLite does not support SELECT ... FOR UPDATE.
      prepare sel_stmt1 from "select * from customer"
      declare cur1 cursor for sel_stmt1
      prepare upd_stmt1 from
         "update customer set contact_name = ? where store_name = ?"

      foreach cur1 into rec.*
         let contact_name = "[some calculated value]"
         if contact_name != rec.contact_name then
            execute upd_stmt1 using contact_name, rec.store_name
         end if
      end foreach

   else
      # Assume that SELECT ... FOR UPDATE is supported.
      prepare sel_stmt2 from "select * from customer for update"
      declare cur2 cursor for sel_stmt2

      # The problem I encountered was that "UPDATE ... WHERE CURRENT OF"
      # yields an error when the update statement is executed.
      prepare upd_stmt2 from
         "update customer set contact_name = ? where current of cur2"

      foreach cur2 into rec.*
         let contact_name = "[some calculated value]"
         if contact_name != rec.contact_name then
            execute upd_stmt2 using contact_name
         end if
      end foreach

   end if

   # Roll back. We just wanted to see if the FOREACH loop worked.
   rollback work

end main

[Sebastien F.]

Hello Matthew,

Your code looks ok to me, I have tested with Oracle, DB2 and PostgreSQL, but I cannot test with SQL Server until next week.

Do you confirm that you get the same error as in your original program?

If yes, please open a case at the support channel.

Provide all configuration information (OS type and version, Genero BDL Version, SQL Server version, exact ODI driver used, SQL Server client used, ODBC data source settings, DB Server settings etc)

We have regression tests checking FOR UPDATE / WHERE CURRENT OF with SQL Server, and there is no known issue with this, so we need to identify what is different with your configuration.

If this is a blocker for you, consider using the primary key (assuming there is one defined) in the WHERE clause of the UPDATE statement: This is the most portable solution.

Seb

[Matthew F.]

Hello Seb,

Thank you for your diligence on this.

I had been reluctant to test my original code again on our development server for fear of another runaway process, so I had been tinkering on my own PC on a standalone program using SQLite.

However, I did test again on that server using SQL Server. The program worked fine.

I had to rewrite the "SELECT ... FOR UPDATE" and "UPDATE ... WHERE CURRENT OF ..." statements, because I had long since changed my code to use the primary key. And now they work.

In order to reproduce the error with SQLCODE = -6372 and SQLERRD[2] = -16916, I had to introduce a typo into the cursor name in the WHERE CURRENT OF clause.

The most likely explanation for the error that I reported is that I had misspelled the name of the SELECT ... FOR UPDATE cursor. Rather embarrassing.

I apologize for wasting your time. However, I'm glad to know that FOREACH works with SELECT ... FOR UPDATE cursors. That pattern is more convenient than explicitly locking/unlocking each detail record.

Best regards,

- Matthew

[Sebastien F.]

You are welcome.

If your problem is solved, then it's not wasted time.

Regarding locking / concurrent data access, you may want to have a look at following topics of our SQL Programming guide:

http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_sql_programming_070.html
http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_sql_programming_079.html
http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_sql_programming_072.html

There are other useful and interesting topics in this chapter, take the time to go through!

Regarding FOREACH, remember it's nothing but a combination of OPEN + FETCH + FOR loop...

Cheers,
Seb

[Matthew F.]

One of my colleagues figured out what the initial programming error was. I had prepared the UPDATE ... WHERE CURRENT OF statement before declaring the SELECT ... FOR UPDATE cursor.

That code compiled, but at runtime it led to SQL Server error -16916 ("A cursor with the name '%.*ls' does not exist").

Declaring the cursor before preparing the UPDATE ... WHERE CURRENT OF statement fixed the problem.

I guess that's a beginner's error. In retrospect it all makes sense. Live and learn!

Thanks again.

[Sebastien F.]

Oh yes I confirm that can bring trouble if you prepare UPDATE/DELETE WHERE CURRENT OF before the DECLARE cursor!

In fact the ODI driver replaces WHERE CURRENT OF <fgl-cursor-name> by the real ODBC cursor name of <fgl-cursor-name>.
But when <fgl-cursor-name> was not yet declared, we cannot guess what future ODBC cursor name it will be...

The driver should however control this, to make sure to not use an unexisting/invalid ODBC cursor name.

The string [%.*ls] you reported looks like some un-initialized memory, so that's not good...

We will review the code and make sure that the code is robust in such case.

Thanks for reporting the issue!
Seb

[Matthew F.]

That string ("A cursor with the name '%.*ls' does not exist.") is just the generic version of the error message as found in Microsoft documentation:

https://technet.microsoft.com/en-us/library/cc645894(v=sql.105).aspx

In practice, if the error message were displayed, it would be replaced with the name of the non-existent cursor. I just looked up the error code to see what it means.

The only bug was in my code. :)