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.

ASA 9.0.2 and set option quoted_identifier

11 posts in General Discussion Last posting was on 2008-11-14 03:29:20.0Z
Arnd Schmidt Posted on 2008-11-05 20:45:12.0Z
From: Arnd Schmidt <arnd.schmidt@gmx.de>
Newsgroups: sybase.public.workspace.general
Subject: ASA 9.0.2 and set option quoted_identifier
Message-ID: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 5 Nov 2008 12:45:12 -0800
X-Trace: forums-1-dub 1225917912 10.22.241.152 (5 Nov 2008 12:45:12 -0800)
X-Original-Trace: 5 Nov 2008 12:45:12 -0800, vip152.sybase.com
Lines: 79
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:753
Article PK: 1088425

Hi all,

we are using Sybase Adaptive Server Anywhere 9.0.2 and also PowerBuilder 9.0.3.
The default Option for QUOTED_IDENTIFIER is ON.

Now, after I execute some SQL in Workspace 2.0.2 a set OPTION quoted_identifier=off" is automatically executed.

When I start the PB Application with the default Option for DelimitIdentifier='yes' some Datawindow's get DB Errors during retrieve.

"Select Error: SQLSTATE = 37000
Syntax Error near...blah blah"

That's correct, because now the generated SQL does not work against a database where the option quoted_identifier for the user is set to 'off'.

I already set the 'Quoted Identifier' Option in the 'Connection Level Option' in Eclipse for the 'ASA 9 X' and it seems as the option is set before the SQL is executed.
But after the execute, the option is set to 'off'. Why?

Is there any chance to correct this behaviour?

I am clueless. Help!


TIA

Arnd


This is a part of the log for the Database:

11/04 14:13:54.431 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.431 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "commit"
11/04 14:13:54.432 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.432 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION ansi_integer_overflow=off"
11/04 14:13:54.432 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.432 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION divide_by_zero_error=on"
11/04 14:13:54.432 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.432 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION chained=off"
11/04 14:13:54.433 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION isolation_level=1"
11/04 14:13:54.433 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION ansinull=off"
11/04 14:13:54.433 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION quoted_identifier=on"
11/04 14:13:54.451 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.451 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION on_tsql_error='conditional'"
11/04 14:13:54.451 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.452 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "if exists ( select 1 from sysobjects where sysobjects.name = 'fib_buchrsks_gendata' ) drop
function fib_buchrsks_gendata "
....

11/04 14:13:54.495 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.500 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "sp_mda"
11/04 14:13:54.500 ** HOSTVAR conn: 97 0 int '0'
11/04 14:13:54.500 ** HOSTVAR conn: 97 1 int '7'
11/04 14:13:54.500 ** WARNING conn: 97 code: 100 "Zeile nicht gefunden"
11/04 14:13:54.500 ** WARNING conn: 97 code: 105 "Prozedur ist abgeschlossen"
11/04 14:13:54.500 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.501 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "select @@tranchained"
11/04 14:13:54.501 ** WARNING conn: 97 code: 100 "Zeile nicht gefunden"
11/04 14:13:54.501 ** WARNING conn: 97 code: 105 "Prozedur ist abgeschlossen"
11/04 14:13:54.501 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.501 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "commit"
11/04 14:13:54.501 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.501 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION ansi_integer_overflow=off"
11/04 14:13:54.501 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION divide_by_zero_error=on"
11/04 14:13:54.502 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION chained=off"
11/04 14:13:54.502 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION isolation_level=1"
11/04 14:13:54.502 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION ansinull=off"
11/04 14:13:54.503 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.503 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION quoted_identifier=off"
11/04 14:13:54.507 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
11/04 14:13:54.508 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION on_tsql_error='conditional'"


Li huang [Sybase] Posted on 2008-11-10 08:02:02.0Z
Reply-To: "Li Huang [Sybase]" <lihuang@sybase.com>
From: "Li Huang [Sybase]" <lihuang@sybase.com>
Newsgroups: sybase.public.workspace.general
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com>
Subject: Re: ASA 9.0.2 and set option quoted_identifier
Lines: 135
Organization: Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4917ea7a@forums-1-dub>
Date: 10 Nov 2008 00:02:02 -0800
X-Trace: forums-1-dub 1226304122 10.22.241.152 (10 Nov 2008 00:02:02 -0800)
X-Original-Trace: 10 Nov 2008 00:02:02 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:755
Article PK: 1088426

Hi, Arnd:

When you use WorkSpace connect to ASA, the option 'quoted_identifier' which
is set in 'Connection Level Option' is only valid for the connection created
by WorkSpace.
Thus, the option 'quoted_identifier' in PowerBuilder is not changed by
WorkSpace.

And in WorkSpace, before your SQLs are executed, the options in 'Connection
Level Option' will be executed first. After the execute, those options will
be reset for the connection created by WorkSpace.

Hope it can solve your issue.

--
Thanks and regards,

Li Huang [Sybase]

Sybase Software (China) Co., Ltd
Room 1202-1203, Building One,
Zhangjiang Semiconductor Industry Park
3000 Longdong Avenue
Pudong, Shanghai 201203
Email: lihuang@sybase.com

"Arnd Schmidt" <arnd.schmidt@gmx.de> wrote in message
news:7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com...
> Hi all,
>
> we are using Sybase Adaptive Server Anywhere 9.0.2 and also PowerBuilder
> 9.0.3.
> The default Option for QUOTED_IDENTIFIER is ON.
>
> Now, after I execute some SQL in Workspace 2.0.2 a set OPTION
> quoted_identifier=off" is automatically executed.
>
> When I start the PB Application with the default Option for
> DelimitIdentifier='yes' some Datawindow's get DB Errors during retrieve.
>
> "Select Error: SQLSTATE = 37000
> Syntax Error near...blah blah"
>
> That's correct, because now the generated SQL does not work against a
> database where the option quoted_identifier for the user is set to 'off'.
>
> I already set the 'Quoted Identifier' Option in the 'Connection Level
> Option' in Eclipse for the 'ASA 9 X' and it seems as the option is set
> before the SQL is executed.
> But after the execute, the option is set to 'off'. Why?
>
> Is there any chance to correct this behaviour?
>
> I am clueless. Help!
>
>
> TIA
>
> Arnd
>
>
> This is a part of the log for the Database:
>
> 11/04 14:13:54.431 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.431 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "commit"
> 11/04 14:13:54.432 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.432 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION ansi_integer_overflow=off"
> 11/04 14:13:54.432 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.432 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION divide_by_zero_error=on"
> 11/04 14:13:54.432 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.432 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION chained=off"
> 11/04 14:13:54.433 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION isolation_level=1"
> 11/04 14:13:54.433 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION ansinull=off"
> 11/04 14:13:54.433 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION quoted_identifier=on"
> 11/04 14:13:54.451 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.451 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION on_tsql_error='conditional'"
> 11/04 14:13:54.451 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.452 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "if
> exists ( select 1 from sysobjects where sysobjects.name =
> 'fib_buchrsks_gendata' ) drop
> function fib_buchrsks_gendata "
> ....
>
> 11/04 14:13:54.495 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.500 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "sp_mda"
> 11/04 14:13:54.500 ** HOSTVAR conn: 97 0 int '0'
> 11/04 14:13:54.500 ** HOSTVAR conn: 97 1 int '7'
> 11/04 14:13:54.500 ** WARNING conn: 97 code: 100 "Zeile nicht gefunden"
> 11/04 14:13:54.500 ** WARNING conn: 97 code: 105 "Prozedur ist
> abgeschlossen"
> 11/04 14:13:54.500 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.501 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "select
> @@tranchained"
> 11/04 14:13:54.501 ** WARNING conn: 97 code: 100 "Zeile nicht gefunden"
> 11/04 14:13:54.501 ** WARNING conn: 97 code: 105 "Prozedur ist
> abgeschlossen"
> 11/04 14:13:54.501 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.501 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "commit"
> 11/04 14:13:54.501 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.501 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION ansi_integer_overflow=off"
> 11/04 14:13:54.501 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION divide_by_zero_error=on"
> 11/04 14:13:54.502 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION chained=off"
> 11/04 14:13:54.502 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION isolation_level=1"
> 11/04 14:13:54.502 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.502 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION ansinull=off"
> 11/04 14:13:54.503 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.503 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION quoted_identifier=off"
> 11/04 14:13:54.507 ** DONE conn: 97 STMT_EXECUTE_ANY_IMM
> 11/04 14:13:54.508 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set
> OPTION on_tsql_error='conditional'"
>
>
>


Arnd Schmidt Posted on 2008-11-10 20:21:39.0Z
From: Arnd Schmidt <arnd.schmidt@gmx.de>
Newsgroups: sybase.public.workspace.general
Subject: Re: ASA 9.0.2 and set option quoted_identifier
Message-ID: <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com>
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 10 Nov 2008 12:21:39 -0800
X-Trace: forums-1-dub 1226348499 10.22.241.152 (10 Nov 2008 12:21:39 -0800)
X-Original-Trace: 10 Nov 2008 12:21:39 -0800, vip152.sybase.com
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:759
Article PK: 1088431

Hi Li,

I am still confused.

My interpretation of the Sybase ASA 9.0.2 "SET OPTION statement" is,
that this is a user specific permanent option and will also be used the next time, the user connects.
That's why the PB Datawindow fails.

To set an option for a connection's life time, I understand, that using "set temporary option" is the right way.


From the log...
--------------------
11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION quoted_identifier=on"

Executing my SQL-Statement

11/04 14:13:54.503 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION quoted_identifier=off"
-------
Where comes the information to "set OPTION quoted_indentifier=off" from?

I there any chance to change this in the Database SQL Anywhere Connection Profile?
Is there any documentation about the "Other Properties" List possibiliites.
Which properties can be set for ASA 9.0.2? I can not find them :-(

Can I manipulate anything in the jcatalog.sql ?!

Maybe also, that it is a site effect, that I am developing with a user that has DBA rights - and this is also the 'technical user'
that connects to the DB during a PB Run.

I know this is a high potential security risk, but I am forced to use this setup :-(

Issue is not solved yet :-(

Arnd

On 10 Nov 2008 00:02:02 -0800, "Li Huang [Sybase]" <lihuang@sybase.com> wrote:

>Hi, Arnd:
>
>When you use WorkSpace connect to ASA, the option 'quoted_identifier' which
>is set in 'Connection Level Option' is only valid for the connection created
>by WorkSpace.
>Thus, the option 'quoted_identifier' in PowerBuilder is not changed by
>WorkSpace.
>
>And in WorkSpace, before your SQLs are executed, the options in 'Connection
>Level Option' will be executed first. After the execute, those options will
>be reset for the connection created by WorkSpace.
>
>Hope it can solve your issue.


Li huang [Sybase] Posted on 2008-11-11 09:57:38.0Z
Message-ID: <4919570B.1010505@sybase.com>
From: "Li huang [Sybase]" <lihuang@sybase.com>
Reply-To: lihuang@sybase.com
Organization: Sybase
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
To: Arnd Schmidt <arnd.schmidt@gmx.de>
Subject: Re: ASA 9.0.2 and set option quoted_identifier
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com>
In-Reply-To: <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com>
Content-Type: multipart/mixed; boundary="------------050107080200050903060303"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 11 Nov 2008 01:57:38 -0800
X-Trace: forums-1-dub 1226397458 10.22.241.152 (11 Nov 2008 01:57:38 -0800)
X-Original-Trace: 11 Nov 2008 01:57:38 -0800, vip152.sybase.com
Lines: 1273
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:763
Article PK: 1088435


Arnd Schmidt wrote:
> Hi Li,
>
> I am still confused.
>
> My interpretation of the Sybase ASA 9.0.2 "SET OPTION statement" is,
> that this is a user specific permanent option and will also be used the next time, the user connects.
> That's why the PB Datawindow fails.
>
> To set an option for a connection's life time, I understand, that using "set temporary option" is the right way.
>
>
> From the log...
> --------------------
> 11/04 14:13:54.433 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION quoted_identifier=on"
>
> Executing my SQL-Statement
>
> 11/04 14:13:54.503 ** REQUEST conn: 97 STMT_EXECUTE_ANY_IMM "set OPTION quoted_identifier=off"
> -------
> Where comes the information to "set OPTION quoted_indentifier=off" from?
>
> I there any chance to change this in the Database SQL Anywhere Connection Profile?
> Is there any documentation about the "Other Properties" List possibiliites.
> Which properties can be set for ASA 9.0.2? I can not find them :-(
>
> Can I manipulate anything in the jcatalog.sql ?!
>
> Maybe also, that it is a site effect, that I am developing with a user that has DBA rights - and this is also the 'technical user'
> that connects to the DB during a PB Run.
>
> I know this is a high potential security risk, but I am forced to use this setup :-(
>
> Issue is not solved yet :-(
>
> Arnd
>
>
> On 10 Nov 2008 00:02:02 -0800, "Li Huang [Sybase]" <lihuang@sybase.com> wrote:
>
>> Hi, Arnd:
>>
>> When you use WorkSpace connect to ASA, the option 'quoted_identifier' which
>> is set in 'Connection Level Option' is only valid for the connection created
>> by WorkSpace.
>> Thus, the option 'quoted_identifier' in PowerBuilder is not changed by
>> WorkSpace.
>>
>> And in WorkSpace, before your SQLs are executed, the options in 'Connection
>> Level Option' will be executed first. After the execute, those options will
>> be reset for the connection created by WorkSpace.
>>
>> Hope it can solve your issue.
>

As a workaround, can you set quoted_identifier option in PB so every
execution is on in PB?
BTW, you can see and edit database options from Sybase Central. Right
click on asa database node and select 'Options'. See the attached image.

--
Thanks and regards,

Daniel(Li) Huang [Sybase]

Sybase
Sybase Software (China) Co., Ltd
Room 1202-1203, Building One,
Zhangjiang Semiconductor Industry Park
3000 Longdong Avenue
Pudong, Shanghai 201203
Tel 8621-38657420
Email: lihuang@sybase.com

sc.JPG

Arnd Schmidt Posted on 2008-11-11 21:06:59.0Z
From: Arnd Schmidt <arnd.schmidt@gmx.de>
Newsgroups: sybase.public.workspace.general
Subject: Re: ASA 9.0.2 and set option quoted_identifier
Message-ID: <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com>
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com> <4919570B.1010505@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 11 Nov 2008 13:06:59 -0800
X-Trace: forums-1-dub 1226437619 10.22.241.152 (11 Nov 2008 13:06:59 -0800)
X-Original-Trace: 11 Nov 2008 13:06:59 -0800, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:766
Article PK: 1088439

Hi Li,

I know about the "Options.." dialog in ASA 9.0 - but it seems to be useless:-(
The dialog shows the database's options.


During the run, the display of the option does not change, although the
option changed for the user - this is my experience.

Setting back the option only works using ISQL.

Believe it or not, setting the option for the user after connectiing during a run with PB is
not accepted by my current principal - he want me to fix this "issue".

Furthermore I was blamed, about using a tool that "corrupts" the database - "chief
PB developer" had problems using the developtment and test DB and I was more
than surprised and did not knew what caused the ASA 37000 Error.

The only information I could give was, that I installed the jcatalog.sql script and used
Sybase Workspace. I sweared not to be the guy manipulating or make changes to
the database without remembering what I did.

So the acceptence to use Sybase Workspace is really sceptical now at this company.

Also because I spent extra hours of time to find out what in depth is a going on.

So actually I use a separate DB server and changed my local pbodb90.ini.

For deployment I build separate batch commands using ISQL..
Using Sybase Workbench against the "public" test or development DB is to risky.

I hoped for a little configuration secret, where I can customize the "set option" statements.

So do you think there is a chance that this behaviour will be changed in the near future?

Arnd

On 11 Nov 2008 01:57:38 -0800, "Li huang [Sybase]" <lihuang@sybase.com> wrote:

>As a workaround, can you set quoted_identifier option in PB so every
>execution is on in PB?
>BTW, you can see and edit database options from Sybase Central. Right
>click on asa database node and select 'Options'. See the attached image.


Li huang [Sybase] Posted on 2008-11-12 10:35:09.0Z
Message-ID: <491AB151.6090001@sybase.com>
From: "Li huang [Sybase]" <lihuang@sybase.com>
Reply-To: lihuang@sybase.com
Organization: Sybase
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
To: Arnd Schmidt <arnd.schmidt@gmx.de>
Subject: Re: ASA 9.0.2 and set option quoted_identifier
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com> <4919570B.1010505@sybase.com> <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com>
In-Reply-To: <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com>
Content-Type: multipart/mixed; boundary="------------000101080900060900050805"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 12 Nov 2008 02:35:09 -0800
X-Trace: forums-1-dub 1226486109 10.22.241.152 (12 Nov 2008 02:35:09 -0800)
X-Original-Trace: 12 Nov 2008 02:35:09 -0800, vip152.sybase.com
Lines: 2137
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:768
Article PK: 1088444


Arnd Schmidt wrote:
> Hi Li,
>
> I know about the "Options.." dialog in ASA 9.0 - but it seems to be useless:-(
> The dialog shows the database's options.
>
>
> During the run, the display of the option does not change, although the
> option changed for the user - this is my experience.
>
> Setting back the option only works using ISQL.
>
> Believe it or not, setting the option for the user after connectiing during a run with PB is
> not accepted by my current principal - he want me to fix this "issue".
>
> Furthermore I was blamed, about using a tool that "corrupts" the database - "chief
> PB developer" had problems using the developtment and test DB and I was more
> than surprised and did not knew what caused the ASA 37000 Error.
>
> The only information I could give was, that I installed the jcatalog.sql script and used
> Sybase Workspace. I sweared not to be the guy manipulating or make changes to
> the database without remembering what I did.
>
> So the acceptence to use Sybase Workspace is really sceptical now at this company.
>
> Also because I spent extra hours of time to find out what in depth is a going on.
>
> So actually I use a separate DB server and changed my local pbodb90.ini.
>
> For deployment I build separate batch commands using ISQL..
> Using Sybase Workbench against the "public" test or development DB is to risky.
>
> I hoped for a little configuration secret, where I can customize the "set option" statements.
>
> So do you think there is a chance that this behaviour will be changed in the near future?
>
> Arnd
>
>
> On 11 Nov 2008 01:57:38 -0800, "Li huang [Sybase]" <lihuang@sybase.com> wrote:
>
>> As a workaround, can you set quoted_identifier option in PB so every
>> execution is on in PB?
>> BTW, you can see and edit database options from Sybase Central. Right
>> click on asa database node and select 'Options'. See the attached image.
>

Hi, Arnd:

I am trying to reproduce your problems.
The reproduce steps I try is below:
1. In WorkSpace, execute SQL.
2. In PowerBuilder, create a ASA profile under ODB ODBC to connect the
ASA database and set DelimitIdentifier='yes'
create a DataWindow quickly list table content
Run the DataWindow, and it works fine

I am not sure are these steps can match your situation? If possible,
please send me more details(steps and scripts) to identify the problem.
Thanks.

And maybe you can do a simple test for quoted_identifier status, see the
attached image. First run some SQLs in WorkSpace, then in PB, run
'select connection_property('quoted_identifier');' and the status will
be returned from server. Please let me know the quoted_identifier status
after you do the test, so that we can figure out the issue quickly.

--
Thanks and regards,

Daniel(Li) Huang [Sybase]

Sybase Software (China) Co., Ltd
Room 1202-1203, Building One,
Zhangjiang Semiconductor Industry Park
3000 Longdong Avenue
Pudong, Shanghai 201203
Tel 8621-38657420
Email: lihuang@sybase.com

PB.JPG

Arnd Schmidt Posted on 2008-11-12 21:02:28.0Z
From: Arnd Schmidt <arnd.schmidt@gmx.de>
Newsgroups: sybase.public.workspace.general
Subject: Re: ASA 9.0.2 and set option quoted_identifier
Message-ID: <s7hmh4tlsjun692htth0cpfvu8299vevle@4ax.com>
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com> <4919570B.1010505@sybase.com> <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com> <491AB151.6090001@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 12 Nov 2008 13:02:28 -0800
X-Trace: forums-1-dub 1226523748 10.22.241.152 (12 Nov 2008 13:02:28 -0800)
X-Original-Trace: 12 Nov 2008 13:02:28 -0800, vip152.sybase.com
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:769
Article PK: 1088442

Hi Li,

great that you still hold the line...

This is a way how you can reprodure the misbehaviour - you do not need a Datawindow.
It's ok to use a 'mixed kind 'of SQL Statement, see below...


1. Open Powerbuilder's Database Painter and connect to asademo.db
In Powerbuilder's Interactive SQL View execute:

SELECT connection_property('quoted_identifier');
SELECT "customer"."fname" FROM "customer" where customer.id = 101;

(If you get an error here, your quoted_identifer property was already 'off')

2. ) Start Workspce, Connect to DB and execute some SQL like

SELECT count(*) from customer;

3. ) Switch to PowerBuilder, do a disconnect.
4. ) In Power do a fresh connect to the database again.
Excecute the 2 Statements again:

SELECT connection_property('quoted_identifier');
SELECT "customer"."fname" FROM "customer" where customer.id = 101;

Do you get the error now?


Even more ..

It's like the 42000er error in the Interactive SQL started from Sybase Central
when you execute

set option quoted_identifier = on;
SELECT "customer"."fname" FROM "customer" where customer.id = 101;

(OK)

set option quoted_identifier = off;
SELECT "customer"."fname" FROM "customer" where customer.id = 101;
(42000)


Arnd

On 12 Nov 2008 02:35:09 -0800, "Li huang [Sybase]" <lihuang@sybase.com> wrote:

>Hi, Arnd:
>
>I am trying to reproduce your problems.
>The reproduce steps I try is below:
>1. In WorkSpace, execute SQL.
>2. In PowerBuilder, create a ASA profile under ODB ODBC to connect the
>ASA database and set DelimitIdentifier='yes'
> create a DataWindow quickly list table content
> Run the DataWindow, and it works fine
>
>I am not sure are these steps can match your situation? If possible,
>please send me more details(steps and scripts) to identify the problem.
>Thanks.
>
>And maybe you can do a simple test for quoted_identifier status, see the
> attached image. First run some SQLs in WorkSpace, then in PB, run
>'select connection_property('quoted_identifier');' and the status will
>be returned from server. Please let me know the quoted_identifier status
>after you do the test, so that we can figure out the issue quickly.


Li huang [Sybase] Posted on 2008-11-13 07:59:54.0Z
Message-ID: <491BDE79.3090702@sybase.com>
From: "Li huang [Sybase]" <lihuang@sybase.com>
Reply-To: lihuang@sybase.com
Organization: Sybase
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
To: Arnd Schmidt <arnd.schmidt@gmx.de>
Subject: Re: ASA 9.0.2 and set option quoted_identifier
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com> <4919570B.1010505@sybase.com> <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com> <491AB151.6090001@sybase.com> <s7hmh4tlsjun692htth0cpfvu8299vevle@4ax.com>
In-Reply-To: <s7hmh4tlsjun692htth0cpfvu8299vevle@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 12 Nov 2008 23:59:54 -0800
X-Trace: forums-1-dub 1226563194 10.22.241.152 (12 Nov 2008 23:59:54 -0800)
X-Original-Trace: 12 Nov 2008 23:59:54 -0800, vip152.sybase.com
Lines: 99
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:770
Article PK: 1088443


Arnd Schmidt wrote:
> Hi Li,
>
> great that you still hold the line...
>
> This is a way how you can reprodure the misbehaviour - you do not need a Datawindow.
> It's ok to use a 'mixed kind 'of SQL Statement, see below...
>
>
> 1. Open Powerbuilder's Database Painter and connect to asademo.db
> In Powerbuilder's Interactive SQL View execute:
>
> SELECT connection_property('quoted_identifier');
> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>
> (If you get an error here, your quoted_identifer property was already 'off')
>
> 2. ) Start Workspce, Connect to DB and execute some SQL like
>
> SELECT count(*) from customer;
>
> 3. ) Switch to PowerBuilder, do a disconnect.
> 4. ) In Power do a fresh connect to the database again.
> Excecute the 2 Statements again:
>
> SELECT connection_property('quoted_identifier');
> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>
> Do you get the error now?
>
>
> Even more ..
>
> It's like the 42000er error in the Interactive SQL started from Sybase Central
> when you execute
>
> set option quoted_identifier = on;
> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>
> (OK)
>
> set option quoted_identifier = off;
> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
> (42000)
>
>
> Arnd
>
> On 12 Nov 2008 02:35:09 -0800, "Li huang [Sybase]" <lihuang@sybase.com> wrote:
>
>> Hi, Arnd:
>>
>> I am trying to reproduce your problems.
>> The reproduce steps I try is below:
>> 1. In WorkSpace, execute SQL.
>> 2. In PowerBuilder, create a ASA profile under ODB ODBC to connect the
>> ASA database and set DelimitIdentifier='yes'
>> create a DataWindow quickly list table content
>> Run the DataWindow, and it works fine
>>
>> I am not sure are these steps can match your situation? If possible,
>> please send me more details(steps and scripts) to identify the problem.
>> Thanks.
>>
>> And maybe you can do a simple test for quoted_identifier status, see the
>> attached image. First run some SQLs in WorkSpace, then in PB, run
>> 'select connection_property('quoted_identifier');' and the status will
>> be returned from server. Please let me know the quoted_identifier status
>> after you do the test, so that we can figure out the issue quickly.
>

Hi, Arnd:

I followed your steps and got the same problems.
The reason is that ASA ODBC driver just use the default options from
server. And it seems that only ASA ODBC driver doesn't set some options
when connected. JDBC driver and Interactive SQL both set
quoted_identifier to 'on' when connected.
Really thank you for your useful feedbacks and I apologize for the
inconvenience.
This is a bug of WorkSpace and will be fixed in the next release.

Here is the suggested workaround way:
In PowerBuilder, if you use ODBC, can you set quoted_identifier to on or
use JDBC driver instead.

Please let me know if you have any problem.

--
Thanks and regards,

Daniel(Li) Huang [Sybase]

Sybase Software (China) Co., Ltd
Room 1202-1203, Building One,
Zhangjiang Semiconductor Industry Park
3000 Longdong Avenue
Pudong, Shanghai 201203
Tel 8621-38657420
Email: lihuang@sybase.com


Li huang [Sybase] Posted on 2008-11-13 09:13:02.0Z
Message-ID: <491BEF9C.2020604@sybase.com>
From: "Li huang [Sybase]" <lihuang@sybase.com>
Reply-To: lihuang@sybase.com
Organization: Sybase
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
To: lihuang@sybase.com
CC: Arnd Schmidt <arnd.schmidt@gmx.de>
Subject: Re: ASA 9.0.2 and set option quoted_identifier
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com> <4919570B.1010505@sybase.com> <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com> <491AB151.6090001@sybase.com> <s7hmh4tlsjun692htth0cpfvu8299vevle@4ax.com> <491BDE79.3090702@sybase.com>
In-Reply-To: <491BDE79.3090702@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 13 Nov 2008 01:13:02 -0800
X-Trace: forums-1-dub 1226567582 10.22.241.152 (13 Nov 2008 01:13:02 -0800)
X-Original-Trace: 13 Nov 2008 01:13:02 -0800, vip152.sybase.com
Lines: 117
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:771
Article PK: 1088441


Li huang [Sybase] wrote:
> Arnd Schmidt wrote:
>> Hi Li,
>>
>> great that you still hold the line...
>>
>> This is a way how you can reprodure the misbehaviour - you do not need
>> a Datawindow.
>> It's ok to use a 'mixed kind 'of SQL Statement, see below...
>>
>>
>> 1. Open Powerbuilder's Database Painter and connect to asademo.db
>> In Powerbuilder's Interactive SQL View execute:
>>
>> SELECT connection_property('quoted_identifier');
>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>
>> (If you get an error here, your quoted_identifer property was already
>> 'off')
>>
>> 2. ) Start Workspce, Connect to DB and execute some SQL like
>>
>> SELECT count(*) from customer;
>>
>> 3. ) Switch to PowerBuilder, do a disconnect.
>> 4. ) In Power do a fresh connect to the database again.
>> Excecute the 2 Statements again:
>>
>> SELECT connection_property('quoted_identifier');
>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>
>> Do you get the error now?
>>
>>
>> Even more ..
>>
>> It's like the 42000er error in the Interactive SQL started from
>> Sybase Central
>> when you execute
>> set option quoted_identifier = on;
>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>
>> (OK)
>>
>> set option quoted_identifier = off;
>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>> (42000)
>>
>>
>> Arnd
>>
>> On 12 Nov 2008 02:35:09 -0800, "Li huang [Sybase]"
>> <lihuang@sybase.com> wrote:
>>
>>> Hi, Arnd:
>>>
>>> I am trying to reproduce your problems.
>>> The reproduce steps I try is below:
>>> 1. In WorkSpace, execute SQL.
>>> 2. In PowerBuilder, create a ASA profile under ODB ODBC to connect
>>> the ASA database and set DelimitIdentifier='yes'
>>> create a DataWindow quickly list table content
>>> Run the DataWindow, and it works fine
>>>
>>> I am not sure are these steps can match your situation? If possible,
>>> please send me more details(steps and scripts) to identify the
>>> problem. Thanks.
>>>
>>> And maybe you can do a simple test for quoted_identifier status, see
>>> the attached image. First run some SQLs in WorkSpace, then in PB,
>>> run 'select connection_property('quoted_identifier');' and the status
>>> will be returned from server. Please let me know the
>>> quoted_identifier status after you do the test, so that we can figure
>>> out the issue quickly.
>>
> Hi, Arnd:
>
> I followed your steps and got the same problems.
> The reason is that ASA ODBC driver just use the default options from
> server. And it seems that only ASA ODBC driver doesn't set some options
> when connected. JDBC driver and Interactive SQL both set
> quoted_identifier to 'on' when connected.
> Really thank you for your useful feedbacks and I apologize for the
> inconvenience.
> This is a bug of WorkSpace and will be fixed in the next release.
>
> Here is the suggested workaround way:
> In PowerBuilder, if you use ODBC, can you set quoted_identifier to on or
> use JDBC driver instead.
>
> Please let me know if you have any problem.
>

Hi, Arnd:

Another workaround here:
In ASA, create a user only for WorkSpace. This user is never used by any PowerBuilder developer.
As 'set option quoted_identifier' just applies to the currently logged on user ID that issued the SET OPTION statement, other users still work well.
For example, the following statement applies an option change to the user 'user1', if 'user1' is the user issuing the SQL statement:

SET OPTION quoted_identifier = off;

The above way should solve your problems.


--
Thanks and regards,

Daniel(Li) Huang [Sybase]

Sybase Software (China) Co., Ltd
Room 1202-1203, Building One,
Zhangjiang Semiconductor Industry Park
3000 Longdong Avenue
Pudong, Shanghai 201203
Tel 8621-38657420
Email: lihuang@sybase.com


Arnd Schmidt Posted on 2008-11-13 21:20:01.0Z
From: Arnd Schmidt <arnd.schmidt@gmx.de>
Newsgroups: sybase.public.workspace.general
Subject: Re: ASA 9.0.2 and set option quoted_identifier
Message-ID: <uc5ph4l25j208lpppl3dbrtbfiuubok6p3@4ax.com>
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com> <4919570B.1010505@sybase.com> <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com> <491AB151.6090001@sybase.com> <s7hmh4tlsjun692htth0cpfvu8299vevle@4ax.com> <491BDE79.3090702@sybase.com> <491BEF9C.2020604@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 13 Nov 2008 13:20:01 -0800
X-Trace: forums-1-dub 1226611201 10.22.241.152 (13 Nov 2008 13:20:01 -0800)
X-Original-Trace: 13 Nov 2008 13:20:01 -0800, vip152.sybase.com
Lines: 127
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:772
Article PK: 1088440


On 13 Nov 2008 01:13:02 -0800, "Li huang [Sybase]" <lihuang@sybase.com> wrote:

>Li huang [Sybase] wrote:
>> Arnd Schmidt wrote:
>>> Hi Li,
>>>
>>> great that you still hold the line...
>>>
>>> This is a way how you can reprodure the misbehaviour - you do not need
>>> a Datawindow.
>>> It's ok to use a 'mixed kind 'of SQL Statement, see below...
>>>
>>>
>>> 1. Open Powerbuilder's Database Painter and connect to asademo.db
>>> In Powerbuilder's Interactive SQL View execute:
>>>
>>> SELECT connection_property('quoted_identifier');
>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>>
>>> (If you get an error here, your quoted_identifer property was already
>>> 'off')
>>>
>>> 2. ) Start Workspce, Connect to DB and execute some SQL like
>>>
>>> SELECT count(*) from customer;
>>>
>>> 3. ) Switch to PowerBuilder, do a disconnect.
>>> 4. ) In Power do a fresh connect to the database again.
>>> Excecute the 2 Statements again:
>>>
>>> SELECT connection_property('quoted_identifier');
>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>>
>>> Do you get the error now?
>>>
>>>
>>> Even more ..
>>>
>>> It's like the 42000er error in the Interactive SQL started from
>>> Sybase Central
>>> when you execute
>>> set option quoted_identifier = on;
>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>>
>>> (OK)
>>>
>>> set option quoted_identifier = off;
>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>> (42000)
>>>
>>>
>>> Arnd
>>>
>>> On 12 Nov 2008 02:35:09 -0800, "Li huang [Sybase]"
>>> <lihuang@sybase.com> wrote:
>>>
>>>> Hi, Arnd:
>>>>
>>>> I am trying to reproduce your problems.
>>>> The reproduce steps I try is below:
>>>> 1. In WorkSpace, execute SQL.
>>>> 2. In PowerBuilder, create a ASA profile under ODB ODBC to connect
>>>> the ASA database and set DelimitIdentifier='yes'
>>>> create a DataWindow quickly list table content
>>>> Run the DataWindow, and it works fine
>>>>
>>>> I am not sure are these steps can match your situation? If possible,
>>>> please send me more details(steps and scripts) to identify the
>>>> problem. Thanks.
>>>>
>>>> And maybe you can do a simple test for quoted_identifier status, see
>>>> the attached image. First run some SQLs in WorkSpace, then in PB,
>>>> run 'select connection_property('quoted_identifier');' and the status
>>>> will be returned from server. Please let me know the
>>>> quoted_identifier status after you do the test, so that we can figure
>>>> out the issue quickly.
>>>
>> Hi, Arnd:
>>
>> I followed your steps and got the same problems.
>> The reason is that ASA ODBC driver just use the default options from
>> server. And it seems that only ASA ODBC driver doesn't set some options
>> when connected. JDBC driver and Interactive SQL both set
>> quoted_identifier to 'on' when connected.
>> Really thank you for your useful feedbacks and I apologize for the
>> inconvenience.
>> This is a bug of WorkSpace and will be fixed in the next release.
>>
>> Here is the suggested workaround way:
>> In PowerBuilder, if you use ODBC, can you set quoted_identifier to on or
>> use JDBC driver instead.
>>
>> Please let me know if you have any problem.
>>
>
>Hi, Arnd:
>
>Another workaround here:
>In ASA, create a user only for WorkSpace. This user is never used by any PowerBuilder developer.
>As 'set option quoted_identifier' just applies to the currently logged on user ID that issued the SET OPTION statement, other users still work well.
>For example, the following statement applies an option change to the user 'user1', if 'user1' is the user issuing the SQL statement:
>
>SET OPTION quoted_identifier = off;
>
>The above way should solve your problems.

Hi Li,

thank you for all your proposals and I am glad that you found the cause.

Believe it or not, PowerDesigner 9.5.2 (using ODBC) walked into the same trap when I tried to do a reverse engineer on the DB.

PD does a select with "unique" as a columnname :-)

But now, as I am the 'uncrowned king' of 'set option quoted_identifier', this makes me smile ;-)

I will continue developing with the same user, runnig a local db server, so I can leave the test and production DB unchanged.
For PB Development I set the DelimitIdentifer='No' in my locally patched pbodb90.ini.

Setting up a new user might confuse my co-worker...

Thank you for patience.

best regards

Arnd


Li huang [Sybase] Posted on 2008-11-14 03:29:20.0Z
Message-ID: <491CF08E.6020202@sybase.com>
From: "Li huang [Sybase]" <lihuang@sybase.com>
Reply-To: lihuang@sybase.com
Organization: Sybase
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
Newsgroups: sybase.public.workspace.general
To: Arnd Schmidt <arnd.schmidt@gmx.de>
Subject: Re: ASA 9.0.2 and set option quoted_identifier
References: <7ev3h414gnor61eifur1hsqcjmhb544b8f@4ax.com> <4917ea7a@forums-1-dub> <pe6hh499g3hbch8q60so6qrtj6dcdotld9@4ax.com> <4919570B.1010505@sybase.com> <0nqjh4lh8f0blvf1vm2iv94798p1i5mli2@4ax.com> <491AB151.6090001@sybase.com> <s7hmh4tlsjun692htth0cpfvu8299vevle@4ax.com> <491BDE79.3090702@sybase.com> <491BEF9C.2020604@sybase.com> <uc5ph4l25j208lpppl3dbrtbfiuubok6p3@4ax.com>
In-Reply-To: <uc5ph4l25j208lpppl3dbrtbfiuubok6p3@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 13 Nov 2008 19:29:20 -0800
X-Trace: forums-1-dub 1226633360 10.22.241.152 (13 Nov 2008 19:29:20 -0800)
X-Original-Trace: 13 Nov 2008 19:29:20 -0800, vip152.sybase.com
Lines: 143
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:773
Article PK: 1088445


Arnd Schmidt wrote:
> On 13 Nov 2008 01:13:02 -0800, "Li huang [Sybase]" <lihuang@sybase.com> wrote:
>
>> Li huang [Sybase] wrote:
>>> Arnd Schmidt wrote:
>>>> Hi Li,
>>>>
>>>> great that you still hold the line...
>>>>
>>>> This is a way how you can reprodure the misbehaviour - you do not need
>>>> a Datawindow.
>>>> It's ok to use a 'mixed kind 'of SQL Statement, see below...
>>>>
>>>>
>>>> 1. Open Powerbuilder's Database Painter and connect to asademo.db
>>>> In Powerbuilder's Interactive SQL View execute:
>>>>
>>>> SELECT connection_property('quoted_identifier');
>>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>>>
>>>> (If you get an error here, your quoted_identifer property was already
>>>> 'off')
>>>>
>>>> 2. ) Start Workspce, Connect to DB and execute some SQL like
>>>>
>>>> SELECT count(*) from customer;
>>>>
>>>> 3. ) Switch to PowerBuilder, do a disconnect.
>>>> 4. ) In Power do a fresh connect to the database again.
>>>> Excecute the 2 Statements again:
>>>>
>>>> SELECT connection_property('quoted_identifier');
>>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>>>
>>>> Do you get the error now?
>>>>
>>>>
>>>> Even more ..
>>>>
>>>> It's like the 42000er error in the Interactive SQL started from
>>>> Sybase Central
>>>> when you execute
>>>> set option quoted_identifier = on;
>>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>>>
>>>> (OK)
>>>>
>>>> set option quoted_identifier = off;
>>>> SELECT "customer"."fname" FROM "customer" where customer.id = 101;
>>>> (42000)
>>>>
>>>>
>>>> Arnd
>>>>
>>>> On 12 Nov 2008 02:35:09 -0800, "Li huang [Sybase]"
>>>> <lihuang@sybase.com> wrote:
>>>>
>>>>> Hi, Arnd:
>>>>>
>>>>> I am trying to reproduce your problems.
>>>>> The reproduce steps I try is below:
>>>>> 1. In WorkSpace, execute SQL.
>>>>> 2. In PowerBuilder, create a ASA profile under ODB ODBC to connect
>>>>> the ASA database and set DelimitIdentifier='yes'
>>>>> create a DataWindow quickly list table content
>>>>> Run the DataWindow, and it works fine
>>>>>
>>>>> I am not sure are these steps can match your situation? If possible,
>>>>> please send me more details(steps and scripts) to identify the
>>>>> problem. Thanks.
>>>>>
>>>>> And maybe you can do a simple test for quoted_identifier status, see
>>>>> the attached image. First run some SQLs in WorkSpace, then in PB,
>>>>> run 'select connection_property('quoted_identifier');' and the status
>>>>> will be returned from server. Please let me know the
>>>>> quoted_identifier status after you do the test, so that we can figure
>>>>> out the issue quickly.
>>> Hi, Arnd:
>>>
>>> I followed your steps and got the same problems.
>>> The reason is that ASA ODBC driver just use the default options from
>>> server. And it seems that only ASA ODBC driver doesn't set some options
>>> when connected. JDBC driver and Interactive SQL both set
>>> quoted_identifier to 'on' when connected.
>>> Really thank you for your useful feedbacks and I apologize for the
>>> inconvenience.
>>> This is a bug of WorkSpace and will be fixed in the next release.
>>>
>>> Here is the suggested workaround way:
>>> In PowerBuilder, if you use ODBC, can you set quoted_identifier to on or
>>> use JDBC driver instead.
>>>
>>> Please let me know if you have any problem.
>>>
>> Hi, Arnd:
>>
>> Another workaround here:
>> In ASA, create a user only for WorkSpace. This user is never used by any PowerBuilder developer.
>> As 'set option quoted_identifier' just applies to the currently logged on user ID that issued the SET OPTION statement, other users still work well.
>> For example, the following statement applies an option change to the user 'user1', if 'user1' is the user issuing the SQL statement:
>>
>> SET OPTION quoted_identifier = off;
>>
>> The above way should solve your problems.
>
> Hi Li,
>
> thank you for all your proposals and I am glad that you found the cause.
>
> Believe it or not, PowerDesigner 9.5.2 (using ODBC) walked into the same trap when I tried to do a reverse engineer on the DB.
>
> PD does a select with "unique" as a columnname :-)
>
> But now, as I am the 'uncrowned king' of 'set option quoted_identifier', this makes me smile ;-)
>
> I will continue developing with the same user, runnig a local db server, so I can leave the test and production DB unchanged.
> For PB Development I set the DelimitIdentifer='No' in my locally patched pbodb90.ini.
>
> Setting up a new user might confuse my co-worker...
>
> Thank you for patience.
>
> best regards
>
> Arnd
>

Hi, Arnd:

Thank you for your feedbacks about WorkSpace.
If you have any issues or suggestion about WorkSpace, feel free to post your comments! :-)

--
Thanks and regards,

Daniel(Li) Huang [Sybase]

Sybase Software (China) Co., Ltd
Room 1202-1203, Building One,
Zhangjiang Semiconductor Industry Park
3000 Longdong Avenue
Pudong, Shanghai 201203
Tel 8621-38657420
Email: lihuang@sybase.com