(********************************************************************) (* *) (* sql_base.s7i Base SQL access library *) (* Copyright (C) 2013, 2014 Thomas Mertes *) (* *) (* This file is part of the Seed7 Runtime Library. *) (* *) (* The Seed7 Runtime Library is free software; you can *) (* redistribute it and/or modify it under the terms of the GNU *) (* Lesser General Public License as published by the Free Software *) (* Foundation; either version 2.1 of the License, or (at your *) (* option) any later version. *) (* *) (* The Seed7 Runtime Library is distributed in the hope that it *) (* will be useful, but WITHOUT ANY WARRANTY; without even the *) (* implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR *) (* PURPOSE. See the GNU Lesser General Public License for more *) (* details. *) (* *) (* You should have received a copy of the GNU Lesser General *) (* Public License along with this program; if not, write to the *) (* Free Software Foundation, Inc., 51 Franklin Street, *) (* Fifth Floor, Boston, MA 02110-1301, USA. *) (* *) (********************************************************************) include "float.s7i"; include "time.s7i"; include "duration.s7i"; include "bigint.s7i"; include "bigrat.s7i"; include "bstring.s7i"; (** * Enumeration type to describe database drivers. * * NO_DB No database * * DB_MYSQL MySql/MariaDb * * DB_SQLITE SQLLite * * DB_POSTGRESQL PostgreSQL * * DB_OCI Oracle * * DB_ODBC Odbc * * DB_FIRE Firebird/Interbase * * DB_DB2 Db2 * * DB_SQL_SERVER SQL Server * * DB_TDS Tabular Data Stream * * DB_INFORMIX Informix *) const type: dbCategory is new enum NO_DB, DB_MYSQL, DB_SQLITE, DB_POSTGRESQL, DB_OCI, DB_ODBC, DB_FIRE, DB_DB2, DB_SQL_SERVER, DB_TDS, DB_INFORMIX end enum; const func string: str (in dbCategory: driver) is return literal(driver); const func dbCategory: dbCategory (in string: name) is func result var dbCategory: driver is NO_DB; local var string: ucName is ""; begin ucName := upper(name); if ucName = "MYSQL" or ucName = "MY" then driver := DB_MYSQL; elsif ucName = "SQLITE" or ucName = "LITE" then driver := DB_SQLITE; elsif ucName = "POSTGRESQL" or ucName = "POST" then driver := DB_POSTGRESQL; elsif ucName = "ORACLE" or ucName = "OCI" then driver := DB_OCI; elsif ucName = "ODBC" then driver := DB_ODBC; elsif ucName = "FIRE" or ucName = "FIREBIRD" or ucName = "INTERBASE" then driver := DB_FIRE; elsif ucName = "DB2" then driver := DB_DB2; elsif ucName = "SQLSRV" or ucName = "SQL_SERVER" or ucName = "SQL SERVER" then driver := DB_SQL_SERVER; elsif ucName = "TDS" then driver := DB_TDS; elsif ucName = "INFORMIX" then driver := DB_INFORMIX; end if; end func; const func dbCategory: (attr dbCategory) parse (in string: name) is return dbCategory(name); enable_io(dbCategory); (** * Abstract data type to store database connections. *) const type: database is newtype; IN_PARAM_IS_REFERENCE(database); const proc: (ref database: dest) ::= (ref database: source) is action "SQL_CREATE_DB"; const proc: destroy (ref database: aValue) is action "SQL_DESTR_DB"; const proc: (inout database: dest) := (ref database: source) is action "SQL_CPY_DB"; const func database: _GENERATE_EMPTY_DATABASE is action "SQL_EMPTY_DB"; (** * Default value of ''database'' (empty database). *) const database: (attr database) . value is _GENERATE_EMPTY_DATABASE; (** * Check if two database connections are equal. * @return TRUE if the two database connections are equal, * FALSE otherwise. *) const func boolean: (in database: db1) = (in database: db2) is action "SQL_EQ_DB"; (** * Check if two database connections are not equal. * @return FALSE if the two database connections are equal, * TRUE otherwise. *) const func boolean: (in database: db1) <> (in database: db2) is action "SQL_NE_DB"; const func integer: DRIVER_NUM (in database: db) is action "SQL_DRIVER"; (** * Abstract data type to store a prepared sql statement. *) const type: sqlStatement is newtype; IN_PARAM_IS_REFERENCE(sqlStatement); const proc: (ref sqlStatement: dest) ::= (ref sqlStatement: source) is action "SQL_CREATE_STMT"; const proc: destroy (ref sqlStatement: aValue) is action "SQL_DESTR_STMT"; const proc: (inout sqlStatement: dest) := (ref sqlStatement: source) is action "SQL_CPY_STMT"; const func sqlStatement: _GENERATE_EMPTY_STATEMENT is action "SQL_EMPTY_STMT"; (** * Default value of ''sqlStatement'' (empty prepared sql statement). *) const sqlStatement: (attr sqlStatement) . value is _GENERATE_EMPTY_STATEMENT; (** * Check if two prepared sql statements are equal. * @return TRUE if the two prepared sql statements are equal, * FALSE otherwise. *) const func boolean: (in sqlStatement: stmt1) = (in sqlStatement: stmt2) is action "SQL_EQ_STMT"; (** * Check if two prepared sql statements are not equal. * @return FALSE if the two prepared sql statements are equal, * TRUE otherwise. *) const func boolean: (in sqlStatement: stmt1) <> (in sqlStatement: stmt2) is action "SQL_NE_STMT"; const proc: BIND_BIG_RAT (in sqlStatement: statement, in integer: pos, in bigInteger: numerator, in bigInteger: denominator) is action "SQL_BIND_BIGRAT"; const proc: BIND_TIME (in sqlStatement: statement, in integer: pos, in integer: year, in integer: month, in integer: day, in integer: hour, in integer: minute, in integer: second, in integer: micro_second, in integer: timeZone) is action "SQL_BIND_TIME"; const proc: BIND_DURATION (in sqlStatement: statement, in integer: pos, in integer: year, in integer: month, in integer: day, in integer: hour, in integer: minute, in integer: second, in integer: micro_second) is action "SQL_BIND_DURATION"; const proc: COLUMN_BIG_RAT (in sqlStatement: statement, in integer: column, inout bigInteger: numerator, inout bigInteger: denominator) is action "SQL_COLUMN_BIGRAT"; const proc: COLUMN_TIME (in sqlStatement: statement, in integer: column, inout integer: year, inout integer: month, inout integer: day, inout integer: hour, inout integer: minute, inout integer: second, inout integer: micro_second, inout integer: timeZone, inout boolean: daylightSavingTime) is action "SQL_COLUMN_TIME"; const proc: COLUMN_DURATION (in sqlStatement: statement, in integer: column, inout integer: year, inout integer: month, inout integer: day, inout integer: hour, inout integer: minute, inout integer: second, inout integer: micro_second) is action "SQL_COLUMN_DURATION"; const func database: openDatabase (DB_MYSQL, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_MY"; const func database: openDatabase (DB_SQLITE, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_LITE"; const func database: openDatabase (DB_POSTGRESQL, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_POST"; const func database: openDatabase (DB_OCI, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_OCI"; const func database: openDatabase (DB_FIRE, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_FIRE"; const func database: openDatabase (DB_DB2, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_DB2"; const func database: openDatabase (DB_SQL_SERVER, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_SQLSRV"; const func database: openDatabase (DB_TDS, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_TDS"; (** * Open database with ''host'', ''port'', ''dbName'' ''user'' and ''password''. * @param driver Database driver to be used. * @param host The host can be specified by name, with an IPv4 address or with * an IPv6 address. If ''host'' is empty it is assumed to be "localhost". * @param port The port to be used to access the database. If ''port'' is * zero the default port of the database or driver is used. * @param dbName The name of the database at the specified ''host''. * @param user Database user name. * @param password Database password. * @return the database connection. * @exception RANGE_ERROR If dbPath, user or password cannot be converted to * the character set of the database. * @exception DATABASE_ERROR If the DLL of the database could not be found, or * the connection to the database failed. *) const func database: openDatabase (in dbCategory: driver, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is DYNAMIC; (** * Open ODBC database with ''odbcDriver'', ''server'', ''dbName'' ''user'' and ''password''. * @param driver Database driver to be used (DB_ODBC). * @param odbcDriver The name of the ODBC driver (e.g.: "sqlserver"). * @param server The database server to be used. * @param dbName The name of the database at the specified ''server''. * @param user Database user name. * @param password Database password. * @return the database connection. * @exception RANGE_ERROR If dbPath, user or password cannot be converted to * the character set of the database. * @exception DATABASE_ERROR If the DLL of the database could not be found, or * the connection to the database failed. *) const func database: openDatabase (DB_ODBC, in string: odbcDriver, in string: server, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_ODBC"; (** * Open Informix database with ''host'', ''port'', ''server'', ''dbName'' ''user'' and ''password''. * @param driver Database driver to be used (DB_INFORMIX). * @param host The host can be specified by name, with an IPv4 address or with * an IPv6 address. If ''host'' is empty it is assumed to be "localhost". * @param port The port to be used to access the database. If ''port'' is * zero the default port of the database or driver is used. * @param server The database server to be used. * @param dbName The name of the database at the specified ''server''. * @param user Database user name. * @param password Database password. * @return the database connection. * @exception RANGE_ERROR If dbPath, user or password cannot be converted to * the character set of the database. * @exception DATABASE_ERROR If the DLL of the database could not be found, or * the connection to the database failed. *) const func database: openDatabase (DB_INFORMIX, in string: host, in integer: port, in string: server, in string: dbName, in string: user, in string: password) is action "SQL_OPEN_INFORMIX"; const func database: openDatabase (DB_INFORMIX, in string: host, in integer: port, in string: dbName, in string: user, in string: password) is func result var database: db is database.value; local var integer: slashPos is 0; var string: server is ""; var string: databaseName is ""; begin slashPos := pos(dbName, '/'); if slashPos <> 0 then server := dbName[.. pred(slashPos)]; databaseName := dbName[succ(slashPos) ..]; else server := dbName; databaseName := dbName; end if; db := openDatabase(DB_INFORMIX, host, port, server, databaseName, user, password); end func; (** * Open the database ''dbPath'' with the specified ''user'' and ''password''. * The ''dbPath'' can be in one of the forms: * * host:port/dbName * * host/dbName * * dbName * The ''host'' can be specified with name (e.g.: www.example.org), or with * IPv4 address (e.g.: 192.0.2.235) or as IPv6 address in colon notation * (e.g.: [1234:feed::dead:beef]). Note that an IPv6 address must be enclosed * in brackets. * * If ''driver'' is DB_ODBC the ''dbPath'' can be in one of the forms: * * odbcDriver:dbServer/dbName * * odbcDriver:dbServer * * dbServer/dbName * * odbcDataSourceName * The ''odbcDriver'' can have a value like ''sqlserver''. * * If ''driver'' is DB_SQLITE the ''dbPath'' is the path of a database file: * * sqlitePath * The ''sqlitePath'' uses the Seed7 standard path representation. * * @param driver Database driver to be used. * @param dbPath Database name in one of the forms listed above. * If no host is specified the host "localhost" is used. * If no port is specified the port of the database driver is used. * If no odbcDriver is specified "sqlserver" is used. * @param user Database user name. * @param password Database password. * @return the database connection. * @exception RANGE_ERROR If dbPath, user or password cannot be converted to * the character set of the database. * @exception DATABASE_ERROR If the DLL of the database could not be found, or * the connection to the database failed. *) const func database: openDatabase (in dbCategory: driver, in string: dbPath, in string: user, in string: password) is func result var database: db is database.value; local var string: host is ""; var string: port is ""; var string: dbName is ""; var string: odbcDriver is ""; var string: dbServer is ""; var integer: portNum is 0; var integer: slashPos is 0; var integer: rightBracketPos is 0; var integer: colonPos is 0; begin if driver = DB_ODBC then colonPos := pos(dbPath, ':'); if colonPos <> 0 then odbcDriver := dbPath[.. pred(colonPos)]; dbServer := dbPath[succ(colonPos) ..]; slashPos := pos(dbServer, '/'); if slashPos <> 0 then dbName := dbServer[succ(slashPos) ..]; dbServer := dbServer[.. pred(slashPos)]; end if; else slashPos := pos(dbPath, '/'); if slashPos <> 0 then odbcDriver := "sqlserver"; dbServer := dbPath[.. pred(slashPos)]; dbName := dbPath[succ(slashPos) ..]; else dbName := dbPath; end if; end if; db := openDatabase(DB_ODBC, odbcDriver, dbServer, dbName, user, password); else slashPos := pos(dbPath, '/'); if driver <> DB_SQLITE and slashPos <> 0 then dbName := dbPath[succ(slashPos) ..]; host := dbPath[.. pred(slashPos)]; if host <> "" and host[1] = '[' then rightBracketPos := pos(host, ']'); if rightBracketPos <> 0 then if length(host) >= succ(rightBracketPos) and host[succ(rightBracketPos)] = ':' then port := host[rightBracketPos + 2 ..]; end if; host := host[2 .. pred(rightBracketPos)]; else host := host[2 ..]; end if; else colonPos := pos(host, ':'); if colonPos <> 0 then port := host[succ(colonPos) ..]; host := host[.. pred(colonPos)]; end if; end if; else dbName := dbPath; end if; # writeln("host: " <& host); # writeln("port: " <& port); # writeln("dbName: " <& dbName); if port <> "" then portNum := integer(port); end if; db := openDatabase(driver, host, portNum, dbName, user, password); end if; end func; (** * Open a database with the specified ''driver'' and ''connectStri''. * The ''connectStri'' must be in one of the forms * * user:password@dbPath * * user@dbPath * * dbPath * The ''dbPath'' can be specified with host name ("e.g.: "www.example.org/myDb"), * or with IPv4 address in standard dot notation (e.g.: "192.0.2.235/myDb"). * Operating systems supporting IPv6 may also accept an IPv6 address * in colon notation. Some databases allow also the specification of a port * number (e.g.: "localhost:1234/myDb" or "[1234:feed::dead:beef]:1234/myDb", with * an IPv6 address). * @param driver Database driver to be used. * @param connectStri Connection string in one of the forms listed above. * If no user is specified the user "guest" is used. * If no password is specified the password "guest" is used. * @return the database connection. * @exception RANGE_ERROR If dbPath, user or password cannot be converted to * the character set of the database. * @exception DATABASE_ERROR If the DLL of the database could not be found, or * the connection to the database failed. *) const func database: openDatabase (in dbCategory: driver, in string: connectStri) is func result var database: db is database.value; local var string: dbPath is ""; var string: user is "guest"; var string: password is "guest"; var integer: atPos is 0; var integer: colonPos is 0; begin atPos := pos(connectStri, '@'); if atPos <> 0 then user := connectStri[.. pred(atPos)]; colonPos := pos(user, ':'); if colonPos <> 0 then password := user[succ(colonPos) ..]; user := user[.. pred(colonPos)]; end if; dbPath := connectStri[succ(atPos) ..]; else dbPath := connectStri; end if; # writeln("dbPath: " <& dbPath); # writeln("user: " <& user); # writeln("password: " <& password); db := openDatabase(driver, dbPath, user, password); end func; (** * Close the specified database ''db''. * @param database Database to be closed. * @exception RANGE_ERROR If the database was not open. *) const proc: close (in database: db) is action "SQL_CLOSE"; (** * Create a prepared statement for the given database ''db''. * @param db Database connection for which the prepared statement should * be created. * @param sqlStatementStri SQL statement in a string. For bind variables * use a question mark (?). * @exception RANGE_ERROR If the database is not open. *) const func sqlStatement: prepare (in database: db, in string: sqlStatementStri) is action "SQL_PREPARE"; (** * Bind a bigInteger parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param num Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''num'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in bigInteger: num) is action "SQL_BIND_BIGINT"; (** * Bind a bigRational parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param bigRatData Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''bigRatData'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in bigRational: bigRatData) is func begin BIND_BIG_RAT(statement, pos, bigRatData.numerator, bigRatData.denominator); end func; (** * Bind a boolean parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param flag Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''flag'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in boolean: flag) is action "SQL_BIND_BOOL"; (** * Bind a bstring parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param bstri Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''bstri'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in bstring: bstri) is action "SQL_BIND_BSTRI"; (** * Bind a float parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param number Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''number'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in float: number) is action "SQL_BIND_FLOAT"; (** * Bind an integer parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param number Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''number'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in integer: number) is action "SQL_BIND_INT"; (** * Bind a NULL parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, NULL) is action "SQL_BIND_NULL"; (** * Bind a string parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param stri Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''stri'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in string: stri) is action "SQL_BIND_STRI"; (** * Bind a time parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param timeData Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''timeData'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in time: timeData) is func begin BIND_TIME(statement, pos, timeData.year, timeData.month, timeData.day, timeData.hour, timeData.minute, timeData.second, timeData.micro_second, timeData.timeZone); end func; (** * Bind a duration parameter to a prepared SQL statement. * @param statement Prepared statement. * @param pos Position of the bind variable (starting with 1). * @param durationData Value for the binding. * @exception RANGE_ERROR If the statement was not prepared or * if ''pos'' is negative or too big or * if ''durationData'' cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const proc: bind (inout sqlStatement: statement, in integer: pos, in duration: durationData) is func begin BIND_DURATION(statement, pos, getYears(durationData), getMonths(durationData), durationData.day, durationData.hour, durationData.minute, durationData.second, durationData.micro_second); end func; (** * Execute the specified prepared SQL ''statement''. * Bind variable can be assigned with the function ''bind'' before * ''execute'' is called. * @param statement Prepared statement, which should be executed. * @exception DATABASE_ERROR If a database function fails. *) const proc: execute (inout sqlStatement: statement) is action "SQL_EXECUTE"; (** * Fetch a row from the result data of an executed ''statement''. * After the ''statement'' has been executed successfully the * function ''fetch'' can be used to get the first and further * rows of the statements result data. The columns of the * result data can be obtained with the ''column'' functions. * @param statement Prepared statement, which has been executed. * @return TRUE if a row of result data could be fetched successfully. * FALSE if no more result data is available. * @exception DATABASE_ERROR If a database function fails. *) const func boolean: fetch (in sqlStatement: statement) is action "SQL_FETCH"; (** * Get the specified column of fetched data as [[bigint|bigInteger]]. * If the column data is NULL it is interpreted as 0_. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from 0_. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[bigint|bigInteger]] or * 0_, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func bigInteger: column (in sqlStatement: statement, in integer: column, attr bigInteger) is action "SQL_COLUMN_BIGINT"; (** * Get the specified column of fetched data as [[bigrat|bigRational]]. * If the column data is NULL it is interpreted as 0_/1_. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from 0_/1_. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[bigrat|bigRational]] or * 0_/1_, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func bigRational: column (in sqlStatement: statement, in integer: column, attr bigRational) is func result var bigRational: rationalData is bigRational.value; begin COLUMN_BIG_RAT(statement, column, rationalData.numerator, rationalData.denominator); reduce(rationalData); end func; (** * Get the specified column of fetched data as [[boolean]]. * If the column data is NULL it is interpreted as FALSE. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from FALSE. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[boolean]] or * FALSE, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func boolean: column (in sqlStatement: statement, in integer: column, attr boolean) is action "SQL_COLUMN_BOOL"; (** * Get the specified column of fetched data as [[bstring]]. * If the column data is NULL it is interpreted as empty bstring. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from an empty bstring. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[bstring]] or * an empty bstring, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func bstring: column (in sqlStatement: statement, in integer: column, attr bstring) is action "SQL_COLUMN_BSTRI"; (** * Get the specified column of fetched data as [[duration]]. * If the column data is NULL it is interpreted as empty duration. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from an empty duration. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[duration]] or * an empty duration, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func duration: column (in sqlStatement: statement, in integer: column, attr duration) is func result var duration: durationData is duration.value; begin COLUMN_DURATION(statement, column, durationData.year_365, durationData.month_30, durationData.day, durationData.hour, durationData.minute, durationData.second, durationData.micro_second); end func; (** * Get the specified column of fetched data as [[float]]. * If the column data is NULL it is interpreted as 0.0. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from 0.0. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[float]] or * 0.0, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func float: column (in sqlStatement: statement, in integer: column, attr float) is action "SQL_COLUMN_FLOAT"; (** * Get the specified column of fetched data as [[integer]]. * If the column data is NULL it is interpreted as 0. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from 0. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to an [[integer]] or * 0, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func integer: column (in sqlStatement: statement, in integer: column, attr integer) is action "SQL_COLUMN_INT"; (** * Get the specified column of fetched data as [[string]]. * If the column data is NULL it is interpreted as "". * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from "". * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[string]] or * "", if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func string: column (in sqlStatement: statement, in integer: column, attr string) is action "SQL_COLUMN_STRI"; (** * Get the specified column of fetched data as [[time]]. * If the column data is NULL it is interpreted as 0-01-01 00:00:00. * The function [[#isNull(in_sqlStatement,in_integer)|isNull]] can distinguish NULL from 0-01-01 00:00:00. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return the column data converted to a [[time]] or * 0-01-01 00:00:00, if the column data is NULL. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist or * if the column cannot be converted. * @exception DATABASE_ERROR If a database function fails. *) const func time: column (in sqlStatement: statement, in integer: column, attr time) is func result var time: timeData is time.value; begin COLUMN_TIME(statement, column, timeData.year, timeData.month, timeData.day, timeData.hour, timeData.minute, timeData.second, timeData.micro_second, timeData.timeZone, timeData.daylightSavingTime); end func; (** * Determine if the specified column of fetched data is NULL. * @param statement Prepared statement for which data was fetched. * @param column Number of the column (starting with 1). * @return TRUE if the column data is NULL, * FALSE otherwise. * @exception RANGE_ERROR If the statement was not prepared or * if no data was successfully fetched or * if the specified column does not exist. *) const func boolean: isNull (in sqlStatement: statement, in integer: column) is action "SQL_IS_NULL"; (** * Get the current auto-commit mode for the specified database 'database'. *) const func boolean: getAutoCommit (in database: db) is action "SQL_GET_AUTO_COMMIT"; (** * Set the auto-commit mode for the specified database 'database'. *) const proc: setAutoCommit (in database: db, in boolean: autoCommit) is action "SQL_SET_AUTO_COMMIT"; (** * Execute a commit statement for the specified database ''db''. *) const proc: commit (in database: db) is action "SQL_COMMIT"; (** * Execute a rollback statement for the specified database ''db''. *) const proc: rollback (in database: db) is action "SQL_ROLLBACK"; (** * Return the number of columns in the result data of a ''statement''. * It is not necessary to ''execute'' the prepared statement, before * ''columnCount'' is called. * @param statement Prepared statement. *) const func integer: columnCount (in sqlStatement: statement) is action "SQL_STMT_COLUMN_COUNT"; (** * Return the name of a column in the result data of a ''statement''. * It is not necessary to ''execute'' the prepared statement, before * ''columnName'' is called. * @param statement Prepared statement. * @param column Number of the column (starting with 1). *) const func string: columnName (in sqlStatement: statement, in integer: column) is action "SQL_STMT_COLUMN_NAME"; const proc: execute (in database: db, in string: sqlStatementStri) is func local var sqlStatement: statement is sqlStatement.value; begin statement := prepare(db, sqlStatementStri); execute(statement); end func; const func string: libFunction (DATABASE_ERROR) is action "SQL_ERR_LIB_FUNC"; const func string: dbFunction (DATABASE_ERROR) is action "SQL_ERR_DB_FUNC"; const func integer: errCode (DATABASE_ERROR) is action "SQL_ERR_CODE"; const func string: errMessage (DATABASE_ERROR) is action "SQL_ERR_MESSAGE"; const func dbCategory: driver (in database: db) is return dbCategory conv DRIVER_NUM(db); const func boolean: quoteTableNames (in dbCategory: databaseKind) is func result var boolean: quoteTableNames is FALSE; begin case databaseKind of when {DB_MYSQL}: quoteTableNames := TRUE; when {DB_SQLITE}: quoteTableNames := TRUE; when {DB_POSTGRESQL}: quoteTableNames := TRUE; when {DB_OCI}: quoteTableNames := TRUE; when {DB_FIRE}: quoteTableNames := TRUE; when {DB_SQL_SERVER}: quoteTableNames := TRUE; when {DB_DB2}: quoteTableNames := TRUE; when {DB_INFORMIX}: quoteTableNames := FALSE; end case; end func; const func string: tableNamesCommand (in database: currDb, in dbCategory: databaseKind) is func result var string: getTableNames is ""; begin case databaseKind of when {DB_MYSQL}: getTableNames := "show tables"; when {DB_SQLITE}: getTableNames := "SELECT name FROM sqlite_master WHERE type='table'"; when {DB_POSTGRESQL}: getTableNames := "SELECT table_name FROM information_schema.tables WHERE table_schema='public'"; when {DB_OCI}: getTableNames := "SELECT table_name FROM user_tables"; when {DB_ODBC}: getTableNames := "SELECT table_name FROM information_schema.tables"; when {DB_FIRE}: getTableNames := "SELECT rdb$relation_name FROM rdb$relations \ \WHERE rdb$view_blr IS NULL AND (rdb$system_flag IS NULL OR rdb$system_flag = 0)"; when {DB_DB2}: getTableNames := "SELECT tabname FROM syscat.tables WHERE type = 'T' AND \ \tabschema=(SELECT current_schema FROM sysibm.sysdummy1)"; # when {DB_DB2}: getTableNames := "SELECT name FROM sysibm.systables WHERE type = 'T'"; when {DB_SQL_SERVER}: getTableNames := "SELECT table_name FROM information_schema.tables"; when {DB_INFORMIX}: getTableNames := "SELECT tabname FROM informix.systables"; end case; end func; const func array string: getTableNames (in database: currDb, in dbCategory: databaseKind) is func result var array string: tableNames is 0 times ""; local var string: getTableNames is ""; var sqlStatement: tableList is sqlStatement.value; var string: tableName is ""; begin getTableNames := tableNamesCommand(currDb, databaseKind); tableList := prepare(currDb, getTableNames); execute(tableList); while fetch(tableList) do tableName := column(tableList, 1, string); tableNames &:= tableName; end while; end func;