Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Embedded SQL - Getting a single return value

5 posts in General Discussion Last posting was on 2013-03-06 00:38:21.0Z
Bill Beale Posted on 2013-03-04 00:38:11.0Z
Sender: 439b.5133e9eb.1804289383@sybase.com
From: Bill Beale
Newsgroups: sybase.public.powerbuilder.general
Subject: Embedded SQL - Getting a single return value
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5133ecf3.4525.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 3 Mar 2013 16:38:11 -0800
X-Trace: forums-1-dub 1362357491 172.20.134.41 (3 Mar 2013 16:38:11 -0800)
X-Original-Trace: 3 Mar 2013 16:38:11 -0800, 172.20.134.41
Lines: 46
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.general:249790
Article PK: 1308060

I'm doing some work with temporary tables and, in the
development
stage, I'm testing their efficiency by attempting in two
cases to return a single value using dynamic SQL. (I plan
to move the functions now attempted temporarily using
dynamic SQL into stored procedures with more appropriate
code at the next stage)

However, I've got an obvious format problem with the type of
dynamic SQL that I'm testing.

For example, as a test comparison, this embedded SQL works
as would be expected on the database table ...
SELECT COUNT(*) INTO :ll_rows FROM orders USING SQLCA;

This doesn't work on the temporary table, returning error
"Incorrect syntax near O"
PREPARE SQLSA FROM
"SELECT COUNT(*) INTO ? FROM #temp_orders " USING SQLCA;
EXECUTE SQLSA USING :ll_rows;

Likewise, this works with embedded SQL on the database table
..
SELECT MAX(pk_no) INTO :ll_pk_no FROM names WHERE
Upper(family_name) = :as_family_name USING SQLCA;

As with the above example, this doesn't work, returning the
same error, "Incorrect syntax near O"
PREPARE SQLSA FROM
"SELECT MAX(pk_no) INTO ? FROM #temp_names WHERE
Upper(family_name) = ?" USING SQLCA;
EXECUTE SQLSA USING :ll_pk_No, :as_family_name;

The two formats of Dynamic SQL I'm testing obviously do not
work when returning a value into a variable.

All the code example formats I can find for Dynamic SQL 4,
where a value(s) is sought, use cursors.
Cursors have relevance to getting a return of multiple
values but, in the two cases above,
I want a simple query that returns only one value in each
case.

I'm using PB11.2 and a MS SQL Server DB

Any advice would be appreciated


Scott Morris Posted on 2013-03-04 16:53:36.0Z
Content-Type: text/plain; charset=iso-8859-15; format=flowed; delsp=yes
Newsgroups: sybase.public.powerbuilder.general
Subject: Re: Embedded SQL - Getting a single return value
References: <5133ecf3.4525.1681692777@sybase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
From: "Scott Morris" <bogus@abc.def>
Message-ID: <op.wtfk7ls3q59cbq@scott-win7.divinv.net>
User-Agent: Opera Mail/11.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 4 Mar 2013 08:53:36 -0800
X-Trace: forums-1-dub 1362416016 172.20.134.152 (4 Mar 2013 08:53:36 -0800)
X-Original-Trace: 4 Mar 2013 08:53:36 -0800, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.general:249799
Article PK: 1308068


On Sun, 03 Mar 2013 19:38:11 -0500, Bill <Beale> wrote:

> I'm doing some work with temporary tables and, in the
> development
> stage, I'm testing their efficiency by attempting in two
> cases to return a single value using dynamic SQL. (I plan
> to move the functions now attempted temporarily using
> dynamic SQL into stored procedures with more appropriate
> code at the next stage)
>
> However, I've got an obvious format problem with the type of
> dynamic SQL that I'm testing.
>
> For example, as a test comparison, this embedded SQL works
> as would be expected on the database table ...
> SELECT COUNT(*) INTO :ll_rows FROM orders USING SQLCA;
>
> This doesn't work on the temporary table, returning error
> "Incorrect syntax near O"
> PREPARE SQLSA FROM
> "SELECT COUNT(*) INTO ? FROM #temp_orders " USING SQLCA;
> EXECUTE SQLSA USING :ll_rows;
>
> Likewise, this works with embedded SQL on the database table
> ..
> SELECT MAX(pk_no) INTO :ll_pk_no FROM names WHERE
> Upper(family_name) = :as_family_name USING SQLCA;
>
> As with the above example, this doesn't work, returning the
> same error, "Incorrect syntax near O"
> PREPARE SQLSA FROM
> "SELECT MAX(pk_no) INTO ? FROM #temp_names WHERE
> Upper(family_name) = ?" USING SQLCA;
> EXECUTE SQLSA USING :ll_pk_No, :as_family_name;
>
> The two formats of Dynamic SQL I'm testing obviously do not
> work when returning a value into a variable.
>
> All the code example formats I can find for Dynamic SQL 4,
> where a value(s) is sought, use cursors.
> Cursors have relevance to getting a return of multiple
> values but, in the two cases above,
> I want a simple query that returns only one value in each
> case.
>
> I'm using PB11.2 and a MS SQL Server DB

I'll go out on a limb and suspect that PB is attempting to retrieve
information about the table in order to properly retrieve, interpret, and
store the desired expression into the application. Since the temp table
does not actually reside in your current database (i.e., it is resides
tempdb), the technique PB uses to do this fails to achieve its purpose.


Bill Beale Posted on 2013-03-04 20:22:08.0Z
Sender: 4487.513500be.1804289383@sybase.com
From: Bill Beale
Newsgroups: sybase.public.powerbuilder.general
Subject: Re: Embedded SQL - Getting a single return value
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <51350270.454f.1681692777@sybase.com>
References: <op.wtfk7ls3q59cbq@scott-win7.divinv.net>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Mar 2013 12:22:08 -0800
X-Trace: forums-1-dub 1362428528 172.20.134.41 (4 Mar 2013 12:22:08 -0800)
X-Original-Trace: 4 Mar 2013 12:22:08 -0800, 172.20.134.41
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.general:249804
Article PK: 1308077


> On Sun, 03 Mar 2013 19:38:11 -0500, Bill <Beale> wrote:
>
> > I'm doing some work with temporary tables and, in the
> > development
> > stage, I'm testing their efficiency by attempting in two
> > cases to return a single value using dynamic SQL. (I
> > plan to move the functions now attempted temporarily
> > using dynamic SQL into stored procedures with more
> > appropriate code at the next stage)
> >
> > However, I've got an obvious format problem with the
> > type of dynamic SQL that I'm testing.
> >
> > For example, as a test comparison, this embedded SQL
> > works as would be expected on the database table ...
> > SELECT COUNT(*) INTO :ll_rows FROM orders USING SQLCA;
> >
> > This doesn't work on the temporary table, returning
> > error "Incorrect syntax near O"
> > PREPARE SQLSA FROM
> > "SELECT COUNT(*) INTO ? FROM #temp_orders " USING SQLCA;
> > EXECUTE SQLSA USING :ll_rows;
> >
> > Likewise, this works with embedded SQL on the database
> > table ..
> > SELECT MAX(pk_no) INTO :ll_pk_no FROM names WHERE
> > Upper(family_name) = :as_family_name USING SQLCA;
> >
> > As with the above example, this doesn't work, returning
> > the same error, "Incorrect syntax near O"
> > PREPARE SQLSA FROM
> > "SELECT MAX(pk_no) INTO ? FROM #temp_names WHERE
> > Upper(family_name) = ?" USING SQLCA;
> > EXECUTE SQLSA USING :ll_pk_No, :as_family_name;
> >
> > The two formats of Dynamic SQL I'm testing obviously do
> > not work when returning a value into a variable.
> >
> > All the code example formats I can find for Dynamic SQL
> > 4, where a value(s) is sought, use cursors.
> > Cursors have relevance to getting a return of multiple
> > values but, in the two cases above,
> > I want a simple query that returns only one value in
> > each case.
> >
> > I'm using PB11.2 and a MS SQL Server DB
>
> I'll go out on a limb and suspect that PB is attempting to
> retrieve information about the table in order to
> properly retrieve, interpret, and store the desired
> expression into the application. Since the temp table
> does not actually reside in your current database (i.e.,
> it is resides tempdb), the technique PB uses to do this
> fails to achieve its purpose.

Thanks Scott. Following your advice, I tried the dynamic
sql on an existing table, ie
PREPARE SQLSA FROM
"SELECT COUNT(*) INTO ? FROM names" USING SQLCA;
EXECUTE SQLSA USING :ll_rows;

I got the same error - so I presume it is the code of the
dynamic sql I'm using.


Scott Morris Posted on 2013-03-05 15:32:50.0Z
Content-Type: text/plain; charset=iso-8859-15; format=flowed; delsp=yes
Newsgroups: sybase.public.powerbuilder.general
Subject: Re: Embedded SQL - Getting a single return value
References: <op.wtfk7ls3q59cbq@scott-win7.divinv.net> <51350270.454f.1681692777@sybase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
From: "Scott Morris" <bogus@abc.def>
Message-ID: <op.wthb40hkq59cbq@scott-win7.divinv.net>
User-Agent: Opera Mail/11.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 5 Mar 2013 07:32:50 -0800
X-Trace: forums-1-dub 1362497570 172.20.134.152 (5 Mar 2013 07:32:50 -0800)
X-Original-Trace: 5 Mar 2013 07:32:50 -0800, vip152.sybase.com
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.general:249813
Article PK: 1308115

On Mon, 04 Mar 2013 15:22:08 -0500, Bill <Beale> wrote:

> Thanks Scott. Following your advice, I tried the dynamic
> sql on an existing table, ie
> PREPARE SQLSA FROM
> "SELECT COUNT(*) INTO ? FROM names" USING SQLCA;
> EXECUTE SQLSA USING :ll_rows;
>
> I got the same error - so I presume it is the code of the
> dynamic sql I'm using.

I think your approach is doomed for a number of reasons, but that is a
different issue. I'm guessing that this particular choice of syntax may
confuse PB and the DB engine. Sql server provides a <select into>
functionality - something very different from the embedded sql statement
that is supported by PB. If you are going to use dynamic sql, I believe
you will need to generate a resultset in order the capture the desired
information, even if the shaped of the resultset is 1 row by 1 column.
This means that you will not be able to use the embedded form of the
<select into> sql statement.


Bill Beale Posted on 2013-03-06 00:38:21.0Z
Sender: 4caa.51368cf7.1804289383@sybase.com
From: Bill Beale
Newsgroups: sybase.public.powerbuilder.general
Subject: Re: Embedded SQL - Getting a single return value
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <51368ffd.4d9c.1681692777@sybase.com>
References: <op.wthb40hkq59cbq@scott-win7.divinv.net>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 5 Mar 2013 16:38:21 -0800
X-Trace: forums-1-dub 1362530301 172.20.134.41 (5 Mar 2013 16:38:21 -0800)
X-Original-Trace: 5 Mar 2013 16:38:21 -0800, 172.20.134.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.general:249821
Article PK: 1308137


> On Mon, 04 Mar 2013 15:22:08 -0500, Bill <Beale> wrote:
>
>
> > Thanks Scott. Following your advice, I tried the
> > dynamic sql on an existing table, ie
> > PREPARE SQLSA FROM
> > "SELECT COUNT(*) INTO ? FROM names" USING SQLCA;
> > EXECUTE SQLSA USING :ll_rows;
> >
> > I got the same error - so I presume it is the code of
> > the dynamic sql I'm using.
>
> I think your approach is doomed for a number of reasons,
> but that is a different issue. I'm guessing that this
> particular choice of syntax may confuse PB and the DB
> engine. Sql server provides a <select into>
> functionality - something very different from the embedded
> sql statement that is supported by PB. If you are going
> to use dynamic sql, I believe you will need to generate
> a resultset in order the capture the desired information
> , even if the shaped of the resultset is 1 row by 1
> column. This means that you will not be able to use the
> embedded form of the <select into> sql statement.

Thanks again, Scott.