Avoid SQL in PowerBuilder
3 posts
• Page 1 of 1
Link to this posting
by Ursego » 19 Feb 2013, 22:25
Don't write SQL statements in PowerBuilder.
Why? Please read 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 .
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, 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 ).
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.
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 will make your life much easier - you will work with a DataStore rather than a PowerScript dynamic cursor.
Why? Please read 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 .
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, 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 ).
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.
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 will make your life much easier - you will work with a DataStore rather than a PowerScript dynamic cursor.
-
Ursego - Site Admin
- Posts: 143
- Joined: 19 Feb 2013, 20:33
Link to this posting
by 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.
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
by 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
3 posts
• Page 1 of 1
