Utility API
This chapter describes functions used to call Tibero utilities from applications.
Tibero provides C and C++ functions related to its utilities. Application developers can call the utilities from applications by using the utility API
Header Files
The utility API uses the following header files.
Header File
Description
tbutil.h
Declares utility API and defines related structures.
The following structures are defined.
struct sqlstr
struct TBExpImpMeta
struct TBExportIn
struct TBExportOut
struct TBExportStruct
struct TBImportIn
struct TBImportOut
struct TBImportStruct
sqlca.h
Declares a structure used to send utility errors to applications.
The following structure is defined.
struct sqlca
sqlcli.h
ODBC standard header file. Defines standard API and macros.
Structures
Utility API can define the following structures.
sqlstr structure
Field
Usage
Type
Description
length
Input
SQLSMALLINT
String length
data
Input
SQLCHAR *
String pointer
TBExpImpMeta structure
Field
Usage
Type
Description
fieldTerm
Input
SQLCHAR *
Column (field) separator string.
fieldTermLen
Input
SQLSMALLINT
Column (field) separator string length.
lineTerm
Input
SQLCHAR *
Record separator string.
lineTermLen
Input
SQLSMALLINT
Record separator string length.
enclStart
Input
SQLCHAR *
Start string of a data column. If set to a double quotation mark ("), this is appended at the start of the column data.
enclStartLen
Input
SQLSMALLINT
Start string length.
enclLen
Input
SQLCHAR *
End string of a data column. If set to a double quotation mark ("), this is appended to the end of the column data.
enclEndLen
Input
SQLSMALLINT
End string length.
escape
Input
SQLCHAR *
ESCAPE string used to interpret column data. This is only used in TBImport structure.
escapeLen
Input
SQLSMALLINT
Length of the ESCAPE string used to interpret column data. This is used only in TBImport structure.
TBExportIn structure
Field
Usage
Type
Description
iMeta
Input
TBExpImpMeta *
Pointer to a TBExpImpMeta structure that has input metadata required to extract data.
TBExportOut structure
Field
Usage
Type
Description
oRowsExported
Output
SQLINTEGER
Number of records extracted from a data file.
TBExportStruct structure
Field
Usage
Type
Description
piDataFileName
Input
SQLCHAR *
Path to a file to store extracted data.
iDataFileNameLen
Input
SQLSMALLINT
Length of the data file name. If a file name ends with NULL, specify SQL_NTS.
piActionString
Input
sqlstr *
Data extracted from a table or view by using a SELECT statement. The data is saved in the column order of the SELECT statement.
iFileType
Input
SQLSMALLINT
Currently only SQL_DEL, which uses column separator, is available.
piMsgFileName
Input
SQLCHAR *
Name of the message file that contains errors and warnings that occur while extracting data and other useful information.
iMsgFileNameLen
Input
SQLSMALLINT
Length of the message file name. If a file name ends with NULL, specify SQL_NTS.
piExportInfoIn
Input
TBExportIn *
Pointer to a TBExportIn structure that has input metadata required to extract data.
piExportInfoOut
Input
TBExportOut *
Pointer to a TBExportOut structure that has result data occurred after extracting data.
TBImportIn structure
Field
Usage
Type
Description
iMeta
Input
TBExpImpMeta *
Pointer to a TBExpImpMeta structure that has input metadata required to load data.
iRowCount
Input
SQLINTEGER
Number of records to load. If set to 0, all records in the data file are loaded.
iSkipCount
Input
SQLINTEGER
Number of records to skip when loading data.
iCommitCount
Input
SQLINTEGER
Number of records to commit at a time when loading data. Because of a performance issue, the actual number of records committed may not be same as this value.
iErrorCount
Input
SQLINTEGER
Allowed maximum number of error records. If this number is exceeded while loading data, loading is stopped.
TBImportOut structure
Field
Usage
Type
Description
oRowsRead
Output
SQLINTEGER
Number of records read from a data file.
oRowsSkipped
Output
SQLINTEGER
Number of records skipped from a data file.
oRowsInserted
Output
SQLINTEGER
Number of records inserted into a table or view.
oRowsUpdated
Output
SQLINTEGER
Number of records updated in a table or view.
oRowsRejected
Output
SQLINTEGER
Number of records failed to be loaded.
oRowsCommitted
Output
SQLINTEGER
Number of records committed successfully.
TBImportStruct structure
Field
Usage
Type
Description
piDataFileName
Input
SQLCHAR *
Path to a data file to load.
iDataFileNameLen
Input
SQLSMALLINT
Length of the data file name. If a file name ends with NULL, specify SQL_NTS.
piActionString
Input
sqlstr *
Column and table to load and detailed meta data. Uses the same string format as in the tbLoader's control file. For more information, refer to the control file format for tbLoader.
iFileType
Input
SQLSMALLINT
Currently, only SQL_DEL, which uses a column separator, is available.
piMsgFileName
Input
SQLCHAR *
Name of the message file that contains errors and warnings that occur while loading data and other useful information.
iMsgFileNameLen
Input
SQLSMALLINT
Length of the message file name. If a file name ends with NULL, specify SQL_NTS.
piBadFileName
Input
SQLCHAR *
Name of the error file that contains errors occurred while loading data.
iBadFileNameLen
Input
SQLSMALLINT
Length of the error file name. If a file name ends with NULL, specify SQL_NTS.
iDPL
Input
BOOL
Option to use direct path load when loading data.
iTrailNullCols
Input
BOOL
Option to bind the last column data that does not
exist in a data file to NULL.
piImportInfoIn
Input
TBImportIn *
Pointer to a TBImportIn structure that has input metadata required to load data.
piImportInfoOut
Input
TBImportOut *
Pointer to a TBImportOut structure that has result data occurred after loading data.
Utility API
Function
Header File
Description
TBConnect
Connects to Tibero server by using database connection information (SID, user name, and password).
Syntax
SQLRETURN SQL_API
TBConnect(SQLCHAR *dnsname, SQLCHAR *username, SQLCHAR *pwd,
struct sqlca *pSqlca);Parameters
Parameter
Usage
Description
dnsname
Input
SID set in the tbdsn.tbr (or tbnet_alias.tbr) file.
username
Input
User name.
pwd
Input
Password.
pSqlca
Output
When the return code is not SQL_SUCCESS, contains utility error logs.
Return codes
Return Code
Description
SQL_SUCCESS
Completed successfully.
SQL_SUCCESS_WITH_INFO
Completed successfully with warning message(s).
SQL_ERROR
Critical error has occurred.
Related function
TBDisconnect
Disconnects from Tibero server that matches the database connection information (SID).
Syntax
SQLRETURN SQL_API
TBDisconnect(SQLCHAR *dnsname, struct sqlca *pSqlca);Parameters
Parameter
Usage
Description
dnsname
Input
SID set in the tbdsn.tbr (or tbnet_alias.tbr) file.
pSqlca
Output
When the return code is not SQL_SUCCESS, contains utility error logs.
Return codes
Return Code
Description
SQL_SUCCESS
Completed successfully.
SQL_SUCCESS_WITH_INFO
Completed successfully with warning message(s).
SQL_ERROR
Critical error has occurred.
Related function
TBExport
Extracts data from a database to an external file. The file contains the extracted data as text, and it is saved as a data file for tbLoader. Column data and records are extracted with column and record separators. Data to export can be specified with a SELECT statement, and this requires the SELECT permission on the target table or view.
Syntax
SQLRETURN SQL_API
TBExport(SQLINTEGER versionNumber, TBExportStruct *pParamStruct,
struct sqlca *pSqlca);Parameters
Parameter
Usage
Description
versionNumber
Input
Version number of the utility library. This number can be used for backward compatibility. Current version number is 1.
pParamStruct
Input/ Output
Specifies data to extract as input and receives the result as output. For more information, refer to "TBExportStruct structure."
pSqlca
Output
When the return code is not SQL_SUCCESS, contains utility error logs.
Return codes
Return Code
Description
SQL_SUCCESS
Completed successfully.
SQL_SUCCESS_WITH_INFO
Completed successfully with warning message(s).
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 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;
} TBImport
Loads data from an external file to the database. Fixed-length and delimited record type files are supported by tbLoader. The INSERT permissions for the table or view is required to use this utility.
Syntax
SQLRETURN SQL_API TBImport(SQLINTEGER versionNumber,
TBImportStruct *pParamStruct,
struct sqlca *pSqlca);Parameters
Parameter
Usage
Description
versionNumber
Input
Version number of the utility library. This number can be used for backward compatibility. Current version number is 1.
pParamStruct
Input/ Output
Specifies data to load as input and receives the result as output. For more information, refer to "TBImportStruct structure."
pSqlca
Output
When the return code is not SQL_SUCCESS, contains utility error logs.
Return codes
Return Code
Description
SQL_SUCCESS
Completed successfully.
SQL_SUCCESS_WITH_INFO
Completed successfully with warning message(s).
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;
}
Last updated