Access ODBC issue/question in MFC Application CDatabase Class
-
Windows 2008 Server, Access 2000 and 2010 (fails on both), Visual Studio 2010 Long story short, I'm trying to use the following query: SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) ) When I execute this in my code it works about 15% of the time. If I add a 3.5 second sleep after the execution of this query before the "read" it seems to work about 95% of the time. The process looks something like this: 1. SELECT INTO chkparam (it should create the table and add 1 record to the table) 2. select from the table looking for the record However the SELECT INTO doesn't always populate the table with the Y. Yes I verified that the table "param" does have a Y. My best guess is the query isn't completing before the code is executed. Is there some way to force completion of the query/transaction? I was alawys under the impression that control wasn't passsed back to the calling procedure until the function was completed. However it seems like I'm getting control back before the function is completing. This is part of our test network, on our live older system we have been using DAO with the same query (obviously different implementation) for 10+ years with no problems. We found that using DAO on our new network slowed down the queries (plus you can't use it with anything new), thus the change to ODBC. This is the code in quesiton (in the live version some of this is replaced by varaibles, but we have hardcoded this for testing for now until it works):
strSQL = _T("SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) ) ")
try
{
//This is the SELECT INTO
// cdw is the CDatabase object
cdw.ExecuteSQL(strSQL);
Sleep(3500);
CString xstrSQL = _T("SELECT TOP 1 * FROM CHKPARAM");
CRecordset getinfoonly(&cdw);
getinfoonly.Open(CRecordset::snapshot, xstrSQL );
if (!getinfoonly.IsEOF())
{
CString tstring1;
short indx = 0;
getinfoonly.GetFieldValue(indx, tstring1);
//AfxMessageBox(xstrSQL+ _T(" YIELDS ") + tstring1);
}
else
//AfxMessageBox(xstrSQL+ _T(" YIELDS NO RECORDS"));getinfoonly.Close(); //getinfoonly.MoveNext(); } catch( CDBException\* e ) { AfxMessageBox(\_T("Given SQL Expression \\n") + strSQL + e->m\_strError ); ok = FALSE; } -
Windows 2008 Server, Access 2000 and 2010 (fails on both), Visual Studio 2010 Long story short, I'm trying to use the following query: SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) ) When I execute this in my code it works about 15% of the time. If I add a 3.5 second sleep after the execution of this query before the "read" it seems to work about 95% of the time. The process looks something like this: 1. SELECT INTO chkparam (it should create the table and add 1 record to the table) 2. select from the table looking for the record However the SELECT INTO doesn't always populate the table with the Y. Yes I verified that the table "param" does have a Y. My best guess is the query isn't completing before the code is executed. Is there some way to force completion of the query/transaction? I was alawys under the impression that control wasn't passsed back to the calling procedure until the function was completed. However it seems like I'm getting control back before the function is completing. This is part of our test network, on our live older system we have been using DAO with the same query (obviously different implementation) for 10+ years with no problems. We found that using DAO on our new network slowed down the queries (plus you can't use it with anything new), thus the change to ODBC. This is the code in quesiton (in the live version some of this is replaced by varaibles, but we have hardcoded this for testing for now until it works):
strSQL = _T("SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) ) ")
try
{
//This is the SELECT INTO
// cdw is the CDatabase object
cdw.ExecuteSQL(strSQL);
Sleep(3500);
CString xstrSQL = _T("SELECT TOP 1 * FROM CHKPARAM");
CRecordset getinfoonly(&cdw);
getinfoonly.Open(CRecordset::snapshot, xstrSQL );
if (!getinfoonly.IsEOF())
{
CString tstring1;
short indx = 0;
getinfoonly.GetFieldValue(indx, tstring1);
//AfxMessageBox(xstrSQL+ _T(" YIELDS ") + tstring1);
}
else
//AfxMessageBox(xstrSQL+ _T(" YIELDS NO RECORDS"));getinfoonly.Close(); //getinfoonly.MoveNext(); } catch( CDBException\* e ) { AfxMessageBox(\_T("Given SQL Expression \\n") + strSQL + e->m\_strError ); ok = FALSE; }MacRaider4 wrote:
cdw.ExecuteSQL(strSQL);
How long is this statement taking to complete? Would SetQueryTimeout() be of any help?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
-
MacRaider4 wrote:
cdw.ExecuteSQL(strSQL);
How long is this statement taking to complete? Would SetQueryTimeout() be of any help?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
Well as mentioned, if we put in a 3.5 second sleep that seems to give it enough time. Granted that seems a bit excessive for, creating a table with one row and col with 1 values of 1 character. I'll try the SetQueryTimeout though to see if that makes any difference.
-
MacRaider4 wrote:
cdw.ExecuteSQL(strSQL);
How long is this statement taking to complete? Would SetQueryTimeout() be of any help?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
Ok we changed the Timeout to 1 second from 45 seconds... and it worked 2 of 3 times. After the failure on the 3rd attempt we stopped as it failed (did not insert the Y and no timeout error).
-
Windows 2008 Server, Access 2000 and 2010 (fails on both), Visual Studio 2010 Long story short, I'm trying to use the following query: SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) ) When I execute this in my code it works about 15% of the time. If I add a 3.5 second sleep after the execution of this query before the "read" it seems to work about 95% of the time. The process looks something like this: 1. SELECT INTO chkparam (it should create the table and add 1 record to the table) 2. select from the table looking for the record However the SELECT INTO doesn't always populate the table with the Y. Yes I verified that the table "param" does have a Y. My best guess is the query isn't completing before the code is executed. Is there some way to force completion of the query/transaction? I was alawys under the impression that control wasn't passsed back to the calling procedure until the function was completed. However it seems like I'm getting control back before the function is completing. This is part of our test network, on our live older system we have been using DAO with the same query (obviously different implementation) for 10+ years with no problems. We found that using DAO on our new network slowed down the queries (plus you can't use it with anything new), thus the change to ODBC. This is the code in quesiton (in the live version some of this is replaced by varaibles, but we have hardcoded this for testing for now until it works):
strSQL = _T("SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE ( ( PARAM.param = 'Y' ) ) ")
try
{
//This is the SELECT INTO
// cdw is the CDatabase object
cdw.ExecuteSQL(strSQL);
Sleep(3500);
CString xstrSQL = _T("SELECT TOP 1 * FROM CHKPARAM");
CRecordset getinfoonly(&cdw);
getinfoonly.Open(CRecordset::snapshot, xstrSQL );
if (!getinfoonly.IsEOF())
{
CString tstring1;
short indx = 0;
getinfoonly.GetFieldValue(indx, tstring1);
//AfxMessageBox(xstrSQL+ _T(" YIELDS ") + tstring1);
}
else
//AfxMessageBox(xstrSQL+ _T(" YIELDS NO RECORDS"));getinfoonly.Close(); //getinfoonly.MoveNext(); } catch( CDBException\* e ) { AfxMessageBox(\_T("Given SQL Expression \\n") + strSQL + e->m\_strError ); ok = FALSE; }I'm not an SQL Expert, but in theory and based on my experience, You want to write SQL commands that does everything you need in 1 command execute, and just assume it worked or write more sql to return a reply. So a conditional SQL Command based on certain parameters being met could look something like below.
IF (SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE PARAM.param = 'Y')
BEGIN
SELECT TOP 1 * FROM CHKPARAM
ENDYou should never have to sleep or wait for an anwser, for the underlying sql client inside the OBDC wrapper should take care of the timing, or socket/pipe and SQL Server latency, in producing your result. Are you destroying the cdw object before creating it again? Are you modeling your SQL Commands in something like EMS SQL Manager to test how long they take to process on a known good program code. This is why I asked in a previous post of mine what others are using to talk to SQL Server, and went with the gut wrenching torture of using the native sql client sqlclin10 to talk to the server. I had really bad luck using ODBC back in 2002, and will never use it again.
-
I'm not an SQL Expert, but in theory and based on my experience, You want to write SQL commands that does everything you need in 1 command execute, and just assume it worked or write more sql to return a reply. So a conditional SQL Command based on certain parameters being met could look something like below.
IF (SELECT PARAM.param INTO CHKPARAM FROM PARAM WHERE PARAM.param = 'Y')
BEGIN
SELECT TOP 1 * FROM CHKPARAM
ENDYou should never have to sleep or wait for an anwser, for the underlying sql client inside the OBDC wrapper should take care of the timing, or socket/pipe and SQL Server latency, in producing your result. Are you destroying the cdw object before creating it again? Are you modeling your SQL Commands in something like EMS SQL Manager to test how long they take to process on a known good program code. This is why I asked in a previous post of mine what others are using to talk to SQL Server, and went with the gut wrenching torture of using the native sql client sqlclin10 to talk to the server. I had really bad luck using ODBC back in 2002, and will never use it again.
This portion of the application is using an Access database not SQL Server (just wanted to be sure I'm clear about that). As far as I can tell the cdw object is not being destroyed before it's being created again, but I'll double check with our senior programmer. It may have got lost in the original post, but this does work fine with ADO on our live network (Win Server 2000, Access 2000 and Visual Studio 6), this has happened with the move to 2008, Access 2000 and 2010 and VS 2010.
-
This portion of the application is using an Access database not SQL Server (just wanted to be sure I'm clear about that). As far as I can tell the cdw object is not being destroyed before it's being created again, but I'll double check with our senior programmer. It may have got lost in the original post, but this does work fine with ADO on our live network (Win Server 2000, Access 2000 and Visual Studio 6), this has happened with the move to 2008, Access 2000 and 2010 and VS 2010.
MacRaider4 wrote:
with the move to 2008, Access 2000 and 2010 and VS 2010.
I don't have an answer, just thoughts about the process. I know it was a access database, quite clear, but over time now in 2012, they sort of became the same to me, with very slight differences, from the client code perspective. I was still thinking about your issue while eating lunch, and 2 things stick in my mind, which is the first call (Line 1), and then the next call below. I guess the code below was a fragment, and is never sent as a request. Must of been a test command to check for sanity.
CString xstrSQL = _T("SELECT TOP 1 * FROM CHKPARAM");
The other is the execution process, There all kind of the same, but yours was different. Make Connection Object Make and set SQL Command Object Make Reader and Execute SQL Command Wait and Read Results Close Reader, Connection Objects Destroy Objects Overall, all I can think of since it fails on both Access 2000 and 2010, is that VS2010 called up a more modern version of ODBC, and that your current code sent out the proper credentials and established a valid session, made the execute request, the response was returned, but is getting lost, stuck, or not being sent at all back to the client, or the reader object was not able to load the response, because it's still working on the previous response. The Microsoft world has changed alot since the days of VS6, Access 2000 and Server 2000. Technologies sort of have to match up like your original working project.
-
MacRaider4 wrote:
with the move to 2008, Access 2000 and 2010 and VS 2010.
I don't have an answer, just thoughts about the process. I know it was a access database, quite clear, but over time now in 2012, they sort of became the same to me, with very slight differences, from the client code perspective. I was still thinking about your issue while eating lunch, and 2 things stick in my mind, which is the first call (Line 1), and then the next call below. I guess the code below was a fragment, and is never sent as a request. Must of been a test command to check for sanity.
CString xstrSQL = _T("SELECT TOP 1 * FROM CHKPARAM");
The other is the execution process, There all kind of the same, but yours was different. Make Connection Object Make and set SQL Command Object Make Reader and Execute SQL Command Wait and Read Results Close Reader, Connection Objects Destroy Objects Overall, all I can think of since it fails on both Access 2000 and 2010, is that VS2010 called up a more modern version of ODBC, and that your current code sent out the proper credentials and established a valid session, made the execute request, the response was returned, but is getting lost, stuck, or not being sent at all back to the client, or the reader object was not able to load the response, because it's still working on the previous response. The Microsoft world has changed alot since the days of VS6, Access 2000 and Server 2000. Technologies sort of have to match up like your original working project.
Basically what has happened is the general functionality of the program has gone to hell (for a lack of better term). 95% or so of our business is run off this application of which basically processes queries. (I'm still fairly new here and new to C++ so my explination will be a bit simple but should give you an idea). Everything is based on a query. So you start with Query1 which normally retreives data from our SQL Server Database and stores the results in a temp (access), there is generally another "check" query looking to make sure that you got results (looking for a row count greater than 0), then you may or may not perform another query based on that and so forth. So yes this is part of a test we wrote into the larger application because of the issue we where having. That is why there is the SELECT INTO and then the SELECT FROM right after it, the SELECT FROM is checking to see if the table was populated as this application doesn't have a way to check the table for a value (i.e. can't check param for the Y, thus the SELECT INTO CHKPARAM where param.param = y). It may be a bit confusing and I'm sure I didn't explain it the best as I'm trying to keep this short. But I'll be happy to try to answer any questions until our senior guy gets in.
-
Basically what has happened is the general functionality of the program has gone to hell (for a lack of better term). 95% or so of our business is run off this application of which basically processes queries. (I'm still fairly new here and new to C++ so my explination will be a bit simple but should give you an idea). Everything is based on a query. So you start with Query1 which normally retreives data from our SQL Server Database and stores the results in a temp (access), there is generally another "check" query looking to make sure that you got results (looking for a row count greater than 0), then you may or may not perform another query based on that and so forth. So yes this is part of a test we wrote into the larger application because of the issue we where having. That is why there is the SELECT INTO and then the SELECT FROM right after it, the SELECT FROM is checking to see if the table was populated as this application doesn't have a way to check the table for a value (i.e. can't check param for the Y, thus the SELECT INTO CHKPARAM where param.param = y). It may be a bit confusing and I'm sure I didn't explain it the best as I'm trying to keep this short. But I'll be happy to try to answer any questions until our senior guy gets in.
Here is some information that I ran across for something else. I know your using Access, but the principals should be the same. Could have something to do with ANSI characters in the older code you guys wrote. I wrote my SQL Commands in Unicode first, and it worked fine. http://msdn.microsoft.com/en-us/library/ms811006.aspx[^] Driver and SQL Server Versions The following table shows which versions of the Microsoft SQL Server ODBC driver shipped with recent versions and service packs (SP) of Microsoft SQL Server. It also lists the operating system versions under which the drivers are certified to run and the versions of SQL Server against which they are certified to work. Newer drivers recognize the capabilities of older databases and adjust to work with the features that exist in the older server. For example, if a user connects a version 2.65 driver to a version 4.21a server, the driver does not attempt to use ANSI or other options that did not exist in SQL Server 4.21a. Conversely, older drivers do not use the features available in newer servers. For example, if a version 2.50 driver connects to a version 6.5 server, the driver has no code to use any new features or options introduced in the 6.5 server.
-
Here is some information that I ran across for something else. I know your using Access, but the principals should be the same. Could have something to do with ANSI characters in the older code you guys wrote. I wrote my SQL Commands in Unicode first, and it worked fine. http://msdn.microsoft.com/en-us/library/ms811006.aspx[^] Driver and SQL Server Versions The following table shows which versions of the Microsoft SQL Server ODBC driver shipped with recent versions and service packs (SP) of Microsoft SQL Server. It also lists the operating system versions under which the drivers are certified to run and the versions of SQL Server against which they are certified to work. Newer drivers recognize the capabilities of older databases and adjust to work with the features that exist in the older server. For example, if a user connects a version 2.65 driver to a version 4.21a server, the driver does not attempt to use ANSI or other options that did not exist in SQL Server 4.21a. Conversely, older drivers do not use the features available in newer servers. For example, if a version 2.50 driver connects to a version 6.5 server, the driver has no code to use any new features or options introduced in the 6.5 server.
Spent some time over the weekend making some tweaks and this seems to be it... I got it working with a trial version of Access 2010 (seems to be 100% of the time) and about 95% with 2000. We plan to go with 2010 anyways so I guess it's "fixed". Thank you for your help!
-
Spent some time over the weekend making some tweaks and this seems to be it... I got it working with a trial version of Access 2010 (seems to be 100% of the time) and about 95% with 2000. We plan to go with 2010 anyways so I guess it's "fixed". Thank you for your help!