Problem with PowerBuilder, Dynamic SQL, Oracle and CLOB

(PB 8.0.3 build 9704 & Oracle9i)
I'm trying to create a "generic" response window to manipulate CLOBs from 
various tables (one at a time).

I pass in three variables to the window:
1. field_name
2. table_name
3. where_clause

So far I'm not even able to retrieve the CLOB value using Dynamic SQL.

I try the following:
I set up a string in the PB Code:
ls_get_string = " SELECTBLOB " + is_field_name + " FROM " + is_table_name + 
is_where_clause

This statement returns one row (I took the ls_get_string value and executed 
the statement in another tool without any problems).

Then...
DECLARE clob_cursor DYNAMIC CURSOR FOR SQLSA;
PREPARE SQLSA FROM :ls_get_string USING SQLCA;
OPEN DYNAMIC clob_cursor;
FETCH clob_cursor INTO :lb_clob;
CLOSE clob_cursor;

The "OPEN DYNAMIC clob_cursor" step gives "ORA-24333: zero iteration count" 
error message.
FETCH: "Cursor is not open"
CLOSE: "Procedure has not been executed or has no results".

I changed the code to retrieve a VARCHAR2 field and then it worked fine 
(changing the SELECTBLOB to SELECT in that case).

I changed the SELECTBLOB to SELECT and tried to access the CLOB field, but 
then PowerBuilder crashes.

Am I doing something wrong? Can you retrieve CLOBs in a cursor? Am I doing 
the Dynamic SQL wrong?
Is there a different way I could resolve this (having a generic window to 
maintain all my CLOBs?

Any help/suggestions/etc is very welcome.

Thanks
0
persa
6/23/2003 9:05:33 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

6 Replies
1063 Views

Similar Articles

[PageSpeed] 46

as per as I know Oracle 9i is supoorted in PB 9 not PB 8


<persa> wrote in message
news:ACFC0CF141D1C2E60073DD6D85256D4E.0073DD9285256D4E@webforums...
> (PB 8.0.3 build 9704 & Oracle9i)
> I'm trying to create a "generic" response window to manipulate CLOBs from
> various tables (one at a time).
>
> I pass in three variables to the window:
> 1. field_name
> 2. table_name
> 3. where_clause
>
> So far I'm not even able to retrieve the CLOB value using Dynamic SQL.
>
> I try the following:
> I set up a string in the PB Code:
> ls_get_string = " SELECTBLOB " + is_field_name + " FROM " + is_table_name
+
> is_where_clause
>
> This statement returns one row (I took the ls_get_string value and
executed
> the statement in another tool without any problems).
>
> Then...
> DECLARE clob_cursor DYNAMIC CURSOR FOR SQLSA;
> PREPARE SQLSA FROM :ls_get_string USING SQLCA;
> OPEN DYNAMIC clob_cursor;
> FETCH clob_cursor INTO :lb_clob;
> CLOSE clob_cursor;
>
> The "OPEN DYNAMIC clob_cursor" step gives "ORA-24333: zero iteration
count"
> error message.
> FETCH: "Cursor is not open"
> CLOSE: "Procedure has not been executed or has no results".
>
> I changed the code to retrieve a VARCHAR2 field and then it worked fine
> (changing the SELECTBLOB to SELECT in that case).
>
> I changed the SELECTBLOB to SELECT and tried to access the CLOB field, but
> then PowerBuilder crashes.
>
> Am I doing something wrong? Can you retrieve CLOBs in a cursor? Am I doing
> the Dynamic SQL wrong?
> Is there a different way I could resolve this (having a generic window to
> maintain all my CLOBs?
>
> Any help/suggestions/etc is very welcome.
>
> Thanks


0
Raj
6/23/2003 9:46:23 PM
9i is supported by PB 8 as well.  The 9i driver may have come in a
maintenance release, but I certainly have it.

--
Bruce Armstrong [TeamSybase]
http://www.teamsybase.com

Two new books on developing with PowerBuilder
http://www.pb9books.com?source=newsgroups

Need code sample?  Check out CodeXchange:
http://www.codexchange.sybase.com

Preach the gospel at all times. If necessary, use words. - Francis of Assisi
http://www.needhim.org
"Raj" <rchilamula@crimes.net> wrote in message
news:#SO#hGdODHA.218@forums-2-dub...
> as per as I know Oracle 9i is supoorted in PB 9 not PB 8
>
>
> <persa> wrote in message
> news:ACFC0CF141D1C2E60073DD6D85256D4E.0073DD9285256D4E@webforums...
> > (PB 8.0.3 build 9704 & Oracle9i)
> > I'm trying to create a "generic" response window to manipulate CLOBs
from
> > various tables (one at a time).
> >
> > I pass in three variables to the window:
> > 1. field_name
> > 2. table_name
> > 3. where_clause
> >
> > So far I'm not even able to retrieve the CLOB value using Dynamic SQL.
> >
> > I try the following:
> > I set up a string in the PB Code:
> > ls_get_string = " SELECTBLOB " + is_field_name + " FROM " +
is_table_name
> +
> > is_where_clause
> >
> > This statement returns one row (I took the ls_get_string value and
> executed
> > the statement in another tool without any problems).
> >
> > Then...
> > DECLARE clob_cursor DYNAMIC CURSOR FOR SQLSA;
> > PREPARE SQLSA FROM :ls_get_string USING SQLCA;
> > OPEN DYNAMIC clob_cursor;
> > FETCH clob_cursor INTO :lb_clob;
> > CLOSE clob_cursor;
> >
> > The "OPEN DYNAMIC clob_cursor" step gives "ORA-24333: zero iteration
> count"
> > error message.
> > FETCH: "Cursor is not open"
> > CLOSE: "Procedure has not been executed or has no results".
> >
> > I changed the code to retrieve a VARCHAR2 field and then it worked fine
> > (changing the SELECTBLOB to SELECT in that case).
> >
> > I changed the SELECTBLOB to SELECT and tried to access the CLOB field,
but
> > then PowerBuilder crashes.
> >
> > Am I doing something wrong? Can you retrieve CLOBs in a cursor? Am I
doing
> > the Dynamic SQL wrong?
> > Is there a different way I could resolve this (having a generic window
to
> > maintain all my CLOBs?
> >
> > Any help/suggestions/etc is very welcome.
> >
> > Thanks
>
>


0
Bruce
6/23/2003 11:40:25 PM
SelectBlob is a PowerScript function, not a SQL function.  You can't use it
as part of the dynamic SQL statement.

--
Bruce Armstrong [TeamSybase]
http://www.teamsybase.com

Two new books on developing with PowerBuilder
http://www.pb9books.com?source=newsgroups

Need code sample?  Check out CodeXchange:
http://www.codexchange.sybase.com

Preach the gospel at all times. If necessary, use words. - Francis of Assisi
http://www.needhim.org
<persa> wrote in message
news:ACFC0CF141D1C2E60073DD6D85256D4E.0073DD9285256D4E@webforums...
> (PB 8.0.3 build 9704 & Oracle9i)
> I'm trying to create a "generic" response window to manipulate CLOBs from
> various tables (one at a time).
>
> I pass in three variables to the window:
> 1. field_name
> 2. table_name
> 3. where_clause
>
> So far I'm not even able to retrieve the CLOB value using Dynamic SQL.
>
> I try the following:
> I set up a string in the PB Code:
> ls_get_string = " SELECTBLOB " + is_field_name + " FROM " + is_table_name
+
> is_where_clause
>
> This statement returns one row (I took the ls_get_string value and
executed
> the statement in another tool without any problems).
>
> Then...
> DECLARE clob_cursor DYNAMIC CURSOR FOR SQLSA;
> PREPARE SQLSA FROM :ls_get_string USING SQLCA;
> OPEN DYNAMIC clob_cursor;
> FETCH clob_cursor INTO :lb_clob;
> CLOSE clob_cursor;
>
> The "OPEN DYNAMIC clob_cursor" step gives "ORA-24333: zero iteration
count"
> error message.
> FETCH: "Cursor is not open"
> CLOSE: "Procedure has not been executed or has no results".
>
> I changed the code to retrieve a VARCHAR2 field and then it worked fine
> (changing the SELECTBLOB to SELECT in that case).
>
> I changed the SELECTBLOB to SELECT and tried to access the CLOB field, but
> then PowerBuilder crashes.
>
> Am I doing something wrong? Can you retrieve CLOBs in a cursor? Am I doing
> the Dynamic SQL wrong?
> Is there a different way I could resolve this (having a generic window to
> maintain all my CLOBs?
>
> Any help/suggestions/etc is very welcome.
>
> Thanks


0
Bruce
6/23/2003 11:42:51 PM
I kind of figured that I had to use SELECT (and not SELECTBLOB) in the 
dynamic SQL statement.
But, when I do that I get an "Application Error", "... memory could not be 
read..."; in short PB crashes.
Anything else that you see that is wrong with the code/statements?

Thanks
0
persa
6/24/2003 2:23:48 PM
WHAT is the content of "is_where_clause" variable?
Does it contain a '?' in it?

If so, you need to use something like :

string my_var = "ABCD"
ls_get_string = " SELECT " + is_field_name + " FROM " + is_table_name +"
WHERE my_field = ?"
//ls_get_string = " SELECT " + is_field_name + " FROM " + is_table_name +
is_where_clause

DECLARE clob_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM :ls_get_string USING SQLCA;
OPEN DYNAMIC clob_cursor using :my_var;
FETCH clob_cursor INTO :lb_clob;
CLOSE clob_cursor;


<persa> wrote in message
news:A9E84FDA5CCA0BD9004F158185256D4F.0004126C85256D4F@webforums...
> I kind of figured that I had to use SELECT (and not SELECTBLOB) in the
> dynamic SQL statement.
> But, when I do that I get an "Application Error", "... memory could not be
> read..."; in short PB crashes.
> Anything else that you see that is wrong with the code/statements?
>
> Thanks


0
WS
6/24/2003 2:25:40 PM
No, the ls_where_caluse contains the entire where-clause (WHERE field_name 
= the_value).

I changed the the SELECTBLOB to SELECT (since it shouldn't be a PB command 
I send to the Oracle server), but then PB crashes on the FETCH statement.
I then changed the FETCH statement to fetch into a string variable (as 
supposed to a blob variable). Then it actually worked (sort of), it only 
gave me the first 39 charachters (no "special" characters in there... and 
why 39? I have not tried it on any other records yet, to see if it is 
consistently 39 characters).
I then thought about using the Oracle "to_char" function in the select but 
then it limits me to 4000 characters (unless there's another parameter I 
can use to keep the entire lenght of the CLOB. No CLOB should be much 
bigger than 30-40K characters).

Any additional ideas?

Thanks


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

WHAT is the content of "is_where_clause" variable? Does it contain a '?' in 
it? 
If so, you need to use something like :

string my_var = "ABCD"
ls_get_string = " SELECT " + is_field_name + " FROM " + is_table_name +"
WHERE my_field = ?"
//ls_get_string = " SELECT " + is_field_name + " FROM " + is_table_name +
is_where_clause

DECLARE clob_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM :ls_get_string USING SQLCA;
OPEN DYNAMIC clob_cursor using :my_var;
FETCH clob_cursor INTO :lb_clob;
CLOSE clob_cursor;
0
persa
6/24/2003 3:34:05 PM
Reply:

Similar Artilces:

Problem with PowerBuilder dynamic cursor in sql-Anywhere and performance problems
Please help ! I have developed a statistic program with PowerBuilder Vers. 6. This program gives the user much freedome in choosing filters (for the where-clause) and the fields to retrieve from the database. Therefore I use the possibility of dynamic cursors, because I am only able to build the select-statement at runtime, when the user has entered his parameters. When using the 16-Bits Data engine of SQL_Anywhere the program works without any troubles. When using the 32-Bit engine I receive the following Error-Message: SQLSTATE = S1000 [Sybase][ODBC Driver] General err...

Problem connecting to oracle database in powerbuilder 9.0 desktop version
I am not able to connect to oracle database using odbc in powerbuilder 9.0 desktop version.I am getting error message which says 'Inavlid or unsupported datasource' Ershad, Have you verified that you have the required Oracle ODBC drivers? The latest set of drivers is available at: http://www.oracle.com/technology/software/tech/windows/odbc/index.html\ Could you also please post the attributes of the transaction object (except the password) that you use to try and connect to the oracle db? Sanjiv. <Ershad Ali Mohammad> wrote in message news:41aea458.51d5...

3-Tier Powerbuilder Application problem talking to Oracle 8.0.5 database
We have had a 3-tier (client, server, database) application built that works well in certain installations but fails in others. The failure is completely dependent on where the server executable is installed. The request that causes the error is completely repeatable. If a request is made for one claim abstract and it's associated content (just a document built from the database) the app works fine always. If a range (more than one) is requested I will get a 'The request caused an abnormal termination...connection closed' error that crashes the client executable. This ...

SQL Problem in powerbuilder
Dear all, I have put the following script to insert a new record to db. But after run the script, nothing is inserted. CAN anyone help me? insert into psap_education (psap_no, psed_seq, psed_grade, psed_school, psed_years, psed_create_user_id, psed_create_date) values (5098,1,'GRADE1','ABC SCHOOL','2002', 5097,'2002/9/6') using sqlca; COMMIT using sqlca; Have you checked the value of sqlca.sqlcode before you issue the commit? If its value is -1 then an error occurred (for example duplicate primary key). You can determine the error that occu...

Version of PowerBuilder (ref: Creating database for PowerBuilder)
I'm using PowerBuilder v8.0 and would like to create a notification system for my practiculum project. I'd like to use Access to create the database. Please enlighten me on what should I create (e.g. tables, queries) for the usage of it. Thank You. Regards, Yang Ashra Don't double post, it's bad etiquette. Unless you have a very good reason to do so, we recommend you don't use Access. Use SQL Anywhere instead - it's more stable - and a far better database in my opinion. PowerBuilder (enterprise) supports just about every database out there, so if you...

PowerBuilder 9.0.2 native Oracle driver for Oracle 10g database
We are migrating from Oracle 8i to 10g. The PowerBuilder 9.0.2 application is extremely slow using PowerBuilder Oracle 8.4 or 9.0 native driver to connect to Oracle 10g database, We had to use 7.3 driver. After using trace we found that the SQL scripts with index hint cause slow down, but they are working ok using Oracle 8.4 driver with Oracle 8i database. Any suggestion which is best way to connect from PowerBuilder 9.0.2 to Oracle 10g database? We have tried the Oracle 8.1.7 client and Oracle 9.0 client. I found a solved case on Sybase support web site, but we do not have support...

PowerBuilder and Oracle-ODBC problem
Hello, we are getting wrong number values back from the database (using Datawindows) when using Oracle's ODBC-driver version 2.x. This doesn't happen with the 1.15 version. Is this a known bug? Is there a workaround for it? We use PB 5.0.02 and Oracle 7.3 TIA, Roland -- = \ // Dipl.-Ing. Roland M=FChlberger \ // mailto:Roland.Muehlberger@systema.co.at \// SYSTEMA Human Information Systems // Tel. +43 7252 587 1662, Fax +43 7252 587 600 // http://www.systema.co.at Can you be a bit more specific. Exactly which Oracle ODBC drive...

Powerbuilder database configuration problem
Recently I was given some source code that uses a Sybase SQL Anywhere database as its back end. I can view the source code but if I try and go into a data window Powerbuilder is not connecting to this database, rather I believe it trys to go to the Powerbuilder example database. The database that corresponds to the source code mentioned was not originally created on my machine. How can I configure Powerbuilder to recognize a database that was not originally created on that machine? Thanks in Advance. What you want to do is configure an ODBC datasource for that database...

SQL problems with PowerBuilder and UDB
Dear All I'm using PB 6.5,PB 5.004,UDB 6.1,NT If column has Null values and I 'm trying to execute simlpe Select Select Sum(column_name) from Table_name I have Error Message : Select error: SQLSTATE = 01S01 [IBM][CLI Driver] CLI0165E Error in row. SQLSTATE=01S01 Is anybody has idea why is this happening ? Yuri Tsimbler InLearnSol@aol.com Yuri Tsimbler wrote: > Dear All > I'm using PB 6.5,PB 5.004,UDB 6.1,NT > If column has Null values and > I 'm trying to execute simlpe Select > > Select Sum(column_name) from Table_name > I ...

PowerBuilder and Oracle Decimal Problem
Hi, I have two type problems related to Oracle Procedure. First My Procedure return to decimal value for example "2.20" but out pb program taking the that value "220.00" .Second Procedure has a Number Parameter I am filling decimal value like "2.20" then i am getting the error message from oracle [ORA-06502: PL/SQL: numeric or value error: character to number conversion error] My Decimal seperator is "," in windows registry. Also i am using that methods in my application, SQLCA.DBPARM= " DecimalSeparator=','" //Dec...

PowerBuilder and Oracle Decimal Problem
Hi, I have two type problems related to Oracle Procedure. First My Procedure return to decimal value for example "2.20" but out pb program taking the that value "220.00" .Second Procedure has a Number Parameter I am filling decimal value like "2.20" then i am getting the error message from oracle [ORA-06502: PL/SQL: numeric or value error: character to number conversion error] My Decimal seperator is "," in windows registry. Also i am using that methods in my application, SQLCA.DBPARM= " DecimalSeparator=','" //Decim...

Powerbuilder and Oracle SQL limits
We've been writing an application in PB against Sybase and MSSQL (6.5 and 7) for about 3 years. Now we are trying to support Oracle (v8.05) as well. It's not going great. The biggest problem seems to be the result of datawindows with large SQL statements that outer join the same table multiple times. Is there a limit in Oracle (or a setting) for number of outer joins or size of SQL statements? I seemed to be able to get it to work only by stripping the statement down to a single outer join and a SQL statement less than half the size of the original. Also, can anyone recomm...

Oracle store procedure problem using Powerbuilder and Oracle 8.0.4.0 Net
Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net 8.0.4.0 I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net 8.0.3.0. Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version 8.0.4.0 (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...

Select in Powerbuilder Database painter WORKS but does NOT in Powerbuilder run mode.
I have enclosed the actual information for a debug run. Will apprecaite any hints. Thanks Jim jimo@jdssc.com in ls_mod_string DataWindow.Table.Select="SELECT DISTINCT ~"inv_dtl~".~"inv_id~", ~"inv_dtl~".~"orig_cost~", ~"inv_dtl~".~"last_cost~", ~"inv_dtl~".~"regular_cost~", ~"inv_dtl~".~"orig_price~", ~"inv_dtl~".~"current_price~", ~"inv_dtl~".~"sugg_price~", ~"inv~".~"vend_id~", ~"inv~".~"style~",...

Web resources about - Problem with PowerBuilder, Dynamic SQL, Oracle and CLOB - 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: 1/1/2016 11:22:23 PM