Chapter 7. Utility APIs

Table of Contents

7.3.1. TBConnect
7.3.2. TBDisconnect
7.3.3. TBExport
7.3.4. TBImport

Tibero provides utility-related C/C++ functions. Application developers can use the utility API to call utilities, which were previously executed at the command prompt, within application programs.

7.1. Header File

Utility API uses the following header files.

Header File Name	Description
tbutil.h	Declares utility API and defines related structures. Defines the following structures: struct sqlstr struct TBExpImpMeta struct TBExportIn struct TBExportOut struct TBExportStruct struct TBImportIn struct TBImportOut struct TBImportStruct
sqlca.h	Declares the structure for utility errors to send to the application program. Declares the following structure: struct sqlca
sqlcli.h	ODBC standard header file. Defines standard API and macros.

Header File Name

Description

tbutil.h

Declares utility API and defines related structures.

Defines the following structures:

struct sqlstr
struct TBExpImpMeta
struct TBExportIn
struct TBExportOut
struct TBExportStruct
struct TBImportIn
struct TBImportOut
struct TBImportStruct

sqlca.h

Declares the structure for utility errors to send to the application program.

Declares the following structure:

struct sqlca

sqlcli.h

ODBC standard header file. Defines standard API and macros.

7.2. Structure

Utility API can define the following structures:

sqlstr structure

Field Usage Type Description
length Input SQLSMALLINT
String length.
data Input SQLCHAR *
String pointer.

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 quote ("), it 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 quote ("), it is appended to the end of the column data.
enclEndLen	Input	SQLSMALLINT	End string length.
escape	Input	SQLCHAR *	ESCAPE string for column data. This is only used in TBImport structure.
escapeLen	Input	SQLSMALLINT	ESCAPE string length. This is used only in TBImport structure.

TBExportIn structure

Field Usage Type Description
iMeta Input TBExpImpMeta *
Pointer for TBExpImpMeta structure.
Points to the input meta data required to extract data.
TBExportOut structure

Field Usage Type Description
oRowsExported Output SQLINTEGER Number of records extracted from a data file.

Field	Usage	Type	Description
iMeta	Input	TBExpImpMeta *	Pointer for TBExpImpMeta structure. Points to the input meta data required to extract data.

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 name of the file to store extracted data.
iDataFileNameLen	Input	SQLSMALLINT	Data file name length. When a file name ends with NULL, it is SQL_NTS.
piActionString	Input	sqlstr *	SELECT statement to extract data from a table or view. The extracted data is saved in the column order of the SELECT statement.
iFileType	Input	SQLSMALLINT	Currently only SQL_DEL, which uses column separator, can be used.
piMsgFileName	Input	SQLCHAR *	Name of the message file for storing errors and warnings that occur while extracting data and other useful information.
iMsgFileNameLen	Input	SQLSMALLINT	Length of the message file name. When the file ends with NULL, it is SQL_NTS.
piExportInfoIn	Input	TBExportIn *	Pointer for TBExportIn structure. Points to the input meta data required to extract data.
piExportInfoOut	Input	TBExportOut *	Pointer for TBExportOut structure. Stores the output that occurred after extracting data.

TBImportIn structure

Field	Usage	Type	Description
iMeta	Input	TBExpImpMeta *	Pointer for TBExpImpMeta structure. Points to the input meta data 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	Amount of data to commit during data loading. Due to performance issues, the actual number of committed records may not be same as this value.
iErrorCount	Input	SQLINTEGER	Allowed maximum number of error records. If the number of error records created exceeds this limit while loading data, loading is suspended.

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 the table or view.
oRowsUpdated	Output	SQLINTEGER	Number of records updated in the table or view.
oRowsRejected	Output	SQLINTEGER	Number of records that failed to load.
oRowsCommitted	Output	SQLINTEGER	Number of records that were successfully committed.

TBImportStruct structure

Field	Usage	Type	Description
piDataFileName	Input	SQLCHAR *	Path of the data file to load.
iDataFileNameLen	Input	SQLSMALLINT	Data file name length. When the file name ends with NULL, it is SQL_NTS.
piActionString	Input	sqlstr *	Column and table to load and detailed meta data. Uses the same string format as the tbLoader's control file. Refer to the control file format for tbLoader for more information.
iFileType	Input	SQLSMALLINT	Currently, only SQL_DEL, which uses a column separator, can be used.
piMsgFileName	Input	SQLCHAR *	Name of the message file for storing errors and warnings that occur while extracting data and other useful information.
iMsgFileNameLen	Input	SQLSMALLINT	Length of the message file name. When the file name ends with NULL, it is SQL_NTS.
piBadFileName	Input	SQLCHAR *	Error file that contains errors that occurred while loading data.
iBadFileNameLen	Input	SQLSMALLINT	Error file name length. When the file name ends with NULL, it is SQL_NTS.
iDPL	Input	BOOL	Option to use direct path load when loading data.
iTrailNullCols	Input	BOOL	Option to bind the last column value that is empty to NULL.
piImportInfoIn	Input	TBImportIn *	Pointer for TBImportIn structure. Points to the input meta data required to load data.
piImportInfoOut	Input	TBImportOut *	Pointer for TBImportOut structure. Imports the output that occurred after loading data.

7.3. Utility API

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

7.3.1. TBConnect

Connects to Tibero server with 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 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 code

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

Return Code	Description
SQL_SUCCESS	Completed successfully.
SQL_SUCCESS_WITH_INFO	Completed successfully with warning message(s).
SQL_ERROR	Critical error has occurred.

7.3.2. TBDisconnect

Disconnects a Tibero server with 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 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 code

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

TBConnect

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 Code	Description
SQL_SUCCESS	Completed successfully.
SQL_SUCCESS_WITH_INFO	Completed successfully with warning message(s).
SQL_ERROR	Critical error has occurred.

7.3.3. TBExport

Extracts data from the database to an external file. The file displays the extracted data as text. Columns and records are extracted by using column and record separators. The file is extracted as the data file type of tbLoader. The data can be specified with a SELECT statement, and this requires SELECT permissions on the target tables or views.

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	Version number of the utility library needed for backward-compatibility. Current version number is 1.
pParamStruct	Input Output	Specifies the data to extract as input and receives the result as output. Refer to "TBExportStruct structure" for more information.
pSqlca	Output	When the return code is not SQL_SUCCESS, contains utility error logs.

Usage

Description

versionNumber

Input

Version number of the utility library needed for backward-compatibility.

Current version number is 1.

pParamStruct

Input Output

Specifies the data to extract as input and receives the result as output.

Refer to "TBExportStruct structure" for more information.

pSqlca

Output

When the return code is not SQL_SUCCESS, contains utility error logs.

Return code

Return Code Description
SQL_SUCCESS Completed successfully.
SQL_SUCCESS_WITH_INFO Completed successfully with warning message(s).
SQL_ERROR Critical error has occurred.

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;
}

7.3.4. 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.

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	Version number of the utility library needed for backward-compatibility. Current version number is 1.
pParamStruct	Input Output	Specifies the data to load as input and receives the result as output. Refer to TBImportStruct for more information.
pSqlca	Output	When the return code is not SQL_SUCCESS, contains utility error logs.

Usage

Description

versionNumber

Input

Version number of the utility library needed for backward-compatibility.

Current version number is 1.

pParamStruct

Input Output

Specifies the data to load as input and receives the result as output.

Refer to TBImportStruct for more information.

pSqlca

Output

When the return code is not SQL_SUCCESS, contains utility error logs.

Return code

Return Code Description
SQL_SUCCESS Completed successfully.
SQL_SUCCESS_WITH_INFO Completed successfully with warning message(s).
SQL_ERROR Critical error has occurred.

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;
}

Prev		Next
Chapter 6. tbdv	Home	Index