[CONNECT C++] Database API Introduction

Introduction

In MicroStation CONNECT Edition, we can use Database APIs to write Database apps. Here is a simple guide for the basic programming.

Prerequisites:

1. Create a new DGN file.

2. Prepare a valid Oracle database(You can use other databases also).

Steps:

1. Write preparative variables and functions.
2. Connect to an oracle database.
3. Create MSCATALOG table.
4. Create table "ELEMENTS" with “MSLINK” and “NAME” properties.
5. Create a line element and attach a linkage to this line.
    a. Create a line element in the active model.
    b. Add a linkage to the line element associating it with the entry in the database.
    c. Check the linkage on the line element and get the data from the database.
6. Disconnect the database.

1. Write preparative variables and functions(These variables and functions are just for this example, you can write anything you will use for preparation in your program)

WChar   mscatalogName[MAXFILELENGTH];
UShort  entityNum(1);

static void oracledemo_setupGlobalData
(
void
)
    {
    /* Copy the mscatalog table name. Use the value in env var if it exists, */
    /* else default to "mscatalog". */
    mdlDB_getMscatalogName(mscatalogName);
    }

static void oracledemo_MessageCenter
(
DgnPlatform::OutputMessagePriority     messagePriority,
MsgId msgId,
DgnPlatform::OutputMessageAlert        openAlertBox
)
    {
    WString BriefMessage;
    mdlResource_loadWString (BriefMessage, NULL, MESSAGELISTID_ORACLEDEMOMessages, msgId);
    mdlOutput_messageCenter(messagePriority, BriefMessage.GetWCharCP(), NULL, openAlertBox);
}

static int     oracledemo_appendLinkage
(
    EditElementHandle & eeh,
    UInt32      mslink,
    WCharCP     tableNameP
)
    {
    short       attributes[MAX_ATTRIBSIZE];
    LinkProps   props;
    int         status;
    int         length;

    memset ( &props, 0, sizeof ( props ) );

    status = mdlDB_buildLink ( attributes, &length, ORACLE_LINKAGE, &props, tableNameP, mslink, 0 );
    if ( SUCCESS == status )
        {
        MSElementP  el = eeh.GetElementP ();
        ElementRefP elemRef = eeh.GetElementRef ();

        MSElementP  elBuffer;
        elBuffer = (MSElementP)_alloca ( sizeof ( MSElement ) );
        el->CopyTo ( *elBuffer );

        status = mdlElement_appendAttributes ( elBuffer, length, attributes );
        if ( SUCCESS != status )
            return status;

        eeh.ReplaceElement ( elBuffer );
        status = eeh.ReplaceInModel ( elemRef );
        }

    return  (status);
}

static void oracledemo_checkLinkage ( EditElementHandle& eeh )
    {
    MSElementP elmP = eeh.GetElementP ();

    /* check if this attribute linkage belongs to us */
    if ( mdlDB_elementFilter ( elmP ) )
        {
        DatabaseLink    *link = NULL;
        int linkCount ( 0 );
        int status = mdlDB_extractLinkages ( &link, &linkCount, elmP );

        if ( status == SUCCESS )
            {
            BeAssert ( linkCount == 1 );
            WChar   sqlStatement[256];
            swprintf ( sqlStatement, L"select name from elements where mslink = %d", link[0].mslink );

            CursorID        cursorID;
            if ( SUCCESS == mdlDB_openCursorWithID ( &cursorID, sqlStatement ) )
                {
                bool found ( false );
                MS_sqlda        tableSQLDA;
                int statusFetch = QUERY_NOT_FINISHED;
                while ( statusFetch == QUERY_NOT_FINISHED )
                    {
                    statusFetch = mdlDB_fetchRowByID ( &tableSQLDA, cursorID );
                    if ( QUERY_NOT_FINISHED == statusFetch )
                        {
                        /* step through every column returned from the query */
                        for ( int i = 0; i < tableSQLDA.numColumns; i++ )
                            {
                            std::wstring column ( tableSQLDA.name[i] );
                            std::wstring value ( tableSQLDA.value[i] );

                            if ( wcsicmp ( column.c_str (), L"name" ) == 0 )
                                {
                                if ( wcscmp ( value.c_str (), L"line" ) == 0 )
                                    {
                                    found = true;
                                    break;
                                    }
                                }
                            }
                        if ( found )
                            break;
                        }
                    }

                mdlDB_closeCursorByID ( cursorID );
                mdlDB_freeSQLDADescriptor ( &tableSQLDA );

                if ( !found )
                    {
                    oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_FailedCheckingLinkage, OutputMessageAlert::Dialog );
                    return;
                    }

                oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_succeededCheckingLinkage, OutputMessageAlert::Dialog );
                }
            }
        }
    }

2. Connect to an oracle database

static void oracledemo_connectToDb(WCharCP loginString)
    {
    // Disconnect current database firstly
    mdlDB_activeDatabase(L" ");

    // Unload the Visual SQL Query Builder and Builder apps
    mdlSystem_unloadMdlProgram(L"VSQL");
    mdlSystem_unloadMdlProgram(L"VSQLGEO");

    // Connect to an oracle database, and also create table "MSFORMS". 
    // Variable “loginString” is a string for connecting to an oracle database,     
    // its format should be “user/password@data_source_name”.
    // Returned value “status” should be SUCCESS if the DB is connected.
    int status = mdlDB_changeDatabase(DATABASESERVERID_Oracle, loginString);
    if(status != SUCCESS)
        {
        //Do something for warning.
        }
    }

3. Create MSCATALOG table

static void oracledemo_createTableMSCATALOG(WCharCP)
    {
    if (!mdlDB_isActive())
        {
        oracledemo_MessageCenter(OutputMessagePriority::Warning, MSG_databaseIsNotActive, OutputMessageAlert::Dialog);
        return;
        }

    WChar           sqlStatement[256];
    CursorID        cursorID;

    //Judge if table "mscatalog" is exist
    swprintf(sqlStatement, L"select * from %ls", mscatalogName);
    if (SUCCESS == mdlDB_openCursorWithID(&cursorID, sqlStatement))
        {
        mdlDB_closeCursorByID(cursorID);
        oracledemo_MessageCenter(OutputMessagePriority::Info, MSG_tableMSCATALOGIsExist, OutputMessageAlert::Dialog);
        return;
        }

    swprintf(sqlStatement, L"create table %ls (tablename char(32), entitynum integer, screenform char(64), reporttable char(64), sqlreview char(240), fencefilter char(240), dastable char(32), formtable char(64))", mscatalogName);
    if (SUCCESS != mdlDB_processSQL(sqlStatement))
        {
        oracledemo_MessageCenter(OutputMessagePriority::Warning, MSG_failedCreatingTableMSCATALOG, OutputMessageAlert::Dialog);
        return;
        }
    else
        {
        oracledemo_MessageCenter(OutputMessagePriority::Info, MSG_succeededCreatingTableMSCATALOG, OutputMessageAlert::None);
        }
    }

4. Create table "ELEMENTS" with “MSLINK” and “NAME” properties

static void oracledemo_createTableELEMENTS()
    {
    WChar           sqlStatement[256];
    CursorID        cursorID;

    //Judge if table "ELEMENTS" is exist. If it is exist, clear its rows.
    swprintf(sqlStatement, L"select * from %ls", L"ELEMENTS");
    if (SUCCESS == mdlDB_openCursorWithID(&cursorID, sqlStatement))
        {
        mdlDB_closeCursorByID(cursorID);
        swprintf ( sqlStatement, L"TRANCATE TABLE ELEMENTS" );
        mdlDB_processSQL ( sqlStatement );
        }
    else
        {
        swprintf ( sqlStatement, L"create table ELEMENTS (mslink NUMBER(10,4), name char(64))" );
        if ( SUCCESS != mdlDB_processSQL ( sqlStatement ) )
            {
            oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_failedCreatingTableELEMENTS, OutputMessageAlert::Dialog );
            mdlDB_closeCursorByID ( cursorID );
            return;
            }
        }

    //Check if "ELEMENTS" is in table "MSCATALOG"
    swprintf ( sqlStatement, L"select * from %ls where tablename = '%hs'", L"MSCATALOG", "ELEMENTS" );
    if ( SUCCESS != mdlDB_openCursorWithID ( &cursorID, sqlStatement ) )
        {
        //Insert a row into MSCATALOG to record the table ELEMENTS
        swprintf ( sqlStatement, L"insert into %ls (tablename, entitynum) values('ELEMENTS', %hu)", mscatalogName, entityNum++ );
        if ( SUCCESS != mdlDB_processSQL ( sqlStatement ) )
            {
            oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_failedInsertingRowIntoMSCATALOG, OutputMessageAlert::Dialog );
            mdlDB_closeCursorByID ( cursorID );
            return;
            }
        }

    oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_succeededCreatingTableELEMENTS, OutputMessageAlert::Dialog );

    mdlDB_closeCursorByID ( cursorID );
    mdlDB_processSQL(L"reload");//Need to reload the db, if the table is newly created.
    }

5. Create a line element and attach a linkage to this line

static void oracledemo_attachAndCheckLinkage ()
    {
    WChar   sqlStatement[256];

    //Create a line
    bool is3d = ACTIVEMODEL->Is3d ();
    DSegment3d  segment;
    segment.Init ( 0.0, 0.0, 0.0, 3.0, 3.0, 0.0 );
    EditElementHandle  eeh;
    BentleyStatus status = LineHandler::CreateLineElement ( eeh, nullptr, segment, is3d, *ACTIVEMODEL );
    if ( SUCCESS != status )
        oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_failedCreatingLineElement, OutputMessageAlert::Dialog );
    StatusInt addStatus = eeh.AddToModel ();
    if ( SUCCESS != addStatus )
        oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_failedCreatingLineElement, OutputMessageAlert::Dialog );

    //Insert a row into table Elements
    UInt32  mslink ( 0 );
    mdlDB_largestMslink ( &mslink, L"elements" );
    mslink++;
    swprintf ( sqlStatement, L"insert into %ls (mslink, name) values(%hu, '%ls')", L"ELEMENTS", mslink, L"line" );
    if ( SUCCESS != mdlDB_processSQL ( sqlStatement ) )
        {
        oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_failedInsertingRowIntoTableELEMENTS, OutputMessageAlert::Dialog );
        return;
        }

    //Attach a linkage to DB row
    if ( SUCCESS != oracledemo_appendLinkage ( eeh, mslink, L"elements" ) )
        {
        oracledemo_MessageCenter ( OutputMessagePriority::Warning, MSG_failedAppendingLinkage, OutputMessageAlert::Dialog );
        return;
        }

    //Check the linkage again
    oracledemo_checkLinkage ( eeh );    
    }

6. Disconnect the database

static void oracledemo_disconnect(WCharCP)
    {
    mdlDB_activeDatabase(L" ");

    /* Unload the Visual SQL Query Builder and Builder apps */
    mdlSystem_unloadMdlProgram(L"VSQL");
    mdlSystem_unloadMdlProgram(L"VSQLGEO");
    }

At last, you can check the attached file "oracledemo.7z" for whole code of this example.

Thank you.

oracledemo.7z