Transact-SQL issues with SQL Anywhere...
Hello,
I am trying to write a stored procedure that I can use in SQL Anywhere AND
in Sybase ASE. I am having compatability issues with Watcom-SQL versus
Transact-SQL. I don't think the following Watcom-SQL procedure can be
written in transact-SQL in such a way so that it can be used in both
environments (ASA and ASE).
WATCOM-SQL:
alter procedure "DBA".sp_RebuildKeyPool_copy()
begin
declare nextid integer;
set nextid=0;
truncate table s_keypool;
for eachdatabaseid as databaseidcursor dynamic scroll cursor for
select database_id as currdatabaseid
from S_KEYPOOL_REMOTE_DB do
set nextid=nextid+1;
for eachtablename as tablenamecursor dynamic scroll cursor for
select table_name_tx as currtable
from S_KEYPOOL_LIMITS do
insert into S_KEYPOOL(table_name_tx,
database_id,key_value_id) values(
currtable,currdatabaseid,nextid) end for end for;
call sp_ReplenishKeyPool()
end
My attempt to convert it to TRANSACT-SQL failed and is as follows:
declare @NextID integer
select @NextID=0
truncate table S_KEYPOOL
declare DatabaseIDCursor dynamic scroll cursor for select
CurrDatabaseID=database_id
from S_KEYPOOL_REMOTE_DB
open DatabaseIDCursor
fetch next DatabaseIDCursor
while(sqlstate<>'02000')
begin
select @NextID=@NextID+1
declare TableNameCursor dynamic scroll cursor for select
CurrTableName=table_name_tx
from S_KEYPOOL_LIMITS
open TableNameCursor
fetch next TableNameCursor
while(sqlstate<>'02000')
begin
insert into S_KEYPOOL(table_name_tx,
database_id,key_value_id) values(
CurrTableName,CurrDatabaseID,@NextID)
fetch next TableNameCursor
end
fetch next DatabaseIDCursor
end
close DatabaseIDCursor
execute sp_ReplenishKeyPool
Does anyone know if the above WATCOM-SQL statement can be converted to a
generic Transact-SQL statement?
Thanks in advance,
Marco Oballa
|
0 |
|
Marco
4/24/1998 8:12:34 PM
sybase.sqlanywhere.general
32637 articles. 
4 followers.
2 Replies
746 Views
Well, here's one thing. Why are you using cursors for this? The issue is that TSQL does not have dynamic scrollable cursors. They only have simple roll-forward cursors. Then again, you're not even doing any dynamic scrolling, so you dont need them. Maybe I'm missing something, and not totally understanding what you want to accomplish, cant this be done through a join rather than stepping through a double nested cursor? Just looks like all you need is an INSERT with a nested SELECT statement. My rule of thumb is avoid cursors like the plague. They are a maintenance chore, and a performance nightmare. In general cursors should only be used when set logic is impossible, or impractical. How are you associating the table names with their id? Simple order? if so AVOID this as well. Without an order by statement, there is no guarntee of the retrieved order. The problems you're having with TSQL are syntactical. TSQL does not have dynamic scrollable cursors. As well, the syntax for fetching is fetch <cursor> into <variable list> You should also be checking @@sqlstatus rather than sqlstate which does not exist on the ASE platform. The TSQL users manual is available online at http://sybooks.sybase.com/ Dave Wolf Sybase Professional Services Marco Oballa wrote: > Hello, > > I am trying to write a stored procedure that I can use in SQL Anywhere AND > in Sybase ASE. I am having compatability issues with Watcom-SQL versus > Transact-SQL. I don't think the following Watcom-SQL procedure can be > written in transact-SQL in such a way so that it can be used in both > environments (ASA and ASE). > > WATCOM-SQL: > > alter procedure "DBA".sp_RebuildKeyPool_copy() > begin > declare nextid integer; > set nextid=0; > truncate table s_keypool; > for eachdatabaseid as databaseidcursor dynamic scroll cursor for > select database_id as currdatabaseid > from S_KEYPOOL_REMOTE_DB do > set nextid=nextid+1; > for eachtablename as tablenamecursor dynamic scroll cursor for > select table_name_tx as currtable > from S_KEYPOOL_LIMITS do > insert into S_KEYPOOL(table_name_tx, > database_id,key_value_id) values( > currtable,currdatabaseid,nextid) end for end for; > call sp_ReplenishKeyPool() > end > > My attempt to convert it to TRANSACT-SQL failed and is as follows: > > declare @NextID integer > select @NextID=0 > truncate table S_KEYPOOL > declare DatabaseIDCursor dynamic scroll cursor for select > CurrDatabaseID=database_id > from S_KEYPOOL_REMOTE_DB > open DatabaseIDCursor > fetch next DatabaseIDCursor > while(sqlstate<>'02000') > begin > select @NextID=@NextID+1 > declare TableNameCursor dynamic scroll cursor for select > CurrTableName=table_name_tx > from S_KEYPOOL_LIMITS > open TableNameCursor > fetch next TableNameCursor > while(sqlstate<>'02000') > begin > insert into S_KEYPOOL(table_name_tx, > database_id,key_value_id) values( > CurrTableName,CurrDatabaseID,@NextID) > fetch next TableNameCursor > end > fetch next DatabaseIDCursor > end > close DatabaseIDCursor > execute sp_ReplenishKeyPool > > Does anyone know if the above WATCOM-SQL statement can be converted to a > generic Transact-SQL statement? > > Thanks in advance, > Marco Oballa
|
|
0 |
|
David
4/25/1998 12:12:06 AM
>> My rule of thumb is avoid cursors like the plague. They are a maintenance chore, and a performance nightmare. In general cursors should only be used when set logic is impossible, or impractical << Yup! -- Jim Egan [TeamPS] Dealer Solutions, LLC Houston, TX
|
|
0 |
|
Jim
4/27/1998 11:49:27 AM
Similar Artilces:
Is SQL Anywhere 5.0 support transaction SQL ?
When I type "sp_who" in ISQL, it display a message "Transact-SQL feature not support". But I can see this in the command "select * from sysobjects" even if I type in server.Is it anything need to setup or else. I would greatly apprepciate if anyone tell me. The ASE procedure sp_who is not supported by SQL Anywhere. A subset of the ASE procedures are supported for compatibility. The complete list is documented in the help file in the section to Transact SQL Compatability /steve -- Stephen Rice Manager Custom Support Sybase Canada Inc. email: sr...
SQL Anywhere, ASA and SQL Anywhere Studio.
Hello, I will want understand the history of the products names SQL Anywhere, ASA and SQL Anywhere Studio. Is an evolution of same product? Previously these exists Sybase SQL Server? Sybase SQL Server is the product what has been involved in project with Microsoft, what result in MS SQL Server? Thanks very much. Renato Cramer. <Renato Cramer> wrote: > I will want understand the history of the products names SQL > Anywhere, ASA and SQL Anywhere Studio. Watcom SQL -> Sybase SQL Anywhere -> Sybase Adaptive Server Anywhere. > Is an evolution of sa...
SQL Anywhere 9 / SQL Anywhere 11
Sybase 9.0.2.3534 Using Mobilink we are looking to synchronize a SQL Anywhere 9.0.2.3534 db with a SQL Anywhere 11 consolidated db, are there certain arguments I need to add to the server and client execution lines? Also, will SQL Anywhere 9 db work with SQL Anywhere 11 db via Remote Servers? Are there special parameters required? Thanks To synchronize a 9.0.2 client with an 11.0.1 MobiLink server you will need to use the -xo switch. -xo switch: http://dcx.sybase.com/index.html#1101en/mlserver_en11/ml-syncserver-xo.html The remainder of the MobiLink switches will be specif...
any difference between sql anywhere and sql anywhere studio
Is there any difference between sql anywhere and sql anywhere studio? Thanks. >Is there any difference between sql anywhere and sql anywhere studio? The answer depends upon the context of the question. Years ago, "sql anywhere" referred to the DBMS that is now known as Adaptive Server Anywhere. Now, "sql anywhere" can mean a general reference to a set of products that includes ASA, as well as some other things. "sql anywhere studio" is a particular bundling of these products. Still, sometimes when people refer to "sql anywhere" t...
SQL Anywhere 11
Hi, SQL Anywhere 11.0.0.59 We're just investigating migrating our SQL 9.0.2 database to 11, we did unload/reload which seemed to go fine, all views got translated successfully, but when we ran it through our application, on several queries we got the error message Database error code = -1099 SQLSTATE = S1000 Transact-SQL outer joins are currently disabled We looked in sysoptions table for tsql_outer_joins parameter (was set to Off), but had no access rights to change parameter from OFF to ON, so we went through Sybase Central - DB options list. We couldn't chang...
Help! Problems migrating transact-sql code to SQL Anywhere 5.5
Hi everyone, We are migrating an application developed to Sybase SQL Server 11.0 to SQL Anywhere 5.5 and, for now, the following SQL Anywhere limitations are killing us: 1. It does not accept rollback in triggers. 2. Raiseerror interrupts triggers execution. 3. Temporary table created in a stored procedure can not return the result rows to clients Does Anyone know how to bypass this SQL Anywhere Transact-SQL limitations?: Does anyone know if ASA SQL Anwwhere 6.0 resolves this problems? Thanks in advance, Eva Correia, Technology Director. >>3. Temporary ta...
"Migrating transact-sql code to SQL Anywhere 5.5"
Hi! I told you before, that I was trying to return data from a temporary table in a stored procedure, and it wasn't working. I'd like to keep the same code to both SGBD's. The procedure's sintaxe is below: create proc teste2 as create table #TMP_qtde ( tmp_nu_matricula char(9) not null, tmp_nm_aluno char(41) not null, tmp_cd_sexo char(1) not null ) insert into #TMP_qtde select aln_nu_matricula, aln_nm_aluno, aln_cd_sexo from ALU_aluno select tmp_nu_matricula, tmp_nm_aluno, tmp...
SQL Anywhere for QNX -> SQL Anywhere for NT
Hello! I have a system (local server with http-server and Sybase SQL Anywhere) on QNX - several cgi-programs written on Watcom C with embedded SQL. Now I want to try to move all programs to Win NT. But I don't have time to entirely rewrite all the programs. Also currently I don't have opportunity to try SQL Anywhere for NT. So before I begin to experiment I want to ask some questions. First of all, I want to try to recompile all my cgi-programs with Watcom C for DOS using SQL Anywhere for NT sql-preprocessor. (btw, I don't use any qnx-specific functions in my programs). ...
Upgrading from SQL Anywhere 8 to SQL Anywhere 11
I am upgrading from SQL Anywhere 8 to SQL Anywhere 11. I am using the "Unload" wizard in Sybase Central. I am choosing the option "Unload and reload into a new database." After following the wizard, the new database does get created BUT it won't load the structure and data from the old database into the newly created database. I get a *** User Authentication Failed *** message. Any ideas? Thank you, Rommel Rommel, I assume you're using the OEM version of our v11 software? In this scenario, the message means that you're running a non-auth...
Having SQL Anywhere 10 and SQL Anywhere 11 on the same machine
Hi Everyone, I have one customer that runs SQL Anywhere 10 and another customer running SQL Anywhere 11. This means I need to run both versions. I do not have the luxury of running multiple VMs on my laptop. I am wondering if anyone out there has experience on how to do this? Appreciate if any info can be shared! Thanks! YM. In article <e486d794-5fa5-4ae4-9f7b- d5e43689608a@w6g2000yqw.googlegroups.com>, yusuf.mai@gmail.com says... > Hi Everyone, > > I have one customer that runs SQL Anywhere 10 and another customer > running SQL Anywhere 11. This mean...
SQL Anywhere and Pervasive SQL
I have a client who is running SQL Anywhere on a Novell 4.11 server. It is being used by a product called The Raiser's Edge. They will be upgrading from AccPac for DOS to AccPac for Windows in the near future and this will require the installation of Pervasive SQL 2000 on the server. Is anyone aware of any compatibility issues surrounding the installation and operation of these two products on the same server? I already answered this question on the SQL Server newsgroup. -- Jim Egan [TeamSybase] Houston, TX http://www.eganomics.com Sybase Developers Network http://sdn.s...
SQL Server or SQL Anywhere ?
I am looking for recommendations/opinions from Sybase types who have use both SQL Server XI (Netware or NT) ad SQL Anyhwere, as a workgroup database server. Our data set (current -> projected near future) is as follows: Single server, maybe SMP in future. Tables: 35 -> 50 Records: 300K -> 2M (per table;large tables only - many are small) DB Space: 500MB -> 2GB (this number may grow if system becomes used for more business processes.) Users: 30 -> 50 (maybe 100 in the far future) Queries: Return 30-200 records on average, occasionally up to 2000. ...
Watcom-SQL or Transact-SQL
In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures and triggers can be "viewed" as either Watcom or T-SQL. If I understand correctly, T-SQL is more transportable, as it is used by Sybase SQl Server, and also to some degree by MS SQL. Is Watcom SQL in place simply for backward compatibility, or are there other advantages? >In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures >and triggers can be "viewed" as either Watcom or T-SQL. If I >understand correctly, T-SQL is more transportable, as it is used by >Sybase S...
SQL Anywhere and Microsoft SQL
I have a server running microsoft SQL for an application. They are adding another software package that uses SQL Anywhere 9. Would there be any problems installing the SQL Anywhere 9 on a server already running Microsoft SQL 2000. <Stephen Taft> wrote in message news:422cd9de.10a5.1681692777@sybase.com... >I have a server running microsoft SQL for an application. > They are adding another software package that uses SQL > Anywhere 9. Would there be any problems installing the SQL > Anywhere 9 on a server already running Microsoft SQL 2000. None. I have both ins...
When I type "sp_who" in ISQL, it display a message "Transact-SQL feature not support". But I can see this in the command "select * from sysobjects" even if I type in server.Is it anything need to setup or else. I would greatly apprepciate if anyone tell me. The ASE procedure sp_who is not supported by SQL Anywhere. A subset of the ASE procedures are supported for compatibility. The complete list is documented in the help file in the section to Transact SQL Compatability /steve -- Stephen Rice Manager Custom Support Sybase Canada Inc. email: sr...
SQL Anywhere, ASA and SQL Anywhere Studio.
Hello, I will want understand the history of the products names SQL Anywhere, ASA and SQL Anywhere Studio. Is an evolution of same product? Previously these exists Sybase SQL Server? Sybase SQL Server is the product what has been involved in project with Microsoft, what result in MS SQL Server? Thanks very much. Renato Cramer. <Renato Cramer> wrote: > I will want understand the history of the products names SQL > Anywhere, ASA and SQL Anywhere Studio. Watcom SQL -> Sybase SQL Anywhere -> Sybase Adaptive Server Anywhere. > Is an evolution of sa...
SQL Anywhere 9 / SQL Anywhere 11
Sybase 9.0.2.3534 Using Mobilink we are looking to synchronize a SQL Anywhere 9.0.2.3534 db with a SQL Anywhere 11 consolidated db, are there certain arguments I need to add to the server and client execution lines? Also, will SQL Anywhere 9 db work with SQL Anywhere 11 db via Remote Servers? Are there special parameters required? Thanks To synchronize a 9.0.2 client with an 11.0.1 MobiLink server you will need to use the -xo switch. -xo switch: http://dcx.sybase.com/index.html#1101en/mlserver_en11/ml-syncserver-xo.html The remainder of the MobiLink switches will be specif...
any difference between sql anywhere and sql anywhere studio
Is there any difference between sql anywhere and sql anywhere studio? Thanks. >Is there any difference between sql anywhere and sql anywhere studio? The answer depends upon the context of the question. Years ago, "sql anywhere" referred to the DBMS that is now known as Adaptive Server Anywhere. Now, "sql anywhere" can mean a general reference to a set of products that includes ASA, as well as some other things. "sql anywhere studio" is a particular bundling of these products. Still, sometimes when people refer to "sql anywhere" t...
SQL Anywhere 11
Hi, SQL Anywhere 11.0.0.59 We're just investigating migrating our SQL 9.0.2 database to 11, we did unload/reload which seemed to go fine, all views got translated successfully, but when we ran it through our application, on several queries we got the error message Database error code = -1099 SQLSTATE = S1000 Transact-SQL outer joins are currently disabled We looked in sysoptions table for tsql_outer_joins parameter (was set to Off), but had no access rights to change parameter from OFF to ON, so we went through Sybase Central - DB options list. We couldn't chang...
Help! Problems migrating transact-sql code to SQL Anywhere 5.5
Hi everyone, We are migrating an application developed to Sybase SQL Server 11.0 to SQL Anywhere 5.5 and, for now, the following SQL Anywhere limitations are killing us: 1. It does not accept rollback in triggers. 2. Raiseerror interrupts triggers execution. 3. Temporary table created in a stored procedure can not return the result rows to clients Does Anyone know how to bypass this SQL Anywhere Transact-SQL limitations?: Does anyone know if ASA SQL Anwwhere 6.0 resolves this problems? Thanks in advance, Eva Correia, Technology Director. >>3. Temporary ta...
"Migrating transact-sql code to SQL Anywhere 5.5"
Hi! I told you before, that I was trying to return data from a temporary table in a stored procedure, and it wasn't working. I'd like to keep the same code to both SGBD's. The procedure's sintaxe is below: create proc teste2 as create table #TMP_qtde ( tmp_nu_matricula char(9) not null, tmp_nm_aluno char(41) not null, tmp_cd_sexo char(1) not null ) insert into #TMP_qtde select aln_nu_matricula, aln_nm_aluno, aln_cd_sexo from ALU_aluno select tmp_nu_matricula, tmp_nm_aluno, tmp...
SQL Anywhere for QNX -> SQL Anywhere for NT
Hello! I have a system (local server with http-server and Sybase SQL Anywhere) on QNX - several cgi-programs written on Watcom C with embedded SQL. Now I want to try to move all programs to Win NT. But I don't have time to entirely rewrite all the programs. Also currently I don't have opportunity to try SQL Anywhere for NT. So before I begin to experiment I want to ask some questions. First of all, I want to try to recompile all my cgi-programs with Watcom C for DOS using SQL Anywhere for NT sql-preprocessor. (btw, I don't use any qnx-specific functions in my programs). ...
Upgrading from SQL Anywhere 8 to SQL Anywhere 11
I am upgrading from SQL Anywhere 8 to SQL Anywhere 11. I am using the "Unload" wizard in Sybase Central. I am choosing the option "Unload and reload into a new database." After following the wizard, the new database does get created BUT it won't load the structure and data from the old database into the newly created database. I get a *** User Authentication Failed *** message. Any ideas? Thank you, Rommel Rommel, I assume you're using the OEM version of our v11 software? In this scenario, the message means that you're running a non-auth...
Having SQL Anywhere 10 and SQL Anywhere 11 on the same machine
Hi Everyone, I have one customer that runs SQL Anywhere 10 and another customer running SQL Anywhere 11. This means I need to run both versions. I do not have the luxury of running multiple VMs on my laptop. I am wondering if anyone out there has experience on how to do this? Appreciate if any info can be shared! Thanks! YM. In article <e486d794-5fa5-4ae4-9f7b- d5e43689608a@w6g2000yqw.googlegroups.com>, yusuf.mai@gmail.com says... > Hi Everyone, > > I have one customer that runs SQL Anywhere 10 and another customer > running SQL Anywhere 11. This mean...
SQL Anywhere and Pervasive SQL
I have a client who is running SQL Anywhere on a Novell 4.11 server. It is being used by a product called The Raiser's Edge. They will be upgrading from AccPac for DOS to AccPac for Windows in the near future and this will require the installation of Pervasive SQL 2000 on the server. Is anyone aware of any compatibility issues surrounding the installation and operation of these two products on the same server? I already answered this question on the SQL Server newsgroup. -- Jim Egan [TeamSybase] Houston, TX http://www.eganomics.com Sybase Developers Network http://sdn.s...
SQL Server or SQL Anywhere ?
I am looking for recommendations/opinions from Sybase types who have use both SQL Server XI (Netware or NT) ad SQL Anyhwere, as a workgroup database server. Our data set (current -> projected near future) is as follows: Single server, maybe SMP in future. Tables: 35 -> 50 Records: 300K -> 2M (per table;large tables only - many are small) DB Space: 500MB -> 2GB (this number may grow if system becomes used for more business processes.) Users: 30 -> 50 (maybe 100 in the far future) Queries: Return 30-200 records on average, occasionally up to 2000. ...
Watcom-SQL or Transact-SQL
In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures and triggers can be "viewed" as either Watcom or T-SQL. If I understand correctly, T-SQL is more transportable, as it is used by Sybase SQl Server, and also to some degree by MS SQL. Is Watcom SQL in place simply for backward compatibility, or are there other advantages? >In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures >and triggers can be "viewed" as either Watcom or T-SQL. If I >understand correctly, T-SQL is more transportable, as it is used by >Sybase S...
SQL Anywhere and Microsoft SQL
I have a server running microsoft SQL for an application. They are adding another software package that uses SQL Anywhere 9. Would there be any problems installing the SQL Anywhere 9 on a server already running Microsoft SQL 2000. <Stephen Taft> wrote in message news:422cd9de.10a5.1681692777@sybase.com... >I have a server running microsoft SQL for an application. > They are adding another software package that uses SQL > Anywhere 9. Would there be any problems installing the SQL > Anywhere 9 on a server already running Microsoft SQL 2000. None. I have both ins...
Web resources about - Transact-SQL issues with SQL Anywhere... - sybase.sqlanywhere.general Transact-SQL - Wikipedia, the free encyclopedia
Transact-SQL ( T-SQL ) is Microsoft 's and Sybase 's proprietary extension to SQL . SQL, the acronym for Structured Query Language, is a standardized ...
Transact-SQL ( T-SQL ) is Microsoft 's and Sybase 's proprietary extension to SQL . SQL, the acronym for Structured Query Language, is a standardized ...
Resources last updated: 12/6/2015 10:21:18 PM