V4RB: handling of SQL-Statements

[Lloyd Butler]

> Message: 1
> Date: Mon, 9 Jun 2003 12:11:58 +0200
> From: Claudius Sailer <Claudius at sailer-online.de>
> Subject: V4RB: handling of SQL-Statements
> To: Valentina list <Valentina at lists.macserve.net>
> Message-ID: <CDF7576A-9A62-11D7-B3AF-00039365848C at sailer-online.de>
> Content-Type: text/plain; charset=US-ASCII; format=flowed
> 
> Hi,
> 
> I had some time today and checked my RB Code. I exported all SQLStrings 
> and found 300 SQL-Strings which are hard coded in the app. Now my 
> question. How do you organize your SQLStrings? Do you use constants or 
> do you use SQLStrings at the place where you need it? Do you make some 
> checks whether you have SQLStrings twice or more times the same?
> 
> Thnaks for you input
> ----------------------------------------------------------------------
> 
Claudius,

I don't use RB, but what I do is I have a table called 'scripts' in each of
my databases.

In there I store the sql query's. If it requires parameters, then I use
place holders of "<parm#>" where the # is replaced by a number.

I have 4 functions in my Valentina library, sqlPull, sqlSelect, sqlExecute
and sqlClose. The sqlPull will allow me to pull a stored script, replace the
parm variables and execute it. It calls sqlSelect and returns the data. The
sqlExecute is using to handle any Execute calls, and sqlClose closes the
database. sqlSelect and sqlExecute automatically open the database, but I
have not added too much error handling in there yet.

At the moment I have the library returning pipe delimited fields in return
delimited records that I can parse. This is because I am using Applescript
or Perl. In Perl I have used MacGlue to link to Valentina and it send
AppleEvents to work with the database. It is a bit pokey getting the glue
inited, but them it zips along quite well for what I need, especially with
the speed of Valentina. It works on OS X and 9 as well.

It is not am ideal solution, but it means that I can have multiple scripts
accessing the database and using the exact same query's if need be and not
have to put them directly into my code.

The idea was to try and simulate stored procedures. I am still working on
added the ability to have a multi-line script stored and the results of the
first line feed into the second and second into third and so on. I haven't
had time basically and now with Ruslan's additions of the ability to select
in a select, I may not need to.

I can also call sqlSelect or sqlExecute directly if need be, but I try no to
as it may mean fixing code later.

That is how I try and reuse my queries so that I do not have to edit them
multple times if I change the database at all. I

I can send you my libraries if you want to see them to get some ideas.

Lloyd Butler
Product and System Development Supervisor
TWN Commercial Services
lbutler at on.pelmorex.com
p: 905.566.9511x286
f: 905.566.9370