Connecting to external databases

[Anonymous]

I know this topic has come up over the years, so please bear with me. I don't think this is a repeat of things that have been asked before.

I have been tasked with modernizing an ARX extension that has been around for a long time. It connects to an external Oracle database, and is currently built using ASI/ASE extensively to do this. I know that Autodesk announced the deprecation of that technology way back in 2000, and they finally removed it 18 years later. Back in 2000, they stated that the modern way to connect to external databases was via the Data Connection Object. However, they didn't explain how to get from ASI/ASE to DCO.

I have searched forward from ObjectARX R14 to ObjectARX 2021 and haven't found anything resembling a migration guide or even a description of the DCO API. Even our old friend Google hasn't turned up anything. This make me wonder if there ever was such a thing.

I seem to be left with the conclusion that the way one now should make this connection is via the standard C/C++ MFC ODBC classes, and that the so-called Data Connection Object is, in reality, nothing more than a reference to the Microsoft way of doing things.

If that really is the case, I guess what I'm looking for is confirmation from the members of this group that I am correct in my conclusion, and that the task I will now have is to convert/migrate from an archaic technology to another one that has actually been around for years.

My apologies for the long-winded approach, and my thanks in advance for anything that anyone can contribute to ease my confusion.

[daniel_cadext]

I use SQLite as a static library, its embedded so there’s no need for any connection strings.

[Anonymous]

Unfortunately, that doesn't help me. The extension connects, as I mentioned, to an external Oracle database. I have no choice in that part of it, as the database is part of a facilities management system. The ARX in question allows the user to alter aspects of a drawing and have those alterations directly modify the database of the facilities management system. For example, the user can drag around the walls of an office on the drawing, and the ARX will update the space tables in the database to reflect the new square footage of the space.

[tbrammer]

As far as I understood ASE/ASI was replaced by CAO (Connectivity Automation Objects).

The most recent docs I found are those for CAO in Acad2017.. But there are still cao20<locale>.tlb for AutoCAD 2020.

Also see here.

[moogalm]

Hi,

I recently written a post about connecting oracle database with Autocad, hope this gives a head start to you.

https://adndevblog.typepad.com/autocad/2020/03/connecting-oracle-database-with-autocad.html

Please do ask me if you have more questions.

[norman.yuan]

Now that we are talking "modernizing" an old ARX app, I'd make sure it is modernized so that the data accessing part of the application is separated from the AutoCAD/drawing manipulation process. That is, the primary process done by the ARX app deals with AutoCAD/Drawing, based on data from external source; as long as the data supplied meets the Acad/Drawing manipulating requirement, the ARX app itself does not need to know where/how the data is obtained, be it Oracle/Sql Server database, or from a service from the cloud (which is more trendy/future than directly connecting AutoCAD to a database).

So, the ideal modernizing route would be:

1. Update the ARX app based on a set of generalized data, so it would only focus on AutoCAD/Drawing process;

2. Create a set of Interfaces to access the external data (for read, and write. if necessary), the Interfaces can be added to the existing. to-be-updated ARX app;

3. Implementing the data access interfaces in separated project, likely a DLL that will be loaded into the ARX app. 

As you can see, the actual data access code is completely separated from the ARX, and you can use whatever suitable technologies to get data from whatever sources and have no impact to the ARX itself, as long as it can be loaded/called by the ARX app. You can have multiple implementations of the data access interfaces as different DLLs, and make your ARX app configurable to load specific data access DLL based on different data source without having to change/update the ARX app again and again just because of data source change.

[Anonymous]

@moogalm  and @tbrammer :

Thank you for your comments. They helped to solve some of the mysteries, especially the blog post,  although some translation is necessary, since I'm working in C/C++ rather than VB.

[Anonymous]

@norman.yuan:

I completely agree with your comments and opinions on how best to modernize the ARX file, and how it should be layered. Were I building this from scratch, that's the way I would do it. I've used a layered approach like that in any number of platforms that I have built, either in C++ or C#. I've done several projects that resulted in a library of DLLs fronting an underlying database.

Sadly, in this case I don't have that luxury. This particular ARX has been in existence for at least 20 years and has seen the hands of a number of developers in its guts over the years. I last worked on it to make it compatible with AutoCAD (actually Map 3D) 2017. At the time we thought that particular implementation would be its last, since its need seemed to be dying out.

However, out of the blue we have been hit with requests to update it and support it for what could be a few more release cycles, but we haven't been given either the time or the money to permit us to rebuild it from scratch. That's what it would take to do the kind of layering and re-structuring that it should get to make it perfect. The gulf between reality and perfection is rather wide and we're facing the typical problem of a legacy application -- make it work in today's world, but don't change its overall functionality, and how soon can you have it done?

It's good to hear from someone with the same first name as me. There aren't all that many Normans in the world  😉

[siddhartha.bhattacharya]

Hi Daniel,

When I connect SQLite db in ObjectARX I get the following error (in the picture below) at the line: sqlite3_prepare(db, createQuery.c_str()..)

	sqlite3* db;
	if (sqlite3_open("SteelTable.db", &db) != SQLITE_OK) { 
		sqlite3_close(db);
		return 0;
	}

	std::string createQuery = "CREATE TABLE WFPROFILE ("
		"ID   INT PRIMARY KEY     NOT NULL,"
		"D    REAL    NOT NULL,"
		"WT   REAL    NOT NULL,"
		"WB   REAL    NOT NULL,"
		"TF   REAL    NOT NULL,"
		"TB   REAL    NOT NULL);";

	sqlite3_stmt* createStmt;
	sqlite3_prepare(db, createQuery.c_str(), static_cast<int>(createQuery.size()), &createStmt, nullptr);
	if (sqlite3_step(createStmt) != SQLITE_DONE) {
		sqlite3_close(db);
		return 0;
	}

	std::vector<std::string> sqlStmList;
	sqlStmList.emplace_back("INSERT INTO WFPROFILE ('ID','D','WT','WB','TF','TB') VALUES ('1','20','10','15','1','1.5');");
	sqlStmList.emplace_back("INSERT INTO WFPROFILE ('ID','D','WT','WB','TF','TB') VALUES ('2','25','12','17','2.5','2');");
	sqlStmList.emplace_back("INSERT INTO WFPROFILE ('ID','D','WT','WB','TF','TB') VALUES ('3','30','14','19.5','2.5','2.5');");

	for (const auto& insertQuery : sqlStmList) {
		sqlite3_stmt* insertStmt;
		sqlite3_prepare(db, insertQuery.c_str(), static_cast<int>(insertQuery.size()), &insertStmt, nullptr);
		if (sqlite3_step(insertStmt) != SQLITE_DONE) {
			sqlite3_close(db);
			return 0;
		}
	}

	sqlite3_close(db);

	return 1;

This error is not there using the same code in a simple console application or using a simple dll library. Is there any specific ObjectARX project setting which needs to be tweaked for t to work in ObjectARX?  

Any pointers would be great. Thanks

[Alexander.Rivilis]

First of all since AutoCAD 2007 ObjectARX using Unicode strings. So you can have a problem to mix Unicode and Non-Unicode strings.

[daniel_cadext]

Hi, I use CppSQLite3U wrapper

CppSQLite3U is a C++ unicode wrapper around the SQLite3 embedded database library

https://github.com/SunDrop/CppSQLite3U

or you can compile with Unicode and call the SQLite functions that use wchar_t, I.e. sqlite3_open16

my projects are open source, here is one, https://www.theswamp.org/index.php?topic=28286.0

though you may need to join to see the thread