Solved: Microsoft SQL and VC++ programming

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

Mark Parr🇺🇸

Microsoft SQL and VC++ programming

I have two VC++ programs -- one that only writes to a SQL database and another that reads from it. I'm using ADO thru ODBC Drivers to connect both programs to the SQL Server and the database. I create my Connection and Recordset in both programs as follows:

ADODB::_ConnectionPtr DBConn = NULL;
ADODB::_RecordsetPtr RecSet = NULL;

TESTHR(DBConn.CreateInstance(__uuidof(ADODB::Connection)));

DBConn->CommandTimeout = 300; // Added when number of records got large
DBConn->Open(_bstr_t("DSN=DBTest"), _bstr_t(""), _bstr_t(""), ADODB::adModeUnknown);

TESTHR(RecSet.CreateInstance(__uuidof(ADODB::Recordset)));
RecSet->Open("DBTestTable", ArcReplayDB.GetInterfacePtr(), ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);

Over time time and testing, the number of entries in the table have gotten very large. I did not originally have the CommandTimeout defined and the RecSet->Open() would timeout. I added an arbitrary value to see if that would solve the open problem and it did. However, the table is now even larger and the RecSet->Open() is starting to timeout again.

I would like to approach this in two manners:

#1. The "write" program is not concerned with the entries already in the table. If possible, I would like to open the recordset in a manner that I can add new records but it does not need to access all the existing records to perform it's function so in theory a timeout value is not needed.

#2. The "read" program does need to access a undetermined amount of the database based on user input. Basically, the provde a starting and stopping date/time range and all records are returned for that range. With alot of records, I can see why it might take awhile for a response and a timeout could happen. Is there a faster way to speed up the database response without having to prompt the user for a specified wait time or can I recover from the timeout, warn the user things are still happening, and continue the search.

I'm new to ADO and have been working my way thru it but I'm a little stuck here. Is it possibile to perform the RecordSet opens in the 2 different manners described without having to provide a timeout value -- either at the user interface level or in some configuration file?

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

adathelad🇬🇧

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Mark Parr🇺🇸

ASKER

Couple of things that I failed to mention in the original post:

#1. The "write" program is a constant activity program. It is constantly collecting data thru a network socket from a remote system and writing the data to the SQL Server. That's part of the reason that I open it the one time and then do multiple writes to the database. I'm doing the ADO AddNew() and Update() calls to add records.

#2. Both programs are not MicroSoft SQL dependant. They need to be able to run with whatever the desired database environment happens to be -- be it MySQL, PostgreSQL, Oracle, etc. Currently, I have the programs are running with MicroSoft SQL and MySQL environment. We have not created the large database environment yet in MySQL so I don't know if I will have similar problems or not.

adathelad🇬🇧

If you have to use exactly the same methods and code to query/update each different type of database (can't see that working) then you have to live with the poor performance to a certain degree. Other than optimizing your VC++., which wont make much difference) there is not a lot you can do.

Optimizing queries is something that needs to be done specific to each database system, and an optimized query that works perfectly on SQL Server might not necessarily work on Oracle. You won't get good performance by using exactly the same methods/queries for each database system.
I'd suggest that you need to do something like this (pseudo code):

If DatabaseSystem is SQL Server
Call this stored procedure
Else if it's Oracle
Run this oracle-specific query
Else if it's Cache
Run this Cache-specific query

Mark Parr🇺🇸

ASKER

With "SQL Database Differences" in mind, is there a call that can be made that will programmatically tell me the type of database that I'm connected to? I tried suggestion (1b) from the original reply and that seems to work -- for MS SQL Server. However, that recordset open command is not supported by MySQL. Just wondering if I can make a call and determine the type Database that I'm dealing with rather than relying on the user to set a flag. Then based on the flag, open the recordset, perform the query (reply #2), and other functions, etc. based on the type of DB.

Thanks
Mark

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

adathelad🇬🇧

Unless you can parse the Provider property of the adodb connection object and tell from that, then I would say that the program needs to be told explicitly what type of database is being used.

HTH

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.