description.
The "global" [c-style basic] functions of the sql group contain basic functions to access a database. If you use these functions, you can access a single back-end database. The subroutines in this group are intended for the case when you intend to access a single database via a single connection for the life of the program.

This database can be accessed in [up to] 2 ways (depending on OS): ODBC and ADO. The latter is available only on Microsoft platforms. Which "access method" you use is determined by zdb_set_axmeth().

The general way how to use these functions is:

1. configure the way the connection is to be made: database type (eg vendor), connection style (ODBC, ADO);
2. open the [singleton back-end] database;
3. execute SQL statements via the available functions: -

   zdb_table_exists() - zdb_getID_byname() - zdb_tablename_to_ID() - zdb_numrows_intable() - zdb_column_maxvalue() - zdb_exec_sql() - zdb_drop_table() - zdb_clear_table() - zdb_numrows_match_criteria()

The configuration-setup routines [1] are necesary in order to (a) find the database system - ie, the tunnels in the OS used by ODBC is different from that of ADO. So in that sense, the access method could be considered to be a component of the RDBMS address: in addition to server name, database name, and account, information about to get to the database server is needed. And (b) One of the primary functions in this zone is zdb_exec_sql(). Although this will execute virtually any valid SQL statement, this function provides no support for getting information with the results of the query executed. Thus, it is adequate for SQL statements such as INSERT, DELETE, CREATE TABLE, and DROP TABLE. For SELECT statements, Vettrasoft recommends using the higher-level facilities relating to database manipulation (dbbi_o, orthodox_o, dbtable_o, etc).

A pair of functions [currently] exist to supplicate some legacy code that will be phased out in the future:

zdb_default_user() - tells what the "master" database user;
zdb_default_password() - retrives the "master" database password;
zdb_set_default_user() - sets "master" database user and password.
These exist for some older internal code only and should not be used.

Many of the subroutines here have an analogous member function in the [layer 2] dbconnection object. Often the subroutine is built on top of its analog. These will be pointed out in the function descriptions on this page.

[C] functions (aka subroutines):

zdb_masterhand()
SIGNATURE: dbconnection_o &zdb_masterhand()
SYNOPSIS:
returns a the singleton handle for accessing the main database. the object returned is a dbconnection object reference (dbconnection_o &).
DESCRIPTION:
the main (or "master") database is the first one opened. The assumption is that most applications will access at most 1 database. Hence, the first one opened is called the "master". This connection handle is set up by a successful call to z_open_database().
 

zdb_geohand()
SIGNATURE: dbconnection_o &zdb_geohand()
SYNOPSIS:
returns a the singleton handle for accessing the geo database. the object returned is a dbconnection object reference (dbconnection_o &).
DESCRIPTION:
this function is a sibling of the zdb_masterhand() and functions like it. The only difference is that it references the geo database. This database can be the same as the master database, or different, depending on where the geo database is. Opening the geo database should be done by calling the subroutine zgeo_open_database().
 

zdb_access_method()
SIGNATURE: zstyle_DBAccess zdb_access_method()
SYNOPSIS: tells what the current database access method is.
RETURNS:
[zstyle_DBAccess type] - current access method (enumeration)
zstyle_DBax_Undefined: zdb_set_axmeth() not invoked prior.
 

is_z_database_open()
SIGNATURE: boolean is_z_database_open (const string_o &dbname, int *pi = NULL)
SYNOPSIS:
returns TRUE if there is a back-end database currently open, and FALSE otherwise. Opening the database must have been done only via successful call to z_open_database(). Closing of an open database must be done only
PARAMETERS

• dbname: input variable specifying the database name. The value need not be set- set it to an empty string ("") in order to check the master (default) database. Otherwise, use an explicit name and a dbconnection object will be searched for that matches the database.
• pi: output error indicator variable. values:
  0: successful function call zErr_Memory_Exhausted: error in global initialization
  (panic)

RETURNS:
TRUE: database is currently open;
FALSE: database is currently closed.
TRAITS:
this function relies on an internal flag instead of querying the database directly, so if a low-level method of opening / closing a database was done (instead of invoking the authorized functions), the flag may get out of sync, resulting in an "incorrect" return value.
 

z_current_database_name()
SIGNATURE: string_o z_current_database_name (int *pi = NULL)
SYNOPSIS: returns the name of the database, if there is one currently open.
PARAMETERS

pi: output error indicator variable. values:
0: name [successfully] returned
zErr_Memory_Exhausted: failed to initialize internal variables (PANIC situation)

RETURNS:
[string]: database name, if there is one currently open;
"": no database is currently open.
 

zdb_vendor()
SIGNATURE: z_DBVendor zdb_vendor (int *pi = NULL)
SYNOPSIS:
This function returns the database manufacturer code, or "Vendor". These codes are simple enumerations for the database vendor-make-types relevant to the Z Directory:

1. zDB_mySQL - database type-maker to mySQL;
2. zDB_SQLServer - database type-maker to Microsoft SQL Server;
3. zDB_Oracle - database type-maker to Oracle;
4. zDB_Informix - database type-maker to Informix;
5. zDB_Other - database type-maker is some other not in the currennt list of database vendors;
6. zDB_Undefined - database type-maker is undefined (not set). This is the default value.

The list of database vendors is of type z_DBVendor, which is defined in z_dbconnection.h.
The database vendor should be set by the application. This is done by calling zdb_setvendor() with the enumeration code (type z_DBVendor).
 

zdb_use_brackets()
SIGNATURE: boolean zdb_use_brackets (int *pi)
SYNOPSIS:
Informs the caller whether or not the parameter 'use_brackets' is set to on (the default) or off. Turning off the usage of square-braces, aka brackets, can be done like so:

paramstring_o ps;
int ie = ps.set ("use_brackets", "NO");
::zdb_setproperty (ps, &ie);

This is done automatically by this call:

::zdb_setvendor(zDB_mySQL);

Actual application usage of this function is expected to be rare. It was created to assist the [layer 5] dbtable object load database tables (via its load() member function).
Note that the return value in no way implies or guarantees whether a database is currently open, or if the flag has been set / unset by the application.
RETURNS:
TRUE: the "use brackets" flag is ON. This is the default.
FALSE: the "use brackets" flag is OFF.
 

zdb_is_stricttype()
SIGNATURE: boolean zdb_is_stricttype (int *);
SYNOPSIS:
Informs the caller whether or not the parameter 'strict_types' has been set on or off (the default). Turning on this flag can be done like so:

paramstring_o ps;
int ie = ps.set ("strict_types", "YES");
::zdb_setproperty (ps, &ie);

When doing an INSERT (eg inserting a row of data into a table), some database systems require numeric values for numeric columns (mySQL), whilst others will accept an empty string and convert it to a zero value (SQL Server). A call to zdb_setvendor() will correctly set (or unset) this property.

 

zdb_last_error()
SIGNATURE: int zdb_last_error (string_o &errstr)
SYNOPSIS:
allows for getting the error codes from the last operation performed. Error information returned via 2 variables - integer return code, and string object parameter.
PARAMETERS

errstr: output variable, string object. Text of the object is set to the value of the last operation performed (if any).

RETURNS:
0: last operation was successful (or no operations have been performed yet)
!= 0: resultant error code of last operation.
TRAITS:
sometimes the numerical value of the last error is 0, even though there was an error and the string object is set. This has been observed in Microsoft environments when a database is attempted to be opened but no ODBC connector has been set up for the database.
 

zdb_default_user()
SIGNATURE: string_o zdb_default_user()
SYNOPSIS:
returns the global-scope "master" ('default') user account, as a string. This function will be phased out and should not be used!
TRAITS: this function is a relic. it will disappear soon.
 

zdb_default_password()
SIGNATURE: string_o zdb_default_password()
SYNOPSIS: returns the global-scope "master" ('default') password. This function will be phased out and should not be used!
TRAITS: this function is a relic. it will disappear soon.
 

zdb_setvendor()
SIGNATURE: int zdb_setvendor (const z_DBVendor dbvendor)
SYNOPSIS:
This function allows the application to tell the Z Directory what "type" (as in manufacturer, or vendor) of database is to be opened. This should generally always be invoked prior to calling z_open_database() or any subsequent database operations, in order to tweak any SQL statements for the underlying database type.
This call invokes "zdb_setproperty()" with the correct parameters applicable to the underlying RDBMS. If an incorrect value of 'dbvendor' is supplied, or if this function is not invoked, the Z Directory datbase functions may generate incorrect SQL statements. For example, in the case of INSERT statements, SQL Server column names can (and in some cases must) be enclosed with square brackets, eg, "INSERT INTO mytable ([name], [age]) VALUES ('John Doe', 55);", whereas mySQL does not allow the square brackets syntax.
 

zdb_setproperty()
SIGNATURE: int zdb_setproperty (const paramstring_o &ps, int *pi = NULL)
SYNOPSIS: configure [1 or more] properties of the database
PARAMETERS

• ps: input variable containing a parameter string name-value pair. This object can be set as follows:
  

  paramstring_o ps; ps = "use_brackets=ON"; // OR: "use_brackets=OFF"

  OR:
  

  paramstring_o ps; ps.set ("use_brackets", "ON");

• pi: output error indicator variable. values:
  0: successful set
  1: unknown parameter name encountered
  2: parameter value is neither YES nor NO (ON | OFF).

DESCRIPTION:
This function was originally created to control the inclusion of square brackets in SQL statements (see the discussion in function zdb_setvendor()). Currently, there are 2 parameter string values implemented: "use_brackets", and "strict_types". Both take values of either "ON" or "YES", and "OFF" or "NO".
 

zdb_set_axmeth()
SIGNATURE: int zdb_set_axmeth (zstyle_DBAccess newmeth)
SYNOPSIS:
sets the access method to the [singleton] database. This can be ADO (newmeth = zstyle_DBax_ODBC) or ODBC (newmeth = zstyle_DBax_ADO). This function should be invoked prior to using any other function in this area. This function is generally used only once during the lifetime of the program. If the access method is changed, or if this function is invoked a second time, any open databases are first closed.
 

zdb_set_dbredirect()
SIGNATURE: int zdb_set_dbredirect (const string_o &db_name, int *pi)
SYNOPSIS:
This function is rather esoeric. It is a subroutine version of dbconnection_o::set_true_dbname(), and allows for executing an explicit "USE [daabase name]" call when doing z_open_database(). This is sometimes required for ODBC access, depending on the vendor's implementation.
This subroutine simply sets a global string to the value given by 'db_name', which is used when the applicaion opens a database.
PARAMETERS

db_name: input string containing a database name. If he sring is empty (""), the call will result in disablling the use of this function.

 

zdb_set_default_user()
SIGNATURE: int zdb_set_default_user (const string_o &my_acct, const string_o my_pass)
SYNOPSIS:
allows the application to set a database user [account] and its password. This function will be phased out and should not be used!
TRAITS: this function is a relic. it will disappear soon.
 

z_open_database()
SIGNATURE: int z_open_database (const string_o &srv, const string_o &db, const string_o &usr, const string_o &pwd, int *pi = NULL)
SYNOPSIS:
opens a database. The database must be accessible to the program in order for this function call to succeed. zdb_set_axmeth() should be called prior to invoking this function.
 

zgeo_open_database()
SIGNATURE: int zgeo_open_database (const string_o &srv, const string_o &db, const string_o &usr, const string_o &pwd, int *pi = NULL)
SYNOPSIS: opens the geo database. The database must be accessible to the program in order for this function call to succeed.
DESCRIPTION:
this subroutine is analogous to z_open_database() and behaves like it exactly. It is intended to open the database for the "geo" tables. A separate dbconnection object is invoked for this dataset. This routine should be used even if the geo data is in the same database as the master database (which is whatever database that is opened by z_open_database()).
 

z_close_database()
SIGNATURE: int z_close_database (const string_o &name = "", const string_o &u2 = "", int *pi = NULL)
SYNOPSIS:
closes any currently open database. This function is typcally invoked at or near the end of the lifecycle of a program. The second parameter ('u2') is [currently, as of 2013] unused and reserved for future changes.
PARAMETERS

• name: the name of the database to close. if set to an empty string (""), the master database will be closed. If the database name specified by this parameter is set and cannot be found, this subroutine does nothing and returns -1.
• u2: unused parameter.
• pi: output error indicator variable. values:
  0: successful closure
  zErr_NotFound: specified database not found

 

zdb_table_exists()
SIGNATURE: boolean zdb_table_exists (const string_o &stab, int *pi)
SYNOPSIS: tells if there is a table in the currently open database that has the name "stab".
 

zdb_drop_table()
SIGNATURE: int zdb_drop_table (const string_o &tab, int *pi)
SYNOPSIS: deletes a table from the database. The underlying database must have been opened prior (via z_open_database()).
PARAMETERS

• tab: name of the table to examine
• pi: output error indicator variable. values:
  0: successful
  zErr_Item_NotFound: no such table
  zErr_UnsupportedProto: not ADO or ODBC
  2: other (SQL) error

RETURNS:
0: table successfully removed
-1: error ocurred during table removal attempt; see return value in 'pi'.
TRAITS: a dangerous subroutine. use with caution.
 

zdb_clear_table()
SIGNATURE: int zdb_clear_table (const string_o &tab, int *pi)
SYNOPSIS: empties out (clears) a table's contents. All rows are deleted. There is no warning shot.
PARAMETERS

tab: name of the table to examine

TRAITS: this is a dangerous subroutine. use with caution.
 

zdb_numrows_intable()
SIGNATURE: count_t zdb_numrows_intable (const string_o &stab, int *pi)
SYNOPSIS: returns the number of rows in the database table specified by 'stab'.
PARAMETERS

• stab: name of the table to examine
• pi: output error indicator variable. values:
  0: maximum value fetched successfully
  1: error occured

 

zdb_tablename_to_ID()
SIGNATURE: count_t zdb_tablename_to_ID (const string_o &table, int *pi)
SYNOPSIS: this is a relative of the function zdb_getID_byname().
TRAITS: this subroutine works only for ADO (BUG-TODO).
 

zdb_getID_byname()
SIGNATURE: int zdb_getID_byname (const string_o &tab, const string_o &name, int *pi)
SYNOPSIS:
Given a table with 'name' and 'id' columns, this function returns the row ID corresponding to the name. This routine is geared towards "orthodox" Z Directory objects.
TRAITS: this subroutine works only for ADO (BUG-TODO).
 

zdb_exec_sql()
SIGNATURE: int zdb_exec_sql (const string_o &sql_str, int *pi)
SYNOPSIS:
executes an SQL statement (found in input parameter 'sql_str'). It is up to the application to make sure the syntax and all parameters are valid. The underlying database must have been opened prior (via z_open_database()).
PARAMETERS

• sql_str: SQL statement to run. This string should not be terminated with a semi-colon (';').
• pi: output error indicator variable. values:
  0: maximum value fetched successfully
  1: error occured

 

zdb_column_maxvalue()
SIGNATURE: count_t zdb_column_maxvalue (const string_o &tab, const string_o &col, int *pi)
SYNOPSIS:
returns the maximum value of column 'col' in table 'tab'. 'tab' and 'col' must exist and be accessible, and 'col' must be a numeric type in order for this function call to succeed.
PARAMETERS

• tab: name of the table to examine
• col: name of the column in table 'tab' to access
• pi: output error indicator variable. values:
  0: maximum value fetched successfully
  1: error occured

 

zdb_numrows_match_criteria()
SIGNATURE: count_t zdb_numrows_match_criteria (const string_o &table, const string_o &sw, int *pi)
SYNOPSIS:
calculates the number of rows in the specified table that meet the criteria (given by the "where clause" of 'sw') . The contents of 'sw' must [carefully] apply to the specified table.
PARAMETERS

• table: name of the table to run the query on.
• sw: "where clause"
• pi: output error indicator variable. values:
  0: query run successfully
  1: error in execution
  zErr_UnsupportedProto: not available for ODBC

WARNING: WARNING: this subroutine is available only for ADO access method! (BUG-TODO)
TRAITS: this is a peculiar function, written for some long-forgotten specific purpose.
 

zdb_getrelated()
SIGNATURE: string_o zdb_getrelated (const string_o &table, const string_o &item, const string_o &keycol, const string_o &keyval, int *pi)
SYNOPSIS:
this is a curious little function that fills in the components of an SQL SELECT statement with the input variables:
SELECT [item] FROM {table} WHERE [keycol] = 'keyval';
The input variables matching the keynames in the above strings form the SELECT statement. The syntax is exactly that given above. This function exists primarily as an assistant to other Z Directory components.
 

note.
One unusual aspect about this group is that these functions sit "on top of" the database connection object class, dbconnection_o.