"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_cd_sexo
from #TMP_qtde

When I try to execute this procedure the SQL Anywhere give me the
message: "not enough fields allocated in SQLDA"
What's the problem??

Thanks,
Eva Correia.




0
Trends
9/11/1998 1:48:54 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

7 Replies
554 Views

Similar Articles

[PageSpeed] 18

You might want to set up a RESULT set in the declaration of your procedure:

CREATE PROCEDURE test_results()
RESULT ( city CHAR(20) )
BEGIN

CREATE TABLE #temp_table
(tmp_city CHAR(20));

INSERT INTO #temp_table
SELECT city FROM contact;

SELECT tmp_city FROM #temp_table;
END;;

But it worked for me (in ISQL) even without the RESULT clause.  Perhaps
your front end development tool needs the RESULT?
-- 
Jim Egan [TeamPS]
Houston, TX

Trends Tecnologia <trendst@svn.com.br> wrote in article
<35F92A45.618F0430@svn.com.br>...
> 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_cd_sexo
> from #TMP_qtde
> 
> When I try to execute this procedure the SQL Anywhere give me the
> message: "not enough fields allocated in SQLDA"
> What's the problem??
> 
> Thanks,
> Eva Correia.
> 
> 
> 
> 
> 
0
Jim
9/11/1998 2:54:54 PM
Right, except this is TSQL code which wouldnt have the RESULT declaration.  Can
you check in Sybase Central if ASA belives this is WATCOM code or TSQL code?


Dave Wolf
Jaguar Product Team

Jim Egan wrote:

> You might want to set up a RESULT set in the declaration of your procedure:
>
> CREATE PROCEDURE test_results()
> RESULT ( city CHAR(20) )
> BEGIN
>
> CREATE TABLE #temp_table
> (tmp_city CHAR(20));
>
> INSERT INTO #temp_table
> SELECT city FROM contact;
>
> SELECT tmp_city FROM #temp_table;
> END;;
>
> But it worked for me (in ISQL) even without the RESULT clause.  Perhaps
> your front end development tool needs the RESULT?
> --
> Jim Egan [TeamPS]
> Houston, TX
>
> Trends Tecnologia <trendst@svn.com.br> wrote in article
> <35F92A45.618F0430@svn.com.br>...
> > 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_cd_sexo
> > from #TMP_qtde
> >
> > When I try to execute this procedure the SQL Anywhere give me the
> > message: "not enough fields allocated in SQLDA"
> > What's the problem??
> >
> > Thanks,
> > Eva Correia.
> >
> >
> >
> >
> >

0
David
9/11/1998 4:08:06 PM
Well the RESULT is Watcom syntax and the #temp should be considered T-SQL. 
They _shouldn't_ mix but it worked for me.  Normally I would use a DECLARE
TEMPORARY instead of #temp but I wanted to get as close to the posted
problem as possible.

The SA version of SQL Central says that this is Watcom syntax.
-- 
Jim Egan [TeamPS]
Houston, TX

David Wolf <dwolf@sybase.com> wrote in article
<35F94AE6.1AAD745E@sybase.com>...
> Right, except this is TSQL code which wouldnt have the RESULT
declaration.  Can
> you check in Sybase Central if ASA belives this is WATCOM code or TSQL
code?
> 
> 
> Dave Wolf
> Jaguar Product Team
> 
> Jim Egan wrote:
> 
> > You might want to set up a RESULT set in the declaration of your
procedure:
> >
> > CREATE PROCEDURE test_results()
> > RESULT ( city CHAR(20) )
> > BEGIN
> >
> > CREATE TABLE #temp_table
> > (tmp_city CHAR(20));
> >
> > INSERT INTO #temp_table
> > SELECT city FROM contact;
> >
> > SELECT tmp_city FROM #temp_table;
> > END;;
> >
> > But it worked for me (in ISQL) even without the RESULT clause.  Perhaps
> > your front end development tool needs the RESULT?
> > --
> > Jim Egan [TeamPS]
> > Houston, TX
> >
> > Trends Tecnologia <trendst@svn.com.br> wrote in article
> > <35F92A45.618F0430@svn.com.br>...
> > > 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_cd_sexo
> > > from #TMP_qtde
> > >
> > > When I try to execute this procedure the SQL Anywhere give me the
> > > message: "not enough fields allocated in SQLDA"
> > > What's the problem??
> > >
> > > Thanks,
> > > Eva Correia.
> > >
> > >
> > >
> > >
> > >
> 
> 
0
Jim
9/11/1998 6:13:24 PM
I think thats the problem.  ASA thinks this is WATCOM syntax and not TSQL.  Is
there a way to tell ASA this is in fact TSQL code?

Dave Wolf
Jaguar Product Team

Jim Egan wrote:

> Well the RESULT is Watcom syntax and the #temp should be considered T-SQL.
> They _shouldn't_ mix but it worked for me.  Normally I would use a DECLARE
> TEMPORARY instead of #temp but I wanted to get as close to the posted
> problem as possible.
>
> The SA version of SQL Central says that this is Watcom syntax.
> --
> Jim Egan [TeamPS]
> Houston, TX
>
> David Wolf <dwolf@sybase.com> wrote in article
> <35F94AE6.1AAD745E@sybase.com>...
> > Right, except this is TSQL code which wouldnt have the RESULT
> declaration.  Can
> > you check in Sybase Central if ASA belives this is WATCOM code or TSQL
> code?
> >
> >
> > Dave Wolf
> > Jaguar Product Team
> >
> > Jim Egan wrote:
> >
> > > You might want to set up a RESULT set in the declaration of your
> procedure:
> > >
> > > CREATE PROCEDURE test_results()
> > > RESULT ( city CHAR(20) )
> > > BEGIN
> > >
> > > CREATE TABLE #temp_table
> > > (tmp_city CHAR(20));
> > >
> > > INSERT INTO #temp_table
> > > SELECT city FROM contact;
> > >
> > > SELECT tmp_city FROM #temp_table;
> > > END;;
> > >
> > > But it worked for me (in ISQL) even without the RESULT clause.  Perhaps
> > > your front end development tool needs the RESULT?
> > > --
> > > Jim Egan [TeamPS]
> > > Houston, TX
> > >
> > > Trends Tecnologia <trendst@svn.com.br> wrote in article
> > > <35F92A45.618F0430@svn.com.br>...
> > > > 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_cd_sexo
> > > > from #TMP_qtde
> > > >
> > > > When I try to execute this procedure the SQL Anywhere give me the
> > > > message: "not enough fields allocated in SQLDA"
> > > > What's the problem??
> > > >
> > > > Thanks,
> > > > Eva Correia.
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> >

0
David
9/11/1998 7:28:55 PM
Uh, you gotta problem with Watcom syntax? <g>
-- 
Jim Egan [TeamPS]
Houston, TX

0
Jim
9/11/1998 8:35:58 PM
David Wolf wrote:
> 
> I think thats the problem.  ASA thinks this is WATCOM syntax and not TSQL.  Is
> there a way to tell ASA this is in fact TSQL code?

When I upgraded from SA5 to ASA6, my stored procs ceased to work from my
client app, although they continued to run from within the ISQL tool.

ASA6 showed that they were Watcom syntax-- in fact, I found that I had
used a hybrid of TSQL and Watcom.  I rewrote the procedures to use
strictly TSQL (no THEN following IF, use BEGIN and END following an IF
with multiple statements, no semi-colons for statement delimiters,
variables declared using the "@" symbol, and of course no RESULT
statement).  Once I cleaned it all up, they were listed as using TSQL
syntax in Sybase Central, and they then worked from my client app.

I concluded that SA5 was more tolerant of my sloppiness than ASA6 is.

Kathleen
0
Kathleen
9/12/1998 1:22:45 PM
It is true that TSQL adherence is stricter in ASA 6.0 than it was in SQL Any
5.x.




0
Rob
9/30/1998 9:16:14 PM
Reply: