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 ); } } } }
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. } }
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); } }
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. }
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 ); }
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.
It's good to see a DB example! Relational databases seem to have been pushed into the background by the shiny new ECSchema and Item Types stuff.