Batch Query in Sybase

Posts   
 
    
sunnyman
User
Posts: 51
Joined: 21-Feb-2007
# Posted on: 19-May-2007 19:20:56   

Hi,

These days we are trying to put Sybase ASA10 driver in production and found 2 problems

First Problem The insert (Add New Entity) doesn’t retrieve the last Identity. Below is the final insert statement that the driver sent to db server, which returns “Syntax error near SELECT”

"INSERT INTO [Person] ([PersonName])  VALUES (:PersonName);SELECT :PersonID=@@IDENTITY"
or
"INSERT INTO [Person] ([PersonName])  VALUES (?);SELECT ?=@@IDENTITY"

Any ideas from Sybase experts why this statement fails - it works with sql server (@PersonName, @PersonID)

To solve this problem I remove the select part from query and use InserQuery.Add Sequence (llblgen specific) and it works fine,

Is there any problem with this approach?

Second Problem The driver some times doesn’t detect @@identity so I change its select statement and it works fine

" select  c.column_name as ColumnName, " +
                " c.column_id as ColumnOrdinal ,c.[nulls] as AllowNull,c.width as ColumnLength , " +
                " c.Scale as ColumnScale,c.[default] as DefaultValue, " +
                " c.max_identity as MaxIdentity,c.column_type as ColumnType, " +
                " d.domain_name as ColumnDBType, u.type_name as UDT_Name,  " +
                " if dxc.sequence is null then 'N' else 'Y' endif as IsPK, " +
                " if c.[default] = 'autoincrement' or c.[default] = 'globalautoincrement' then 'Y' else 'N' endif as IsIdentity " +
                " from systabcol c " +
                " join systab t on t.table_id = c.table_id  " +
                " join sysdomain d on c.domain_id=d.domain_id " +
                " left join sysidxcol dxc on c.table_id=dxc.table_id and c.column_id=dxc.column_id and dxc.index_id =0 " +
                " left join sysusertype u on c.user_type=u.type_id " +
                " where t.table_name = " ;

Instead of checking against MaxIdentity which is not always work, i check for autoincrement

if c.[default] = 'autoincrement' or c.[default] = 'globalautoincrement' then 'Y' else 'N' endif as IsIdentity "

Thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 21-May-2007 10:16:24   

sunnyman wrote:

Hi,

These days we are trying to put Sybase ASA10 driver in production and found 2 problems

First Problem The insert (Add New Entity) doesn’t retrieve the last Identity. Below is the final insert statement that the driver sent to db server, which returns “Syntax error near SELECT”

"INSERT INTO [Person] ([PersonName])  VALUES (:PersonName);SELECT :PersonID=@@IDENTITY"
or
"INSERT INTO [Person] ([PersonName])  VALUES (?);SELECT ?=@@IDENTITY"

Any ideas from Sybase experts why this statement fails - it works with sql server (@PersonName, @PersonID)

To solve this problem I remove the select part from query and use InserQuery.Add Sequence (llblgen specific) and it works fine,

It's likely that sybase doesn't support batching of queries. You then have to make the second select a SequenceRetrievalQuery and set the ExecuteSequenceCommandFirst flag to false, similar to how it's done in the Access DQE (see sourcecode of Access DQE's DynamicQueryEngine.CreateInsertDQ).

LLBLGen Pro supports sequence queries before and after inserts. I'm not sure what you mean with InsertQuery.Add sequence, but if it's a sequence query then that's indeed the way to solve it. simple_smile

Is there any problem with this approach?

Second Problem The driver some times doesn’t detect @@identity so I change its select statement and it works fine

" select  c.column_name as ColumnName, " +
                " c.column_id as ColumnOrdinal ,c.[nulls] as AllowNull,c.width as ColumnLength , " +
                " c.Scale as ColumnScale,c.[default] as DefaultValue, " +
                " c.max_identity as MaxIdentity,c.column_type as ColumnType, " +
                " d.domain_name as ColumnDBType, u.type_name as UDT_Name,  " +
                " if dxc.sequence is null then 'N' else 'Y' endif as IsPK, " +
                " if c.[default] = 'autoincrement' or c.[default] = 'globalautoincrement' then 'Y' else 'N' endif as IsIdentity " +
                " from systabcol c " +
                " join systab t on t.table_id = c.table_id  " +
                " join sysdomain d on c.domain_id=d.domain_id " +
                " left join sysidxcol dxc on c.table_id=dxc.table_id and c.column_id=dxc.column_id and dxc.index_id =0 " +
                " left join sysusertype u on c.user_type=u.type_id " +
                " where t.table_name = " ;

Instead of checking against MaxIdentity which is not always work, i check for autoincrement

if c.[default] = 'autoincrement' or c.[default] = 'globalautoincrement' then 'Y' else 'N' endif as IsIdentity "

Thanks

Thanks for the feedback simple_smile I'm not sure how sybase stores the identity column info though. autoincrement defaults is a way but I wouldnt be surprised if there are two ways (so that it might fail as well). Is autoincrement always filled if a column is an identity?

Frans Bouma | Lead developer LLBLGen Pro
sunnyman
User
Posts: 51
Joined: 21-Feb-2007
# Posted on: 03-Jun-2007 12:37:28   

Sorry for late, I was out of my town and just arrive.

I think Sybase Support Batch Query because I can use them from their Interactive SQL (Built in program to execute sql against db), I think the problem may be in output parameters syntax.

To solve the problem I use sequence queries exactly like you explain.

Autoincrement in Sybase works exactly the same as identity in other dbs

What I mean by the second problem is that I use a systabcol.MaxIdentity in query inside the driver code to determine if the column is identity or not, which is not always correct

So I change it to check the systabcol.defualt which is always correct and have not problem, just for info

Thanks.