Page 1 of 1

Avoid SQL in PowerBuilder

PostPosted: 19 Feb 2013, 22:25
by Ursego
Don't write SQL statements in PowerBuilder.

Why? Please read here about the concept of a SQL-free client. Now I only want to suggest replacement for different kinds of SQLs in PB:

SQL TO REPLACE: SELECT statement as the data source of a DW.
SUGGESTED REPLACEMENT: Stored procedure, returning a records set, as the data source of a DW. If the DW is updatable, then also procs for INSERT, UPDATE and DELETE. If you use Oracle, then read Oracle packaged procedure as DW data source.
REASONS: Data storage abstraction. Encapsulation of data retrieval/processing complexity and security. Ability to return recordsets, built with help of cursors and temporary tables (which would be impossible in a plain SELECT, or the SELECT would be very complex, messy and bugs-prone).
REMARK: If you disregard this advice and want to use SQL as its data source anyway, this info could be useful.

SQL TO REPLACE: Dynamic SQL, embedded to PowerScript, that does NOT produces a result set (forms 1 & 2 of dynamic SQL) (includes SELECT, which returns one or more scalar values, and INSERT, UPDATE and DELETE).
SUGGESTED REPLACEMENT: Stored procedure/stored function (called with RPCFUNC).
REASONS: Data storage abstraction. Encapsulation of data retrieval/processing complexity and security. Efficiency: if the business flow consists a few SQL statements, each one of them requires two trips (to and from the database), while placement of all these SQL statements in one stored proc means only two trips.

SQL TO REPLACE: Static (i.e. not dynamic) cursor, embedded to PowerScript.
SUGGESTED REPLACEMENT: DataStore.
REASON: PB cursors are very inefficient. Each processed row requires two trips (to and from the database), while the DataStore is retrieved once (no matter how many records are returned).

SQL TO REPLACE: Stored procedure/stored function call, embedded to PowerScript.
SUGGESTED REPLACEMENT: RPCFUNC declaration in transaction object (see here).
REASON: Stored procedure/stored function calls, embedded to PowerScript, are ornamented with a lot of technical code which shadows business logic. Code duplication if the proc/func is called more than once in the client.

SQL TO REPLACE: Dynamic SELECT, embedded to PowerScript, that produces a result set (forms 3 & 4 of dynamic SQL).
SUGGESTED REPLACEMENT: Move the whole logic to a stored procedure and call it with RPCFUNC.
REASON: Obvious. :lol:
REMARK: If it's absolutely impossible to move the whole logic to a stored procedure and, anyway, you need to write an embedded dynamic SELECT which returns more than one record, then the function uf_ds_from_sql() will make your life much easier - you will work with a DataStore rather than a PowerScript dynamic cursor.

Re: Avoid SQL

PostPosted: 10 Mar 2016, 17:15
by usrmk
Hi, new user here =) (and fairly new to PB)

We have a number of MariaDB stored procedures that we've been calling using the DECLARE - EXECUTE PROCEDURE technique, but we're not entirely happy with that approach, reading this post convinced us use the RPCFUNC declaration but, since we are using a source control tool for PB, just one programmer can edit an object at a time (for us it means only one programmer can edit de transaction object at a given time).

We were wondering if we needed to create multiple transaction objects per module (or application?) with its own set of functions to be able to work on parallel or is there a better approach?

I've been enjoying this site. Thank you.

Re: Avoid SQL

PostPosted: 19 Apr 2018, 12:51
by jagonzo62
There was or is a 32k limit on the number of declarations that can be written in a local or global external.