SQL preparation

Applications Grid / Reports
#1

Can anybody give me an example how to put in a procedurename in the SQL preparation of a grid.
I wrote an procedure in the programming PHP section with the name docstatus_update.

How to call this procedure in the SQL preparation section of the Grid.
Simple writing docstatus_update() or docstatus_update(); doesn not work.

The procedure works fine under a testbutton. But I want i to have it running before the select in Grid. So in the SQL-preparation…
The webmanual says it can be done, but how?

Regards Bert Steinebach

#2

You can only intercept in the available events. In this case in the onbefore or onafter (update/insert). Before the select in Grid you need to use the oninit. To mess with the sql statement you need to use the sc-macro’s. Actually I don’t understand what you are trying to achieve…

#3

I use SQL preparation to prepare the data that the Grid will retrieve. So It runs before the grid is selected. However the SQL Preparation only accepts SQL Queries or Stored Procedures inside the database.

To achieve your goal, as Albert mentioned, use the onApplicationInit. Call your docstatus_update() or docstatus_update() from there.

1 Like
#4

Thanks vcgonzales and albert,

I never used “sql preparation” before and I am experimenting if I can do usefull things with it, I understand that it is executed before the “main select” is taking place.

In the documentation of a grid there is:

SQL Preparation - Enter SQL commands or procedure names to be excecuted before the main select.

Using an SQL commands is clear but to use an “procedure name”. I understood that as the name of procedure which I made under Programming / PHP methods. But that is not the case. In think the procedure name is not the name of the PHP method but a procedure made in the database as you said vcgonzales.

Yes the procedure (method) works under onApplicationInit. The restriction here is that this procedure is executed only ones when starting the application. Thats not the aim. So I am going to experiment with “stored procedures” in the database. Come back with feedback.

Thanks sofor…

Regards Bert

#5

Yes the way is to develop stored procedures in the database and that works. So I was mixed up with procedure inside scriptcase and procedures in the database.
Of course I could have though about that before…never to old to learn.

#6

Glad you solved this. Please be so klnd and share your solution using stored procedures in the database.

Blessings,
Deon

#7

Ok deonbez,

I made in the database (mysql) a simple procedure only for to show how…(you do it in the database and not in sc)

BEGIN
SELECT * from documenten where opruimen <> 1; // “documenten” in english is “documents” and “opruimen” is “cleanup” and “1” is an integer

END

and give it the name “test”

So I in this case I donot want to show in the grid the documents which are cleanup.

In sc in the grid in the “SQL preparation” of the grid I put in “call test”

You can make the procedure as complex as you want. Of course you can do this also directly in het where clause also, but that is not your question.

Regards Bert

#8

Hi everyone,
I love it when people share questions and solutions :slight_smile:

Just to clarify for anyone stumbling upon this post in several years LOL… the advantage of a store procedure in the database is that you can centralize (and possibly better protect) business logic and even the database’s underlying table and field structure in the database. There are other pros and cons, but that in theory makes it desirable.

From a RAD perspective, for me, I usually try to discipline myself to do it this way, but always end up building ad-hoc SQL selects in the PHP/SC side of things. I could go back later and sometimes do, but it does make it ‘harder’ for me to adjust my select if is a stored procedure.

I wonder if in SC10 (OMG LOL) they could make an interface directly on a grid or other app that would let us create and edit and test stored procedures. Would be kinda RAD right?

I was wondering if anyone has found other advantages for this when developing in SC? Like possibly performance increases? Optimization is always something to seek out and may be enough of a reason to completely switch over to stored procedures… thoughts anyone?

PS - I am also jumping in on this thread because I have been reading all the SC9 horror story bug posts and this post was about a ‘real’ programming thing :slight_smile: One way to ‘solve’ SC9 bugs is to just keeping using or install SC8 until the dust settles.

Peace,
Jamie

1 Like
#9

I looks ok. I cannot see the table name. Is that ok?

#10

SQL Preparation is supposed to let you process some data, or scriptcase calls it to prepare your data.

If you do a simple select inside the SQL preparation, it simply just wastes your database server resource as it will execute the query and do nothing about its results.

I think what you need to do is just put the WHERE clause inside the SQL Statement, and leave the SQL Preparation empty.

1 Like