cancel
Showing results for 
Search instead for 
Did you mean: 

Most effiecient way to process result sets of stored procedures

Former Member
0 Kudos

Are the use of datastores efficient in processing stored procedure result sets?  Or would looping through the result sets using fetch statements in powerscript?

Datastores would simplify the processing in many ways but if there is a huge overhead in doing so than I would have issues with speed in the application.

FYI, most result sets would be fairly small as in less than 5000 rows with the majority less than 500.

Opinions?

Thanks

TPS

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Tom;

  DS's do the same Fetch loop as you would probably code yourself. However, the DS's Fetch is written in Assembler - so it's way more efficient. 

  The other key consideration is to use a native DB client driver and not Middleware like ADO.net, OLE-DB, ODBC, etc.

HTH

Regards ... Chris

Former Member
0 Kudos

Strange issue that I am having.

datastore lds_formlabels;

lds_formlabels = create datastore;

lds_formlabels.dataobject = "d_get_formlabel";

lds_formlabels.settrans( SQLCA);

lds_formlabels.retrieve(formid);

MessageBox(string(formid),string(lds_formlabels.rowcount( )),StopSign!);

Running from the IDE I get 20 records being returned which is correct.

When I deploy and test I get 0 records.

This is a very simple stored procedure that just returns a result set of 2 integer values with 20 rows in the table.

Any idea what I have messed up here??

Thanks

ricardojasso
Participant
0 Kudos

Include the dw in the resource file for the project,

OR

Check the "PBD" checkbox in the project's library list.

PowerBuilder does not automatically include the dw in the exe when the dataobject is explicitly set using the dataobject property. But it will include it in the PBD file if this option is chosen.

Former Member
0 Kudos

Hi Tom;

  I bet Ricardo's observation is correct. I also bet that you didn't check the return codes on the SetTrans() & Retrieve() methods.  

  Also, you should be using SetTransObject() and not SetTrans BTW. 

Regards ... Chris

Former Member
0 Kudos

Does this have a mechanism of getting blobs into the database or is this to just display them in the datawindow once they are already in the database.

If my table is:

BIGTHING_ID    integer

BIGTHING         long binary

What is the mechanism of setting this up for inserts / deletes and selects. 

Thanks

Former Member
0 Kudos

Hi Tom;

=> Does this have a mechanism of getting blobs into the database

    Yes, you can pass a Blob argument to a Stored Procedure in newer versions of PB. That all depends though on your DBMS type & version as well. For example, only ASE 15.7 and higher allow Blobs (aka BINARY) data type to be passed in as an argument.

     The other alternative is the new Table Blob DataWindow feature which includes automatic retrieval and updating via a DW object. That feature was added in version 12.5.

Regards ... Chris

Former Member
0 Kudos

Chris,

Can you point me to the documentation on how to use the table blob.  I see that you do not place it in the original SQL selection of creating a new datawindow.  So when I create the new datawindow I just select the BIGTHING_ID and than manually ad in the BIGTHING blog?  does the datawindow core funtions such as insertrow, deleterow , print  ,update , etc.. function?  I am using powerbuilder 12.6 classic

Another quick question:  Why does the SetItem not trigger the Itemchange event?

Thanks,

Former Member
0 Kudos

Hi Tom;

  Yes, the documentation is a little scant on the exact details for utilizing the TableBlob feature. Here is a quick overview of the steps ...

1) Create a table with a blob column. Here is my example: 

2) Create a Freeform DW object on the table containing the blob column (you cannot select the blob yet). Then add a Table Blob column to the new DW as follows:

3) In the next dialogue - map the TB column & define its usage.

4) Add a picture or text to the Blob area in its background so that inserts are obvious.

    (a Chris Pollach tip)

5) Run the DW object and perform a Retrieve or an insert. If the Blob data is not present, the background text will show and advise the user to double-click to add some Rich Text. If RTF data is present, it should show this within the TB area and the background text object will not show.

HTH

Regards ... Chris

Former Member
0 Kudos

Chris,

I followed these steps.  The blank item comes up on insert. When I double click it not only does the application crash. But the entire IDE crashes.

This occurs both when running the program and just doing an insert from the datawindow painter.

PB Classic 12.6

Former Member
0 Kudos

Oh Dear ... and YES, the same crash of the IDE happens to me as well! Even if I use a picture vs a text object underneath the BLOB - double-click and you get an IDE GPF!     

Looks like a PB 12.6 bug.  

Former Member
0 Kudos

Datastores are more efficient than cursor fetches as the whole result set arrives in one piece.

Having said that. If the processing can be done on the whole set, the dbms should do it.

On datastores a loop doing dw_1.getItemString(ll_row, 'col4') is faster than dot notation like dw_1.object.col4[ll_row]. On the other hand dot notation is better if you want whole arrays filled. ls_col4s[] = dw_1.object.col4.