Table of Contents
Tibero provides C and C++ functions related to utilities. Application program developers can use utility API to call utilities, which were previously called in the command prompt, by application programs.
Utility API uses the following header files.
Header File Name | Description |
---|---|
tbutil.h | Declares utility API and defines related structures. The structures to be defined are as follow:
|
sqlca.h | Declares the structure which transfers the error information that occurs within a utility to the application program. The structure should be declared as follows:
|
sqlcli.h | ODBC standard header file. Defines standard API and macros. |
Utility API can define the following structures.
sqlstr structure
Field | Usage | Type | Description |
---|---|---|---|
length | Input | SQLSMALLINT | Specifies the length of a string. |
data | Input | SQLCHAR * | Specifies the pointer which stores a string |
TBExpImpMeta structure
Field | Usage | Type | Description |
---|---|---|---|
fieldTerm | Input | SQLCHAR * | As a separator of columns (fields), specifies the string to be used. |
fieldTermLen | Input | SQLSMALLINT | As a separator of columns (fields), specifies the length of a string to be used. |
lineTerm | Input | SQLCHAR * | As a separator of records, specifies the string to be used. |
lineTermLen | Input | SQLSMALLINT | As a separator of records, specifies the length of a string to be used. |
enclStart | Input | SQLCHAR * | Specifies the start string to wrap the data of a column. If specifying the start string with double quotes ("), double quotes are attached at the front of data of the column. |
enclStartLen | Input | SQLSMALLINT | Specifies the length of the start string to wrap the data of column. |
enclLen | Input | SQLCHAR * | Specifies the end string to wrap the data of the column. If specifying the end string with double quotes ("), double quotes are attached at the end of the data of the column. |
enclEndLen | Input | SQLSMALLINT | Specifies the length of the end string to wrap the data of a column. |
escape | Input | SQLCHAR * | When interpreting the data of column, it specifies the required ESCAPE strings. But, this is used only in TBImport structure. |
escapeLen | Input | SQLSMALLINT | When interpreting the data in a column, it specifies the length of the required ESCAPE string. But, this is used only in TBImport structure. |
TBExportIn structure
Field | Usage | Type | Description |
---|---|---|---|
iMeta | Input | TBExpImpMeta * | Pointer of TBExpImpMeta structure. Specifies the input meta data required to extract data. |
TBExportOut structure
Field | Usage | Type | Description |
---|---|---|---|
oRowsExported | Output | SQLINTEGER | Writes the number of records extracted from a data file. |
TBExportStruct structure
Field | Usage | Type | Description |
---|---|---|---|
piDataFileName | Input | SQLCHAR * | Specifies the path name of a file to store extracted data. |
iDataFileNameLen | Input | SQLSMALLINT | Specifies the length of a data file name. When a file name ends with NULL, specifies SQL_NTS. |
piActionString | Input | sqlstr * | Specifies the table view or the data of the view by using a SELECT statement. The extracted data is stored in the column order specified in a SELECT statement. |
iFileType | Input | SQLSMALLINT | Currently only SQL_DEL, which uses column separator, can be used. |
piMsgFileName | Input | SQLCHAR * | Specifies the name of a message file which stores errors and warnings that occur while extracting data and other useful information. |
iMsgFileNameLen | Input | SQLSMALLINT | Specifies the length of a message file name. When the file ends with NULL, specifies SQL_NTS. |
piExportInfoIn | Input | TBExportIn * | Pointer of the TBExportIn structure. Specifies the input meta data required to extract data. |
piExportInfoOut | Input | TBExportOut * | Pointer of the TBExportOut structure. Stores the result information, after extracting data. |
TBImportIn structure
Field | Usage | Type | Description |
---|---|---|---|
iMeta | Input | TBExpImpMeta * | Pointer of theTBExpImpMeta structure. Specifies the input meta data required to load data. |
iRowCount | Input | SQLINTEGER | Specifies the number of records to be loaded. When a user specifies the value as 0, all the records in a data file are loaded. |
iSkipCount | Input | SQLINTEGER | Specifies the number of records to skip in order to delete the load targets from a data file. |
iCommitCount | Input | SQLINTEGER | Specifies the amount of data to commit during data loading. However, due to performance issues the exact amount of data, as specified by the user, may not be successfully committed. |
iErrorCount | Input | SQLINTEGER | The number of error records allowed by users. If the number of error records created, while loading data, is larger than the allowed number, loading is suspended. |
TBImportOut structure
Field | Usage | Type | Description |
---|---|---|---|
oRowsRead | Output | SQLINTEGER | Writes the number of records read from a data file. |
oRowsSkipped | Output | SQLINTEGER | Writes the number of records skipped from a data file. |
oRowsInserted | Output | SQLINTEGER | Writes the number of records input (inserted) in the table or the view. |
oRowsUpdated | Output | SQLINTEGER | Writes the number of records updated or edited in the table or the view. |
oRowsRejected | Output | SQLINTEGER | Writes the number of records that failed to load. |
oRowsCommitted | Output | SQLINTEGER | Writes the number of records that were successfully committed. |
TBImportStruct structure
Field | Usage | Type | Description |
---|---|---|---|
piDataFileName | Input | SQLCHAR * | Specifies the path name of a data file to load. |
iDataFileNameLen | Input | SQLSMALLINT | Specifies the length of a data file name. When a file name ends with NULL, specifies SQL_NTS. |
piActionString | Input | sqlstr * | Specifies the column and the table to load. Specifies detailed meta data. The same syntax of string used by a control file of the tbLoader utility. Refer to the control file type of the tbLoader utility for more information about this syntax. |
iFileType | Input | SQLSMALLINT | Currently, only SQL_DEL, which uses a column separator, can be used. |
piMsgFileName | Input | SQLCHAR * | Specifies the message file name, that stores errors and warnings and useful information on data loading. |
iMsgFileNameLen | Input | SQLSMALLINT | Specifies the length of a message file name. When file name ends with NULL, specifies SQL_NTS. |
piBadFileName | Input | SQLCHAR * | Error data file, which collects and writes data about errors that occurred while loading data. |
iBadFileNameLen | Input | SQLSMALLINT | Specifies the length of an error data file name. When file name ends with NULL, specifies SQL_NTS. |
iDPL | Input | BOOL | When loading data, specifies whether to use Direct Path Load type. |
piImportInfoIn | Input | TBImportIn * | Pointer of the TBImportIn structure. Specifies input meta data required to load data. |
piImportInfoOut | Input | TBImportOut * | Pointer of the TBImportOut structure. Loads the result information, after extracting loading data. |
piImportInfoOut | Input | TBImportOut * | Pointer of the TBImportOut structure. Load the result information, after loading data. |
The following is a list of utility API.
Function Type | Function | Header File |
---|---|---|
Database Connection | TBConnect | tbutil.h |
Database Disconnection | TBDisconnect | tbutil.h |
Data Extraction | TBExport | tbutil.h |
Data Loading | TBImport | tbutil.h |
A function that connects to a Tibero server by using database connection information (SID, user name, and password).
A detailed description of the TBConnect
function follows:
Syntax
SQLRETURN SQL_API
TBConnect(SQLCHAR *dnsname, SQLCHAR *username, SQLCHAR *pwd, struct sqlca *pSqlca);
Parameter
Parameter | Usage | Description |
---|---|---|
dnsname | Input | SID defined in the tbdsn.tbr(or tbnet_alias.tbr) file. |
username | Input | Specifies a user name. |
pwd | Input | Specifies a password. |
pSqlca | Output | When a return code is not SQL_SUCCESS, it includes information on errors that occurred, within the utility. |
Return code
Return Code | Description |
---|---|
SQL_SUCCESS | Function completed successfully. |
SQL_SUCCESS_WITH_INFO | Function completed successfully, but there is a warning message. |
SQL_ERROR | Critical error has occurred. |
Related function
A function that disconnects a Tibero server that matches the database connection information (SID).
A detailed description of the TBDisconnect
function
follows:
Syntax
SQLRETURN SQL_API
TBDisconnect(SQLCHAR *dnsname, struct sqlca *pSqlca);
Parameter
Parameter | Usage | Description |
---|---|---|
dnsname | Input | SID defined in tbdsn.tbr(or tbnet_alias.tbr) file. |
pSqlca | Output | When return code is not SQL_SUCCESS, includes information on errors that occurred within the utility. |
Return code
Return Code | Description |
---|---|
SQL_SUCCESS | Function completed successfully . |
SQL_SUCCESS_WITH_INFO | Function completed successfully, but there is a warning message. |
SQL_ERROR | Critical error has occurred. |
Related function
A function which extracts data from a database to an external file. The External file displays the extracted data as text type. Columns and records are extracted by using column separator and record separator. The extracted external file becomes data file type of the tbLoader utility. Users can select data to be extracted by using the SELECT statement. But, to use this function, the SELECT permission over the target table or view is required.
A detailed description of the TBExport
follows:
Syntax
SQLRETURN SQL_API TBExport(SQLINTEGER versionNumber, TBExportStruct *pParamStruct, struct sqlca *pSqlca);
Parameter
Parameter | Usage | Description |
---|---|---|
versionNumber | Input | The version number of the utility library. This parameter exists for compatibility with lower versions if the utility library is updated. Current version number is 1. |
pParamStruct | Input Output | This parameter specifies the target information to be extracted and receives the result information after extraction. Refer to "TBExportStruct structure" for more information. |
pSqlca | Output | When return code is not SQL_SUCCESS, includes information on errors that occurred within the utility. |
Return code
Return Code | Description |
---|---|
SQL_SUCCESS | Function completed successfully. |
SQL_SUCCESS_WITH_INFO | Function completed successfully, but there is a warning message. |
SQL_ERROR | Critical error has occurred. |
예제
#include "tbutil.h" #define DNS_NAME "DEFAULT" #define USER_NAME "SYS" #define PWD "tibero" int main(int argc, char *argv[]) { SQLRETURN rc = SQL_SUCCESS; SQLINTEGER versionNumber = 1; SQLCHAR dataFileName[256]; SQLCHAR actionString[256]; SQLCHAR msgFileName[256]; SQLCHAR fieldTerm[5]; SQLCHAR lineTerm[5]; SQLCHAR enclStart[5]; SQLCHAR enclEnd[5]; struct sqlca ca = {"\0", 0, 0, {0, "\0"}, "\0", {0, 0, 0, 0, 0, 0}, "\0", "\0"}; TBExportStruct exportStruct = {NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL}; TBExportIn exportIn = {{NULL, 0, NULL, 0, NULL, 0, NULL, 0, NULL, 0}}; TBExportOut exportOut = {0}; rc= TBConnect((SQLCHAR *)DNS_NAME, (SQLCHAR *)USER_NAME, (SQLCHAR *)PWD, &ca); if (rc != SQL_SUCCESS) return -1; strcpy((char *)dataFileName, "./all_tables.dat"); strcpy((char *)actionString, "select * from all_tables"); strcpy((char *)msgFileName, "./all_tables.log"); strcpy((char *)fieldTerm, ","); strcpy((char *)lineTerm, "\n"); strcpy((char *)enclStart, "\""); strcpy((char *)enclEnd, "\""); /* setting data file name */ exportStruct.piDataFileName = dataFileName; exportStruct.iDataFileNameLen = strlen((char *)dataFileName); /* setting action String */ exportStruct.piActionString = calloc(1, sizeof(sqlstr)); exportStruct.piActionString->data = actionString; exportStruct.piActionString->length = strlen((char *)actionString); /* setting file type */ exportStruct.iFileType = SQL_DEL; /* setting message file name */ exportStruct.piMsgFileName = msgFileName; exportStruct.iMsgFileNameLen = strlen((char *)msgFileName); /* setting field term, line term etc.. */ exportIn.iMeta.fieldTerm = fieldTerm; exportIn.iMeta.fieldTermLen = strlen((char *)fieldTerm); exportIn.iMeta.lineTerm = lineTerm; exportIn.iMeta.lineTermLen = strlen((char *)lineTerm); exportIn.iMeta.enclStart = enclStart; exportIn.iMeta.enclStartLen = strlen((char *)enclStart); exportIn.iMeta.enclEnd = enclEnd; exportIn.iMeta.enclEndLen = strlen((char *)enclEnd); /* setting export input, output information */ exportStruct.piExportInfoIn = &exportIn; exportStruct.poExportInfoOut = &exportOut; /* setting file type */ rc = TBExport(versionNumber, &exportStruct, &ca); if (rc != SQL_SUCCESS) return -1; /* disconnect */ rc= TBDisconnect((SQLCHAR *)DNS_NAME, &ca); if (rc != SQL_SUCCESS) return -1; return 1; }
A function that loads data located in external file to a database. Fixed record type and separated record type external files are supported by the tbLoader utility. The INSERT permission for the table or view is required, to use this utility.
A detailed description of the TBImport
function follows:
Syntax
SQLRETURN SQL_API TBImport(SQLINTEGER versionNumber, TBImportStruct *pParamStruct, struct sqlca *pSqlca);
Parameter
Parameter | Usage | Description |
---|---|---|
versionNumber | Input | The version number of the utility library. This parameter exists for compatibility with lower versions if the utility library is updated. Current version number is 1. |
pParamStruct | Input Output | This parameter specifies the target information to be loaded and receives the result information after loading. Refer to TBImportStruct for more information. |
pSqlca | Output | When return code is not SQL_SUCCESS, includes the error information occurred within the utility. |
Return code
Return code | Description |
---|---|
SQL_SUCCESS | Function completed successfully. |
SQL_SUCCESS_WITH_INFO | Function completed successfully, but there is a warning message. |
SQL_ERROR | Critical error has occurred. |
Example
#include "tbutil.h" #define DNS_NAME "DEFAULT" #define USER_NAME "SYS" #define PWD "tibero" int main(int argc, char *argv[]) { SQLRETURN rc = SQL_SUCCESS; SQLINTEGER versionNumber = 1; SQLCHAR dataFileName[256]; SQLCHAR actionString[256]; SQLCHAR msgFileName[256]; SQLCHAR badFileName[256]; SQLCHAR fieldTerm[5]; SQLCHAR lineTerm[5]; SQLCHAR enclStart[5]; SQLCHAR enclEnd[5]; SQLCHAR escape[5]; struct sqlca ca = {"\0", 0, 0, {0, "\0"}, "\0", {0, 0, 0, 0, 0, 0}, "\0", "\0"}; TBImportStruct importStruct = {NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, 0, 0, NULL, NULL}; TBImportIn importIn = {{NULL, 0, NULL, 0, NULL, 0, NULL, 0, NULL, 0}, 0, 0, 0, 0}; TBImportOut importOut = {0, 0, 0, 0, 0, 0}; rc= TBConnect((SQLCHAR *)DNS_NAME, (SQLCHAR *)USER_NAME, (SQLCHAR *)PWD, &ca); if (rc != SQL_SUCCESS) return -1; strcpy((char *)actionString, "LOAD DATA " "APPEND " "INTO TABLE DEPT " "MULTI INSERT INDEXES " "(position, deptno, dname CONSTANT \"co dep\", loc)"); strcpy((char *)dataFileName, "./test.dat"); strcpy((char *)msgFileName, "./test.log"); strcpy((char *)badFileName, "./test.bad"); strcpy((char *)fieldTerm, ",b"); strcpy((char *)lineTerm, "abbb\n"); strcpy((char *)enclStart, "{$"); strcpy((char *)enclEnd, "$}"); strcpy((char *)escape, "XX"); /* setting data file name */ importStruct.piDataFileName = dataFileName; importStruct.iDataFileNameLen = strlen((char *)dataFileName); /* setting action String */ importStruct.piActionString = calloc(1, sizeof(sqlstr)); importStruct.piActionString->data = actionString; importStruct.piActionString->length = strlen((char *)actionString); /* setting file type */ importStruct.iFileType = SQL_DEL; /* setting message file name */ importStruct.piMsgFileName = msgFileName; importStruct.iMsgFileNameLen = strlen((char *)msgFileName); /* setting bad data file name */ importStruct.piBadFileName = badFileName; importStruct.iBadFileNameLen = strlen((char *)badFileName); /* turn on DPL mode */ importStruct.iDPL = 2; /* setting field term, line term etc.. */ importIn.iMeta.fieldTerm = fieldTerm; importIn.iMeta.fieldTermLen = strlen((char *)fieldTerm); importIn.iMeta.lineTerm = lineTerm; importIn.iMeta.lineTermLen = strlen((char *)lineTerm); importIn.iMeta.enclStart = enclStart; importIn.iMeta.enclStartLen = strlen((char *)enclStart); importIn.iMeta.enclEnd = enclEnd; importIn.iMeta.enclEndLen = strlen((char *)enclEnd); importIn.iMeta.escape = escape; importIn.iMeta.escapeLen = strlen((char *)escape); importIn.iRowcount = 0; importIn.iSkipcount = 0; importIn.iCommitcount = 2; importIn.iErrorcount = 50; /* setting export input, output information */ importStruct.piImportInfoIn = &importIn; importStruct.poImportInfoOut = &importOut; /* setting file type */ rc = TBImport(versionNumber, &importStruct, &ca); if (rc != SQL_SUCCESS) return -1; fprintf(stdout, "oRowsRead[%ld]", importOut.oRowsRead); fprintf(stdout, "oRowsSkipped[%ld]", importOut.oRowsSkipped); fprintf(stdout, "oRowsInserted[%ld]", importOut.oRowsInserted); fprintf(stdout, "oRowsUpdated[%ld]", importOut.oRowsUpdated); fprintf(stdout, "oRowsRejected[%ld]", importOut.oRowsRejected); fprintf(stdout, "oRowsCommitted[%ld]", importOut.oRowsCommitted); /* disconnect */ rc= TBDisconnect((SQLCHAR *)DNS_NAME, &ca); if (rc != SQL_SUCCESS) return -1; return 1; }