Chapter 6. Utility API

Table of Contents

6.1. Header File
6.2. Structure
6.3. Utility API List
6.3.1. TBConnect
6.3.2. TBDisconnect
6.3.3. TBExport
6.3.4. TBImport

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.

6.1. Header File

Utility API uses the following header files.

Header File NameDescription
tbutil.h

Declares utility API and defines related structures.

The structures to be defined are as follow:

  • struct sqlstr

  • struct TBExpImpMeta

  • struct TBExportIn

  • struct TBExportOut

  • struct TBExportStruct

  • struct TBImportIn

  • struct TBImportOut

  • struct TBImportStruct

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:

  • struct sqlca

sqlcli.hODBC standard header file. Defines standard API and macros.

6.2. Structure

Utility API can define the following structures.

  • sqlstr structure

    FieldUsageTypeDescription
    lengthInputSQLSMALLINT

    Specifies the length of a string.

    dataInputSQLCHAR *

    Specifies the pointer which stores a string

  • TBExpImpMeta structure

    FieldUsageTypeDescription
    fieldTermInputSQLCHAR *

    As a separator of columns (fields), specifies the string to be used.

    fieldTermLenInputSQLSMALLINT

    As a separator of columns (fields), specifies the length of a string to be used.

    lineTermInputSQLCHAR *

    As a separator of records, specifies the string to be used.

    lineTermLenInputSQLSMALLINT

    As a separator of records, specifies the length of a string to be used.

    enclStartInputSQLCHAR *

    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.

    enclStartLenInputSQLSMALLINT

    Specifies the length of the start string to wrap the data of column.

    enclLenInputSQLCHAR *

    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.

    enclEndLenInputSQLSMALLINT

    Specifies the length of the end string to wrap the data of a column.

    escapeInputSQLCHAR *

    When interpreting the data of column, it specifies the required ESCAPE strings.

    But, this is used only in TBImport structure.

    escapeLenInputSQLSMALLINT

    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

    FieldUsageTypeDescription
    iMetaInputTBExpImpMeta *

    Pointer of TBExpImpMeta structure.

    Specifies the input meta data required to extract data.

  • TBExportOut structure

    FieldUsageTypeDescription
    oRowsExportedOutputSQLINTEGERWrites the number of records extracted from a data file.
  • TBExportStruct structure

    FieldUsageTypeDescription
    piDataFileNameInputSQLCHAR *

    Specifies the path name of a file to store extracted data.

    iDataFileNameLenInputSQLSMALLINT

    Specifies the length of a data file name.

    When a file name ends with NULL, specifies SQL_NTS.

    piActionStringInputsqlstr *

    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.

    iFileTypeInputSQLSMALLINT

    Currently only SQL_DEL, which uses column separator, can be used.

    piMsgFileNameInputSQLCHAR *

    Specifies the name of a message file which stores errors and warnings that occur while extracting data and other useful information.

    iMsgFileNameLenInputSQLSMALLINT

    Specifies the length of a message file name.

    When the file ends with NULL, specifies SQL_NTS.

    piExportInfoInInputTBExportIn *

    Pointer of the TBExportIn structure.

    Specifies the input meta data required to extract data.

    piExportInfoOutInputTBExportOut *

    Pointer of the TBExportOut structure.

    Stores the result information, after extracting data.

  • TBImportIn structure

    FieldUsageTypeDescription
    iMetaInputTBExpImpMeta *

    Pointer of theTBExpImpMeta structure.

    Specifies the input meta data required to load data.

    iRowCountInputSQLINTEGER

    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.

    iSkipCountInputSQLINTEGER

    Specifies the number of records to skip in order to delete the load targets from a data file.

    iCommitCountInputSQLINTEGER

    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.

    iErrorCountInputSQLINTEGER

    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

    FieldUsageTypeDescription
    oRowsReadOutputSQLINTEGER

    Writes the number of records read from a data file.

    oRowsSkippedOutputSQLINTEGER

    Writes the number of records skipped from a data file.

    oRowsInsertedOutputSQLINTEGER

    Writes the number of records input (inserted) in the table or the view.

    oRowsUpdatedOutputSQLINTEGER

    Writes the number of records updated or edited in the table or the view.

    oRowsRejectedOutputSQLINTEGER

    Writes the number of records that failed to load.

    oRowsCommittedOutputSQLINTEGER

    Writes the number of records that were successfully committed.

  • TBImportStruct structure

    FieldUsageTypeDescription
    piDataFileNameInputSQLCHAR *

    Specifies the path name of a data file to load.

    iDataFileNameLenInputSQLSMALLINT

    Specifies the length of a data file name.

    When a file name ends with NULL, specifies SQL_NTS.

    piActionStringInputsqlstr *

    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.

    iFileTypeInputSQLSMALLINT

    Currently, only SQL_DEL, which uses a column separator, can be used.

    piMsgFileNameInputSQLCHAR *

    Specifies the message file name, that stores errors and warnings and useful information on data loading.

    iMsgFileNameLenInputSQLSMALLINT

    Specifies the length of a message file name.

    When file name ends with NULL, specifies SQL_NTS.

    piBadFileNameInputSQLCHAR *

    Error data file, which collects and writes data about errors that occurred while loading data.

    iBadFileNameLenInputSQLSMALLINT

    Specifies the length of an error data file name.

    When file name ends with NULL, specifies SQL_NTS.

    iDPLInputBOOL

    When loading data, specifies whether to use Direct Path Load type.

    piImportInfoInInputTBImportIn *

    Pointer of the TBImportIn structure.

    Specifies input meta data required to load data.

    piImportInfoOutInputTBImportOut *

    Pointer of the TBImportOut structure.

    Loads the result information, after extracting loading data.

    piImportInfoOutInputTBImportOut *

    Pointer of the TBImportOut structure.

    Load the result information, after loading data.

6.3. Utility API List

The following is a list of utility API.

Function TypeFunctionHeader File
Database ConnectionTBConnecttbutil.h
Database DisconnectionTBDisconnecttbutil.h
Data ExtractionTBExporttbutil.h
Data LoadingTBImporttbutil.h

6.3.1. TBConnect

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

    ParameterUsageDescription
    dnsnameInputSID defined in the tbdsn.tbr(or tbnet_alias.tbr) file.
    usernameInputSpecifies a user name.
    pwdInputSpecifies a password.
    pSqlcaOutputWhen a return code is not SQL_SUCCESS, it includes information on errors that occurred, within the utility.
  • Return code

    Return CodeDescription
    SQL_SUCCESSFunction completed successfully.
    SQL_SUCCESS_WITH_INFOFunction completed successfully, but there is a warning message.
    SQL_ERRORCritical error has occurred.
  • Related function

    TBDisconnect

6.3.2. TBDisconnect

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

    ParameterUsageDescription
    dnsnameInputSID defined in tbdsn.tbr(or tbnet_alias.tbr) file.
    pSqlcaOutputWhen return code is not SQL_SUCCESS, includes information on errors that occurred within the utility.
  • Return code

    Return CodeDescription
    SQL_SUCCESSFunction completed successfully .
    SQL_SUCCESS_WITH_INFOFunction completed successfully, but there is a warning message.
    SQL_ERRORCritical error has occurred.
  • Related function

    TBConnect

6.3.3. TBExport

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

    ParameterUsageDescription
    versionNumberInput

    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.

    pParamStructInput Output

    This parameter specifies the target information to be extracted and receives the result information after extraction.

    Refer to "TBExportStruct structure" for more information.

    pSqlcaOutputWhen return code is not SQL_SUCCESS, includes information on errors that occurred within the utility.
  • Return code

    Return CodeDescription
    SQL_SUCCESSFunction completed successfully.
    SQL_SUCCESS_WITH_INFOFunction completed successfully, but there is a warning message.
    SQL_ERRORCritical 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;
    }

6.3.4. TBImport

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

    ParameterUsageDescription
    versionNumberInput

    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.

    pParamStructInput Output

    This parameter specifies the target information to be loaded and receives the result information after loading.

    Refer to TBImportStruct for more information.

    pSqlcaOutputWhen return code is not SQL_SUCCESS, includes the error information occurred within the utility.
  • Return code

    Return codeDescription
    SQL_SUCCESSFunction completed successfully.
    SQL_SUCCESS_WITH_INFOFunction completed successfully, but there is a warning message.
    SQL_ERRORCritical 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;
    }