Powerbuilder 7 nested SELECT parameter MS SQL Server 2000 error.

Hello.

I was reviewing a little bit more my problem and I can redefine it as

Powerbuilder 7.0 executing SELECT with a parameter on its WHERE clause, 
nested within other SELECT and executed against a MS SQL 2000 database, 
gives an error.

i.e.:

SELECT DISTINCT
    field
FROM
    table
WHERE
    data IN (    SELECT
                            data
                        FROM
                            othertable
                        WHERE
                            otherfield= :lsVariable);

generates an error where SQLCA.SQLCode= -1 AND SQLCA.SQLErrText='SQLSTATE = 
37000 [Microsoft][ODBC SQL Server Driver]Syntax Error or access violation'

However, if I replace the :lsVariable with a string literal ('value'), it 
works.

Also, I've found that if I use a parameter in a non nested SELECT, it works 
with no problem...

Any idea on what is wrong and how to make it work?

Thanks..!

0
Alex
9/26/2007 9:04:27 PM
sybase.powerbuilder.database 9855 articles. 2 followers. Follow

2 Replies
695 Views

Similar Articles

[PageSpeed] 38
Get it on Google Play
Get it on Apple App Store

Microsoft doesn't support use of parameter bindings in subselects, so
you have your workaround at this point... set DisableBind=1 to force
the SQL to use a literal.

On 26 Sep 2007 14:04:27 -0700, "Alex Castillo"
<AlexCast_SlwMx-at-HotMail-dot-Com> wrote:

>Hello.
>
>I was reviewing a little bit more my problem and I can redefine it as
>
>Powerbuilder 7.0 executing SELECT with a parameter on its WHERE clause, 
>nested within other SELECT and executed against a MS SQL 2000 database, 
>gives an error.
>
>i.e.:
>
>SELECT DISTINCT
>    field
>FROM
>    table
>WHERE
>    data IN (    SELECT
>                            data
>                        FROM
>                            othertable
>                        WHERE
>                            otherfield= :lsVariable);
>
>generates an error where SQLCA.SQLCode= -1 AND SQLCA.SQLErrText='SQLSTATE = 
>37000 [Microsoft][ODBC SQL Server Driver]Syntax Error or access violation'
>
>However, if I replace the :lsVariable with a string literal ('value'), it 
>works.
>
>Also, I've found that if I use a parameter in a non nested SELECT, it works 
>with no problem...
>
>Any idea on what is wrong and how to make it work?
>
>Thanks..!
0
Jim
9/26/2007 10:19:00 PM
Jim,

I modified the value of SQLCA.DBParm adding 'DisableBind=1' before executing 
the CONNECT USING SQLCA and that solved my problem.

Thanks for your help..!

"Jim O'Neil [Sybase]" <joneil@sybase.com> wrote in message 
news:elmlf39geog628hc0sat9h26ocv30503gh@4ax.com...
> Microsoft doesn't support use of parameter bindings in subselects, so
> you have your workaround at this point... set DisableBind=1 to force
> the SQL to use a literal.
>
> On 26 Sep 2007 14:04:27 -0700, "Alex Castillo"
> <AlexCast_SlwMx-at-HotMail-dot-Com> wrote:
>
>>Hello.
>>
>>I was reviewing a little bit more my problem and I can redefine it as
>>
>>Powerbuilder 7.0 executing SELECT with a parameter on its WHERE clause,
>>nested within other SELECT and executed against a MS SQL 2000 database,
>>gives an error.
>>
>>i.e.:
>>
>>SELECT DISTINCT
>>    field
>>FROM
>>    table
>>WHERE
>>    data IN (    SELECT
>>                            data
>>                        FROM
>>                            othertable
>>                        WHERE
>>                            otherfield= :lsVariable);
>>
>>generates an error where SQLCA.SQLCode= -1 AND SQLCA.SQLErrText='SQLSTATE 
>>=
>>37000 [Microsoft][ODBC SQL Server Driver]Syntax Error or access violation'
>>
>>However, if I replace the :lsVariable with a string literal ('value'), it
>>works.
>>
>>Also, I've found that if I use a parameter in a non nested SELECT, it 
>>works
>>with no problem...
>>
>>Any idea on what is wrong and how to make it work?
>>
>>Thanks..! 

0
Alex
9/27/2007 12:42:18 PM
Reply:

Similar Artilces:

MS-SQL Server 7 With PowerBuilder 7
I write a SQL statement like this: SELECT * FROM MATERIAL Table Material have 120,000 records data. Execute this Query in SQL Server Query Analyzer. It's spends about 2 minutes Execute this Query in PB 7.0 Database painter's ISQL Session It's spends over 10 minutes. I used Native drive not used ODBC to connect the MS-SQL 7.0. My database server have: Server's CPU x 4 Database Server : MS-SQL Server 7 How can I improve the performance in PowerBuilder? Thanks for your help! from Leo Leo, Depending on your requireme...

How to set database profile to PowerBuilder 10.5 connect to MS-SQL Server 2000 sp4?
Hi: How to set database profile to PowerBuilder 10.5 connect to MS-SQL Server 2000 sp4? It seems that the special connection inferface for "MSS Microsoft SQL Server" is canceled in PowerBuilder 10.5. I do not want to via ODBC. Thanks! Wu; No, MSS driver (actually Microsoft's NTDBLIB.dll) was cancelled by Bill Gates in SQLServer v7. It was supported in 2000 - but ANSI only with limited functionality and dropped for SS2000 and 2005 in the Unicode world. So your choices are: ODBC, OLE-DB and ADO.net. There is new SNC driver for PB 11 that is simil...

Porting Ms Sql Server 6.5 Database to Ms Sql Server 7.0
Hi, We have developed a product using PowerBuilder7.0, Ms Sql Server 6.5 and connecting them using powerbuilder native driver. Now we are thinking of porting Ms Sql Server 6.5 database to Ms Sql Server 7.0.As we feel, we could achive new features of Sql server 7.0.e.g row level locking. Actually i want to know, what things i have to do.e.g what sort of connection i have to use? what new features i will able to use?.will Stored procedures work properly. Is there anyting i have to chang in my application(at front end)e.g datawindows or any embeded sql, external functions. Should i po...

Porting Ms Sql Server 6.5 database to Ms Sql Server 7.0
Hi, We have developed a product using PowerBuilder7.0, Ms Sql Server 6.5 and connecting them using powerbuilder native driver. Now we are thinking of porting Ms Sql Server 6.5 database to Ms Sql Server 7.0.As we feel, we could achive new features of Sql server 7.0.e.g row level locking. Actually i want to know, what things i have to do.e.g what sort of connection i have to use? what new features i will able to use?.will Stored procedures work properly. Is there anyting i have to chang in my application(at front end)e.g datawindows or any embeded sql, external functions. Should i po...

Why does a simple select query in MS Access return the error: [Intersolv][ODBC SQL Server Driver][SQL Server]ct_results(): user api layer: internal client library error: Read from the server has timed
I have applied the information contained in support document 10447954. That did not resolve the problem. I am running a simple query with one criteria and one table Thanks. Hi Bill, The doc refers to a different ODBC driver (for DirectConnect product) so you might have a completely different problem. The message implies you are using ODBC 12.0 (Client Library based) or prior since the message indicates CtLib info. You might want to post more details involved with getting to this error. You also might want to post exact versions of ASE and ODBC driver, including Open ...

Pipeline Error using MS SQL Server 7.0
Whe I use the pipeline to copy a table from one MS SQL Server 7.0 database to another, I get the following error: Database error 999 SQLSTATE 07001 [Microsoft] ODBC SQL server Driver] COUNT field incorrect I am connecting to these database with ODBC and NOT any "native" drivers. Powerbuilder 6.51 Microsoft SQL Server 7.0 service pack 1 Any ideas? Thanks, Andy just a guess, but do you have a column in your table named [count] ? SQL Server allows keywords to be used as column/table names as long you provide the {} brackets around it. If you are using ODBC ...

PowerBuilder & MS SQL Server 2000
We have an application that we would like to implement using PB 8.x as the front end and MS SQL Server 2000 as the backend. Just wondering how homogenous the two really are. I have searched through some of the old messages in the PB general newsgroup in Google and I see instances of "this doesn't work here and this doesn't work there". Of course, a newsgroup like this is where you would get such messages. I know there are people that use it (SQL Server 2000) with PB. I'm just wondering how much, and with what degree of success? Will anyone take a guess as...

SQL Server 2005 error when trying to connect to sql server 2000 database
Hi all.I am working on asp.net 2.0 with back end as sqlserver 2000. but when trying to connect to the database i am getting the following error displayed An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Description: An unhandled exception occurred during the execution of the current web request. Please review the...

PowerBuilder & MS SQL Server, v.7
Hi, Just a few simple questions. Will PowerBuilder access MS SQL Server v 7.0? Has anyone successfully connected to SQL Server 7.0? Did you connect using the Native driver or ODBC? Thanks in advance, Roman Hi!! Yes, with ODBC, and it's working fine. Jose Rivera Merla Hi!!! My ODBC send message "Program type out of range". Please send me Your configuration (ODBC number, MSSQL number, operating system W95/98 NT4/5). Its important for me. JRivera wrote in ...

Powerbuilder 6.0 and MS SQL Server 2000
Greetings all! I have searched far and wide on the internet for the following problem. I have figured out a work around, but would like to know if anyone has solved the issue (other than the you need to upgrade answer). We have a PB 6.0 application with a MS SQL Server 2000 Database on an NT box. The application works fine, we have no problems running the application. The problem is in the development environment. I can connect to the database with MSS SQL Server 6.x native driver, but it has problems. It cannot create a datawindow without getting an error. It says&qu...

Powerbuilder 10.5
I am trying to connect to SQL Server 2000 from PB 10.5 and it gives me error "DBMS MSS is not supported in your current installation." Can anyone help me with this ? I do not see any Native DLLs from Powerbuilder 10.5 for SQL Server 2000. How do I use OLEDB Drivers for this ? try using this... SQLCA.DBMS = "ODBC" SQLCA.AutoCommit = False SQLCA.DBParm = "ConnectString='Server=server1;UID=username;PWD=pass;Driver=SQL Server;Database=dbname'" hope this help mike <ADuvvuru> wrote in message news:46bb9f7b.1420.1681692777@sybase....

Connecting Powerbuilder 7.03 to MS SQL 2000
I have been told that the dll's included in the 7.03 version of PB has the ability to connect to and work with SQL 2000. I have installed the upgrade but, when I open the database with the MSS driver the database is not updateable. In all the painters PB will not allow me to edit the data. I know that for Sybase SQL Server you need to run a script on the database to allow the driver to communicate with PB. Am I missing this file somewhere or is there another way to enable PB to work with SQL 2000? Thanks, Paul Swanson We work with PB7/SQL2000 with no problems, we di...

Microsoft SQL Server 2000 Support in Powerbuilder 7.0
Is there non-OLE support for SQL Server 2000 in Powerbuilder 7.0? If not, what can I do (besides upgrade)? Charles Sustek We access SS2K via ODBC. Is that what you are asking? HTH, Steve "Charles Sustek" <csustek@acep.org> wrote in message news:qxhDqs#5BHA.196@forums.sybase.com... Is there non-OLE support for SQL Server 2000 in Powerbuilder 7.0? If not, what can I do (besides upgrade)? Charles Sustek In PB7, you can connect to SQL Server 2000 3 ways: 1) ODBC (sqlca.dbms='ODB') 2) DBLIB (sqlca.dbms='MSS') 3) OLE-D...

intermittent Timeout Error with PowerBuilder App and MS SQL Database
We have a client server application that was developed using powerbuilder. This application has several modules, but one of the modules after working fine for several months, has started giving users Timeout Expired errors when creating a new record. Once this starts, every user in that module will start to get this error and we will have to restart the database. The curious thing is that this only occurs in this 1 of 7 modules. There was a thre started last year Oct 25, 2005, where another user was experiencing a similiar situation, but there was no resolution to it. A quesiton w...

Web resources about - Powerbuilder 7 nested SELECT parameter MS SQL Server 2000 error. - sybase.powerbuilder.database

PowerBuilder - Wikipedia, the free encyclopedia
PowerBuilder is an integrated development environment owned by Sybase , a division of SAP . It has been in use since 1991, peaking around 1998 ...

Sybase PowerBuilder tool readied for Microsoft's .Net
... Win32 or newer style .Net development After several years of work, Sybase is ready to deliver on the final step in its plan to move PowerBuilder ...

Jim O'Neil (@jimoneil) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Open Directory - Computers: Programming: Languages
about dmoz - dmoz blog - report abuse/spam - help the entire directory only in Programming/Languages Description Top : Computers : Programming ...

The HP Booth Staff (Goons) Owe Me an Apology
... when you might need to use it. For instance, when I was in the WinRunner world there were plugins for driving terminal emulators and powerbuilder ...

Contact Us - Sybase Inc
Thanks for visiting the 主页 section of Sybase.com. Here you will find information about Contact Us. For more information about Business Intelligence, ...

热门搜索 - 我的异常网
... 我的异常网 » 热门搜索 sdpnet2 移动【A111】其它用户原因鉴权失败,是什么意思啊 http:// 192.168.1.102:22578 本网站服务器位于美国,受美国法律保护 MiniUI js 破解 http://192.168.1.103:8080/ powerbuilder ...

Mobile app security: Always keep the back door locked
The best way to keep mobile apps safe is to secure the services they connect to.

10 Tech Skills That Will Instantly Net You A $100,000+ Salary
... that let's developers run Java applications. Java is highly popular language for writing web apps and custom enterprise apps. No. 8: PowerBuilder ...

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Resources last updated: 12/11/2015 9:06:26 PM