# sqllb.4gl
# Functions for generic SQL statements

INCLUDE SYSTEM "ixconn.4gh"
INCLUDE SYSTEM "ixstmt.4gh"

#------------------------------------------------------------------------
FUNCTION selCount(tableName CHAR(*),
                  whereString CHAR(*)) RETURNING INTEGER
# Input: Table to get COUNT(*) from,
#        Any WHERE criteria needed
# Assumes: tableName exists in database, appropriate permissions
# Purpose: Do SELECT COUNT(*) of the named table
# Returns: Results of SELECT
#------------------------------------------------------------------------
# get a count of values
VARIABLE sqlString CHAR(*)
VARIABLE mySQLStmt ixSQLStmt
VARIABLE myRow ixRow
VARIABLE myValue ixValue
VARIABLE lcount INTEGER

LET mySQLStmt = NEW ixSQLStmt()
LET sqlString = "SELECT COUNT(*) FROM ", tableName CLIPPED, " ",
                "WHERE ", whereString CLIPPED

CALL mySQLStmt.prepare(sqlString)
CALL mySQLStmt.execute()
LET myRow = mySQLStmt.fetch()

LET myValue = myRow.getVal(1)
LET lcount = myValue.getValueStr()

RETURN lcount

END FUNCTION # selCount(tableName CHAR(*), whereString CHAR(*))

#------------------------------------------------------------------------
FUNCTION selMax(tableName CHAR(*),
                colName CHAR(*),
                whereString CHAR(*)) RETURNING INTEGER
# Input: Table & Column to get MAX(*) of,
#        Any WHERE criteria needed
# Assumes: tableName.colName exists in database, appropriate permissions
# Purpose: Do SELECT MAX(colName) of the named table
# Returns: Results of SELECT
#------------------------------------------------------------------------
# get a count of values
VARIABLE sqlString CHAR(*)
VARIABLE mySQLStmt ixSQLStmt
VARIABLE myRow ixRow
VARIABLE myValue ixValue
VARIABLE lMax CHAR(*)

LET mySQLStmt = NEW ixSQLStmt()
LET sqlString = "SELECT MAX(", colName CLIPPED, ") FROM ",
                tableName CLIPPED, " ",
                "WHERE ", whereString CLIPPED

CALL mySQLStmt.prepare(sqlString)
CALL mySQLStmt.execute()
LET myRow = mySQLStmt.fetch()

LET myValue = myRow.getVal(1)
LET lMax = myValue.getValueStr()

RETURN lMax

END FUNCTION # selMax(tableName CHAR(*), colName CHAR(*), ...

#------------------------------------------------------------------------
FUNCTION selMin(tableName CHAR(*),
                colName CHAR(*),
                whereString CHAR(*)) RETURNING INTEGER
# Input: Table & Column to get MIN(*) of,
#        Any WHERE criteria needed
# Assumes: tableName.colName exists in database, appropriate permissions
# Purpose: Do SELECT MIN(colName) of the named table
# Returns: Results of SELECT
#------------------------------------------------------------------------
# get a count of values
VARIABLE sqlString CHAR(*)
VARIABLE mySQLStmt ixSQLStmt
VARIABLE myRow ixRow
VARIABLE myValue ixValue
VARIABLE lMin CHAR(*)

LET mySQLStmt = NEW ixSQLStmt()
LET sqlString = "SELECT MIN(", colName CLIPPED, ") FROM ",
                tableName CLIPPED, " ",
                "WHERE ", whereString CLIPPED

CALL mySQLStmt.prepare(sqlString)
CALL mySQLStmt.execute()
LET myRow = mySQLStmt.fetch()

LET myValue = myRow.getVal(1)
LET lMin = myValue.getValueStr()

RETURN lMin

END FUNCTION # selMin(tableName CHAR(*), colName CHAR(*), ...