Get SUM from MSSQL in Clarion

[rob_bac]

Maybe a simple question, but i miss the clue.

I have a MSSQL file, where i want to total a field, between two dates (LOC:DAT1 and LOC:DAT2)
In SSMS it’s simple:
SELECT SUM(fil:field) FROM file WHERE (LOC:DAT1>=fil:date AND fil:date<=LOC:DAT2)
this retuns the total sum of fil:field

But what is my Clarion equivalent to get this total?
Thanks for your help!

Rob

[KevinErskine]

check out PROP:SQL and PROP:SQLRowSet

Here is from the help (Not specific to your request)

MySQLiteFile{PROP:SQLRowSet} = 'WITH q AS (SELECT COUNT(*) FROM f) SELECT * FROM q'

[seanh]

That’s the sort of thing I use a Dummy table for. I have a couple with different columns defined like
STRING(100)
REAL
LONG
DATE
etc. The just use Dummy{prop:sql}=‘your sql’.
The Dummy table needs the /TurboSQL switch: which really means don’t check the columns.
Note that if the above was the table layout I’d return 2 fields NULL,sum() to put the number into the real.

[rob_bac]

Thanks Sean,

But how will i get the result in a variabele so i can process it?

[seanh]

Easy. Assuming the fields I put above are a table layout called Dummy with prefix Dum: and the field names are the same as the type.

Dummy{prop:SQL}='SELECT NULL,SUM(field) FROM file WHERE (LOC:DAT1>=date AND LOC:DAT2<=date)'
NEXT(Dummy)

The Sum is now in Dum:Real and you use it as per normal.

[Mark_Sarson]

One Clarion-specific option that hasn’t been mentioned yet, but probably presumed, is /TURBOSQL on the SQL driver.

When /TURBOSQL=TRUE is used, the SQL driver does not validate the table structure at OPEN time and instead matches the result set by ordinal position, not by column name. This allows you to receive results from ad-hoc SQL (including aggregates like SUM()) without defining a VIEW that exactly matches the server schema.

Example:

MyResult FILE, DRIVER('MSSQL', '/TURBOSQL=TRUE')
Record   RECORD
TotalQty LONG
         END
         END

CODE
OPEN(MyResult)
MyResult{PROP:SQL} = |
  'SELECT SUM(Qty) FROM Products'

This works because the driver maps the first column in the result set to the first field in the RECORD buffer.

Important caveat (from Clarion help):
/TURBOSQL should only be used for receiving PROP:SQL result sets.
If you perform any normal Clarion file I/O (GET/PUT/ADD via the driver), you can get corrupted data or GPFs.

So for read-only / aggregate queries, this is often the simplest solution.

Clarion Help reference:
SQL Driver Strings → TURBOSQL

[Rick_UpperPark]

I also need to mention that if you are using /TURBOSQL beware there is a bug when you connect your application to multiple databases. Sometimes the “turboSQL” table will connect to a database different than what the OWNER is set to.
Most of us are only using a single database in the application so this isn’t any issue, but it is something to be aware of.

[Rick_UpperPark]

Another method is to use Prop:Name on a View. See in the Clarion help about view support for aggregate functions and Prop:Name - SQL.

VIEW support for aggregate functions

This is a pretty neat topic from the help. The SQL view engine supports PROP:GroupBy and PROP:Having. These properties allow you to add respectively GROUP BY and HAVING SQL clauses to your SELECT statement. PROP:GroupBy must be set first to allow PROP:Having to be generated. Example: PROGRAM MAP END EMP FILE,DRIVER('ORACLE'),NAME('EMP'),PRE(EMP) P_EKY_EMP KEY(EMP:EMPNO),NOCASE,OPT,PRIMARY KEY_DEP KEY(EMP:DEPTNO),DUP,NOCASE,OPT Record RECORD EMPNO SHORT …

[jslarve]

Another thing to keep in mind, especially if you are writing as a 3rd party, is that some files could have PROP:SQL turned off. Clarion Object Based Database Drivers Documentation

[Bruce]

Just to be clear; prop:sql turned off, SQL() command turned on.

And to be even more pedantic, writing prop:sql turned off, reading prop:sql remains.

[rob_bac]

Thansk everybody for the help.
It’s better now, only a problem rizes when i add the WHERE option.
In detail the PROP{SQL} is now:

SELECT SUM(FKT_INK_BEDR), SUM(FKT_OMZ_EX) FROM BAC5300 WHERE (LOC:DAT1>=FKT_DATUM AND LOC:DAT2<=FKT_DATUM)

But it gives a problem with the two local fields (LOC:DAT1, LOC:DAT2)
All variabeles are binded, but this error i get now (from the log file):

02A04H(4) 12:16:33.958 Preparing Statement 04F3FE48H : SELECT SUM(FKT_INK_BEDR), SUM(FKT_OMZ_EX) FROM BAC5300 WHERE (LOC:DAT1>=FKT_DATUM AND FKT_DATUM<=LOC:DAT2) Time Taken:0.00 secs
02A04H(4) 12:16:33.958 Executing prepared Statement 04F3FE48H
02A04H(4) 12:16:33.958 Error Occurred: 37000 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'LOC:'.
[Microsoft][ODBC SQL Server Driver][SQL Server]The label 'LOC' has already been declared. Label names must be unique within a query batch or stored procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
02A04H(4) 12:16:33.958  Time Taken:0.00 secs
02A04H(4) 12:16:33.958 Getting Number of columns for Statement 04F3FE48H Time Taken:0.00 secs
02A04H(4) 12:16:33.958 SET_PROPERTY(RESULT:05A802C8H) Incorrect syntax near 'LOC:'.
The label 'LOC' has already been declared. Label names must be unique within a query batch or stored procedure.
Statement(s) could not be prepared. Time Taken:0.00 secs

Any suggestions.
Thanks
Rob

[Bruce]

Don’t use colons in the names of variables that you plan to BIND into a filter.
BIND let’s you set the “bound” name - don’t use a colon in there, or in the WHERE statement.

[rob_bac]

When using:

SELECT SUM(FKT_INK_BEDR), SUM(FKT_OMZ_EX) FROM BAC5300 WHERE (LOC_DAT1>=FKT_DATUM AND FKT_DATUM<=LOC_DAT2)

I get the eror:
Invalid column name ‘LOC_DAT1’
Invalid column name ‘LOC_DAT2’

I’m a bit lost now

[Bruce]

What do your BIND statements look like?

[JonW]

It’s not the colons are the problem, it’s that PROP:SQL sends that string just like it is to the back end, and SQL server doesn’t have a clue what LOC:dat1 or loc_dat1 are. They are variable names in your clarion program that the server knows nothing about.

If those values are numbers, then you can do:

x{PROP:SQL} = SELECT SUM(FKT_INK_BEDR), SUM(FKT_OMZ_EX) FROM BAC5300 WHERE (’ & loc:dat1 & '>= fkt_datum and fkt_datum <= ’ & loc:dat2

If they are strings, then you will need either two single quotes in you PROP:SQL string before and after, or <39>.

Also, does MSSQL not have a Between operator? i.e. fkt_datum between low_bound and high_bound?

Bruce will probably point out that this is a good example of a SQL injection vulnerability, which the new drivers (with bound parameters) get rid of.

[rob_bac]

Thank you all!
Indeed like Jon said, it sends a string and LOC:DAT1 and LOC:DAT2 should not be in the string itself.
So this is the solution (for users who come to the same problem);
I define a file:

Result  FILE,DRIVER('MSSQL', '/TurboSQL=true'),NAME('RESULT'),PRE(RES)
Record    RECORD
INKOOP      REAL
OMZET       REAL
          END
        END

Then LOC:DAT1 (begindate) and LOC:DAT2 (enddate) are two Clarion dates, defined as LONG and binded.

To get the information i coded:

OPEN(Result)
Result{PROP:SQL}='SELECT SUM(FKT_INK_BEDR), SUM(FKT_OMZ_EX) FROM BAC5300 WHERE (FKT_DATUM BETWEEN '&LOC:DAT1&' AND '&LOC:DAT2&')'
NEXT(Result)
LOC:INK1=RES:INKOOP
LOC:JR1=RES:OMZET
CLOSE(Result)

[Bruce]

@JonW

Bruce will probably point out that this is a good example of a SQL injection vulnerability,

True, but Clarion programmers in general dont really care much about security.

[Bruce]

@rob_bac

Indeed like Jon said, it sends a string and LOC:DAT1 and LOC:DAT2 should not be in the string itself.

Sort of. If you BIND the fields then the value should be substituted in the filter before it is sent to the server. (I’ll confirm that tomorrow, its been a while since I last tested that on the old drivers.)

Of course using BIND does not mitigate the security implications; prop:Sql is inherently insecure even if you only use database fields.