Avoid SQL in PowerBuilder


Link to this posting

Postby Ursego » 19 Feb 2013, 22:25

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.
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33

Link to this posting

Postby usrmk » 10 Mar 2016, 17:15

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.
usrmk
 
Posts: 1
Joined: 09 Mar 2016, 19:33

Link to this posting

Postby jagonzo62 » 19 Apr 2018, 12:51

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




Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker