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	tbutil.h	Connects to a database.
TBDisconnect	tbutil.h	Disconnects from a database.
TBExport	tbutil.h	Extracts data.
TBImport	tbutil.h	Loads data.

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

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

TBConnect

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;

  }