Dummy Table and ODBC transactions: looking for best practices advices

questions
, ,
1

Hi all.
We probably use the “Dummy table” trick to recover data from SQL DBMS and execute stored procedures, etc.
I personally use a “real” table with several cstring fields, and it´s defined on my DCT. I know there are several variants on this.
I’m opening this thread to discuss the “best practices” on this subject.
Some starting points:

  • Implicit or explicit transactions?
  • Clarion vs engine transaction management (logout vs set/start transaction)
  • Include (and open/close by template) the dummy table on Procedure’s “Other Tables”?
  • Use alias in DCT for dummy tables and manage several “SQL dummies”?
  • To “buffer” or “not to buffer”?

I hope you can share your expertise.
Thanks!

1 Like
2

Can you explain what do you mean by “Dummy table” trick?

3

Mike - I assume it’s a generic table to which to use as a vessel for PROP:SQL.

1 Like
4

well, one can assume the same, another can assume something different :slight_smile:

5

Several thoughts come to mind regarding a whole range of “best practices.”

However, most important in my mind -
Get as much data integrity logic/rules/control out of Clarion as absolutely possible.

6

I’m not so sure about getting all the RI out of clarion. I really like some aspects of getting clarion to ALSO enforce this as well as the backend.
One reason being that the backend will only tell you about a problem after the fact. With clarion we can find it beforehand.

1 Like
7

Not sure who first used it, but Steven Parker wrote an article in Clarion Magazine about it: “Getting Useful Information Out of SQL, Part 2” (available from https://clarionmag.jira.com/wiki/download/attachments/399447/cmag-2008-02.pdf?api=v2).
As Jeff stated, it’s a techique to put the results of an SQL query (or call SP with no results, also) into an app’s readable buffer.

8

Not sure I fully comprehend. Got an example?

9

I usually do the following:

  1. Create a table in the database (called “SQL”) with several fields (named Campo1, Campo2, etc)

  2. Import that table into the DCT

  3. Add the file to “Other files” on each procedure I need it.

  4. Write code like:

    sql{prop:sql} = ‘call sp_obtener_nro_caja_abierta(’ & GLO:Sucursal & ‘)’
    if errorcode() then message(fileerror()).
    next(sql)
    if errorcode() then message(fileerror()).
    if sql:campo1 > 0
    !do something if the output of the stored procedure is > 0
    end

There is a lot to do to make this better (like use “bind” for parameters, have a better error management, etc), it´s just a simple example as you asked for it.

10

@totalhip Clarions RI recovers and warns you fairly well of a relational problem. A backend error for the same thing is sometimes obtuse. There are also places where Clarions RI works where you need to play about in SQL. eg If you have multi parents having the same child. In some cases I have Clarion RI and no backend, sometime the opposite. But usually both.

11

Sean,

Recognizing the likely unique aspects of each developer’s situation, I state 3 basic reasons why I move RI out of Clarion to the backend (PostgreSQL).

Like me, I believe Clarion is getting old. The chance of finding a developer to take over existing Clarion code is not high. Moving logic out of Clarion to PG gives a “next generation” developer the opportunity to benefit from much of the work I have done using his/her own tools of choice without learning Clarion.

Second, adding a trigger (insert, delete, update, before, after) in PG is simple. I essentially do not need to worry about logout/rollback. If the trigger does not complete w/out error, the transaction is not written. While I agree SQL errors can be obtuse, I believe that is something my Clarion code CAN trap and more understandably explain to the user.

Finally, for highly normalized table structures, using a PG view InsteadOf trigger fantastically simplifies what I need to include in my Clarion code. The result is usually a Clarion form procedure that writes to multiple PG tables with the simple use of a single ABC FileManager insert or update call.

2 Likes
12

With TURBOSQL you don’t need the table really created in your database. You didn’t mention with SQL are you using and some answers could change based on that.
If you’re updating just 1 table, you don’t need to start a transaction. In case of multiple updates, I prefer to indicate SQL that we’re starting a transaction and control that. Not with LOGOUT/COMMIT but with real BEGIN TRAN, etc.
The same for RI, in case of cascade updates Clarion is not the most eficient tool to do it and, as someone said, I don’t know if Clarion is going to be the only tool that uses the database (you could need a web app soon and there are several tools for doing that, not only NetTalk or H5) and it could be a mess.
You can have several alias of the same table or just several tables (remember, with Turbosql the phisical table doesn’t exist so it doesn’t bother you).
And buffer can help, of course it depends of the rows you have in your table and how your browses works. There is an old article of Dan Pressnell, for example, where recommend to start with an empty browse and let the user defines what he wants. Another way is to reduce the traffic filtering in advance, do you really need to show all the invoices (for example) or bringing it the last 50 is enough?

1 Like