How to program SQL in Clarion

[Kelvin Chua]

Hi Pierre,

Sorry being a Oliver Twist, just hope to clear my doubts before the year
ends. In fact, I really appreciate the code you given, else I will be
struggling in the total darkness.

> open(ATempFile)
> ATempFile{prop:SQL} = SQLStr
> if errorcode()
> message('Error executing ' & clip(SQLStr) & ', FileError() = ' &
> FileError())
> else
> next(ATempFile)
> end

What actually is the purpose of the ----- next(ATempFile)? To set to
the first record?

Let's say I were to have another table CHART which contains my Chart of
Accounts.

Table: CHART
Prefix: CHA
Fields: CHA:AccountNo, CHA:DEBITAMT, CHA:CREDITAMT

How should I actually code the loop to get all the debit and credit totals
for each of my AccountNo within my Chart of Accounts?

Is it similar to:

open(ATempFile)
CLEAR(CHA:Record) !Presuming I had included this table in the
procedure
SET(CHA:ByAccountNo,CHA:ByAccountNo) ! Just for reference, will try to
code in ABC
LOOP
NEXT(CHART)
IF ERROR() THEN BREAK.
loc:AccountInfo = CHA:AccountNo
ATempFile{prop:SQL} = SQLStr
if errorcode()
message('Error executing ' & clip(SQLStr) & ', FileError() = ' &
FileError())
else
next(ATempFile)
end
.
CLOSE(ATempFile)

Thank you very much and Happy New Year!!

Kelvin Chua
SINGAPORE

[Pierre Tremblay]

Hi Kelvin!

> > open(ATempFile)
> > ATempFile{prop:SQL} = SQLStr
> > if errorcode()
> > message('Error executing ' & clip(SQLStr) & ', FileError() = ' &
> > FileError())
> > else
> > next(ATempFile)
> > end
>
> What actually is the purpose of the ----- next(ATempFile)? To set to
> the first record?

next(ATempFile) will retrieve the first row of the result set for the
statement contained in SQLStr. When you issue a {prop:SQL}, the statement is
executed immediately. If the statement produces a result set, you will need
to issue NEXT statement in order for your application to retrieve it.

Not exactly. You will add a new field in ATempFile that will represent the
AccountNo and the select statement wont have the where clause, just the
group by.

Ex: select sum(Debit), sum(Credit), AccountNo from CHART group by AccountNo

Then, you will retrieve the result set using a loop instead of a single
next.

[Kelvin Chua]

Hi Pierre,

Got it! Thanks!

Kelvin Chua
SINGAPORE

"Pierre Tremblay" <pie...@pierretci.com> wrote in message
news:3a4f4347$1...@news.softvelocity.com...

[Chet Sapino]

Kelvin:

Just write the code in ABC clarion and let CW translate it into SQL thru the
driver. Why write the SQL code at all? I have an 11 module enterprise
accounting/manufacturing app using SQL Anywhere V7 without about 1,000
procedures and 200+ tables, and it is all cw code with almost zero sql and
it runs like a bullet. I converted it over from a Topspeed version. A couple
of modules has about 5 pages of Cw code updating about 12 tables and ran as
is with no sql code whatsoever.

Regards,
Chet Sapino

"Kelvin Chua" <kel...@accpro.com.sg> wrote in message
news:3a4f...@news.softvelocity.com...

[Kelvin Chua]

Hi Chet,

Wow!!

I certainly agree with you because I would rather do things in Clarion way.
I just lectured my Delphi programmers.... just cannot imagine why so much
code is needed for Delphi...

Chet, I wonder if you can guide me on my SQL implementation.

I need the following to be resolved.

1. How do you actually filter your table when generating reports. I am
using the VIEW within the Interbase, as per what Kenneth Schan had
suggested.

2. How do you process you records, I mean using of SELECT.. WHERE...
INTO... Mr Pierre Tremblay had suggested using the temp files to capture the
Atemp{PROP:SQL} = '.......'

3. I am using Fomin Reporter and CPCS Reporter. Do you think they are
appropriate?

Sorry to bother you with these three problems I am facing. I would really
appreciate if you could even provide few lines of source to enlighten me.

Thanks.

Kelvin Chua
SINGAPORE

"Chet Sapino" <cs1...@voicenet.com> wrote in message
news:3a53...@news.softvelocity.com...

[Chet Sapino]

For filters, I always have a key for the filter so I use a record selector,
rather than a key so I can set(key,key). This puts you on the starting
record. The report or process starts there and you don't do a full table
scan. Define a variable to set the record selector in the embed code.

Again. let Clarion do it. Create a key in the dct which will synchronize up
to an index in the SQL table.

For a filter:
Let's say you want a date range report. Create a window to get Glo:FromDate
and Glo:ToDate. Bind the date and the globals in the report. Call the date
window from an embed in the report.

Then put in the filter InvoiceDate >= Glo:FromDate AND InvoiceDate <=
Glo:ToDate.

Since the index is on InvoiceDate in the SQL db, it will do a Set(key,Key)
to start at that date, loop thru that date and exit when the date changes.
If you have a large db, try it out.

Regards,
Chet Sapino

"Kelvin Chua" <kel...@accpro.com.sg> wrote in message

news:3a53...@news.softvelocity.com...

[Chet Sapino]

Kelvin:

I used to use CPCS. Since I switched to ABCs, I use the CW reporter and
Tintools viewer, which I like better. I have a bunch of apps written in Cw
using the cw IDE report writer and they do not convert to CPCS by doing a
template change.

Chet Sapino

"Chet Sapino" <cs1...@voicenet.com> wrote in message

news:3a54...@news.softvelocity.com...

[Kelvin Chua]

Hi Chet,

Noted and thanks.

Kelvin Chua
SINGAPORE

"Chet Sapino" <cs1...@voicenet.com> wrote in message

news:3a54...@news.softvelocity.com...

[Michael Gould]

Chet,

There are a couple of reasons to write the code in SQL that I can think
of... One is for pure referrential integrity. If you have it all in CW, then
the DB is wide open for someone to destroy the integrity of your data via
other products.

The other reason is that CW is a record by record processor. Most SQL db's
like Oracle, Sybase, MsSQL are recordset oriented and many times processing
record by record is very inefficent. For example, if I want to update a
process date on a few hundred or thousand records.... what is more
efficient,

SET(key)
LOOP
NEXT()
processing
END

for each row

or

Update tablename set columnname = date

Using prop:sql here is much more efficient. If your going to be doing some
heavy processing through the db, then creating a stored procedure using
cursor processing on the db is much more efficient than using Clarion.

There are many reasons to do things in SQL. Personally, I might not even go
to SQL if I was going to only use Clarion code. I think your missing the
power of the backend by using only Clarion code. We have processess that
took several hours which are now running in less than 10 minutes under the
engine using SQL and stored procedures. A much better use of the time and
effort in my opinion.

Michael Gould

"Chet Sapino" <cs1...@voicenet.com> wrote in message
news:3a53...@news.softvelocity.com...

[Kelvin Chua]

Hi Michael,

Let's say I have the following stored procedure;

SET TERM !! ;
CREATE PROCEDURE GETACCTCHRTS (ACCT_NO VARCHAR(15))
RETURNS (ACCOUNTNO VARCHAR(15), ACCOUNTNAME VARCHAR(40))
AS BEGIN
FOR SELECT ACCOUNTNO, ACCOUNTNAME
FROM ACCTCHRT
WHERE ACCOUNTNO = :acct_no
INTO :accountno, :accountname
DO
SUSPEND;
END !!
SET TERM ; !!

And that I know by keying in the following will return me the result I want;

SELECT * FROM GETACCTCHRTS ('10100')

How can I activate this stored procedure in Clarion??

And... what if I only require the stored procedure to be activated and I
need no results from it? That is to say I would only want to invoke the
stored procedure and perform all the processing at the server side. If that
store procedure is called PROCESSALLTRANSACTIONS, how can I call it from
Clarion side??

Thanks.

Kelvin Chua
SINGAPORE
... learning SQL bits by bits...
3:25am 05 JAN 2001

"Michael Gould" <mgo...@omnicc.com> wrote in message
news:3a54...@news.softvelocity.com...

[Michael Gould]

Kelvin,

I've not seen a "Select * from GETACCTCHRTS" processing done in clarion.
You need to use either a CALL or a NORESULTCALL.
Your Getacctchrts stored procedure if using C5 needs to be changed to not
have a return statement but at the end to a
Select Accountno, Accountname. You will need a view or filestructure that
matches this layout to return your results into

so you would have

View{Prop:SQL} = 'Call GetAcctChrts(' & ''' & passedaccountvar & ''')'

If you just want to run a stored procedure on the backend but not return
anything here's an example of one we are using

Access:Config.File{PROP:Sql} = 'NORESULTCALL DBA.SRecapMakeView(''' &
CLIP(Access:SRecap.GetName()) & ''')' !Modify SRecap View

HTH's

Michael Gould

"Kelvin Chua" <kel...@accpro.com.sg> wrote in message

news:3a54...@news.softvelocity.com...

[Kelvin Chua]

Hi Michael,

Thanks and I will try it out.

Kelvin Chua
SINGAPORE