Re^4: portable mysql auto

Well - to start off DBIx::Sequence doesn't work with Sybase (and probably won't work with MS-SQL).

Running it without creating the tables first generates the following error:

ct_result(ct_dynamic(CS_PREPARE)) returned -205 at /usr/lib/perl5/site
+_perl/5.6.0/i386-linux/DBD/Sybase.pm line 105.
DBIx::Sequence: Server message number=208 severity=16 state=1 line=1 s
+erver=trollprocedure=DBD1text=dbix_sequence_state not found. Specify 
+owner.objectname or use sp_help to check whether the object exists (s
+p_help may produce lots of output).
 at ./sequence.pl line 5
[download]

Creating the tables manually and running a basic loop to get new IDs generates the following error:

ct_param() failed! at /usr/lib/perl5/site_perl/5.6.0/DBIx/Sequence.pm 
+line 257.
DBIx::Sequence: OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY 
+= (1) NUMBER = (16)
Message String: ct_param(): user api layer: external error: This routi
+ne cannot be called while results are pending for a command that has 
+been sent to the server.
 at ./sequence.pl line 11
[download]

The reason for this is that this module doesn't properly fetch all results after executing queries - and this leaves the various statement handles that it creates in an unstable state when used with DBD::Sybase.

In addition, pre-preparing 5 or 6 queries works really well with Oracle, but is not a good idea with Sybase, because this will open 5 or 6 connections to the server for each instance of DBIx::Sequence.

After patching the code so that it will work with Sybase I ran three concurrent processes that did nothing but fetch new IDs. The result was that only one process actually got any IDs - the other two were effectively locked out. Admittedly this is an extreme use of the module, but it does show the limits that this technique can have.

Michael

Comment on Re^4: portable mysql auto_increment Select or Download Code

Replies are listed 'Best First'.
Re^5: portable mysql auto_increment by adrianh (Chancellor) on Oct 26, 2002 at 17:51 UTC
All fair points - although I don't see any problems with race conditions (which was what had me nervous :-) Running it without creating the tables first generates the following error ... Did you run the extended tests on install? DBIx::Sequence uses 2 tables for its operation, namely the dbix_sequence_state and the dbix_sequence_release tables. Those tables will be created if you run extended tests, if not you will need to create them yourself. (from the docs) Creating the tables manually and running a basic loop to get new IDs generates the following error ... The reason for this is that this module doesn't properly fetch all results after executing queries - and this leaves the various statement handles that it creates in an unstable state when used with DBD::Sybase. That's interesting - I didn't know that you have to retrive all results from a statement handle in DBI. Is this a general thing, or just an issue (bug?) for the Sybase DBD? (I suddenly feel nervous about some of the code I've written to handle paged results.) Might be nice to submit your Sybase patches to the DBIx::Sequence author. After patching the code so that it will work with Sybase I ran three concurrent processes that did nothing but fetch new IDs. The result was that only one process actually got any IDs - the other two were effectively locked out. Admittedly this is an extreme use of the module, but it does show the limits that this technique can have. True. However if you're being forced to write code for a DB without decent transaction support you don't have much choice (and, as you say, it is a somewhat extreme example that wouldn't be common outside of data-loading) That said, you are completely correct that the best way to do it is with transactions if you have them.	[reply]
Re: Re: Re^4: portable mysql auto_increment by mpeppler (Vicar) on Oct 28, 2002 at 17:21 UTC
I didn't know that you have to retrive all results from a statement handle in DBI. Is this a general thing, or just an issue (bug?) for the Sybase DBD? This may be specific to DBD::Sybase. The problem is that I need an extra fetch (the fetch that returns an empty array) to realize that there are no more results to be fetched. Otherwise the Sybase client libraries will complain that a new request is being attempted on a connection that has pending results. Michael	[reply]