|
Hi all,
I have been testing the connection pooling in ADO 2.6, and seems to work fine. By changing the OLE DB Services parameter I have seen how the pooling is enabled or disabled, in this way:
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Sub Command1_Click()
Dim start, finish, duration As Long
Dim conn, conn2, conn3, conn4 As ADODB.Connection
Dim strConn
strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=ALBERT;Initial Catalog=MyCatalog;User ID=arichart;Trusted_Connection=Yes;OLE DB Services = -2;"
'Conn1
start = GetTickCount()
Set conn = New ADODB.Connection
conn.ConnectionString = strConn
conn.CursorLocation = adUseClient
conn.Open
finish = GetTickCount()
duration = finish - start
Debug.Print "Duration 1: " & Str(duration)
conn.Close
Set conn = Nothing
'Conn2
start = GetTickCount()
Set conn2 = New ADODB.Connection
conn2.ConnectionString = strConn
conn2.CursorLocation = adUseClient
conn2.Open
finish = GetTickCount()
duration = finish - start
Debug.Print "Duration 2: " & Str(duration)
conn2.Close
Set conn2 = Nothing
I have two more connection objects in the code I have tested (omitted here to simplify).
Ok. Perfect. The problem is that I have tested it again in Visual C++ and does not work. If I set the OLE DB Services to -1 (or 0), then pooling is enabled, and if I set it to -2, then pooling is disabled. Do you know why does it work in different ways in Visual C++ and Visual Basic?
The code in Visual C++ looks like this. I use a wrapper class to encapsulate the ADODB::_ConnectionPtr object:
CADOConnection Conn;
CString strConn = _T("Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=ALBERT;Initial Catalog=MyCatalog;User ID=arichart;Trusted_Connection=Yes;OLE DB Services = -2;");
Conn.SetConnectionString(strConn);
Conn.Connect();
The connect function looks like this:
bool CADOConnection::Connect()
{
ASSERT ( m_pADOConnection == NULL );
if ( m_pADOConnection != NULL )
Close(false);
#ifdef _DEBUG
clock_t start, finish;
start = clock();
#endif
HRESULT hr = S_OK;
try {
TESTHR(m_pADOConnection.CreateInstance(_T("ADODB.Connection")));
m_pADOConnection->ConnectionString = m_strConnectionString.AllocSysString();
m_pADOConnection->CursorLocation = ADODB::adUseClient;
TESTHR(m_pADOConnection->Open("", "", "",ADODB::adConnectUnspecified));
} catch ( _com_error &e ) {
CString strError = e.Description().copy();
AfxMessageBox(strError);
m_pADOConnection = NULL;
return false;
}
#ifdef _DEBUG
finish = clock();
double duration = (double)(finish - start) / CLOCKS_PER_SEC;
TRACE("ADO Connection opened. Duration: %2.3f seconds\n", duration );
#endif
return true;
}
I'm working on Windows 2000
I hope anybody could answer my question. Thanks in advance.
|
|
|
|
|
I have a couple of questions about using BeginTrans() in a try catch block. Below is an example of how I use ADO to modify a record in my Application. This application was originally made to use JET and I am tyring to migrate to MSDE. When I was using JET to maintain consistancy between the records of one connection and the records of another connection in the same app using a different thread I had to flush the cache in Jet ( using a call to pJetEngine->RefreshCache( m_pConnection)) and then call BeginTrans, update the data and call CommitTrans. In JET each ConnectionPtr has a cache on the database that is independent of other open connections. Using this procedure the thread will see and modify the current data in the db and not what was cached for this connection. Do we have to do someting like this with MSDE? Is there a way to flush the cache? Also is it possible that BeginTrans / CommitTrans will throw an exception? If so how do I handle BeginTrans inside a try / catch block? Do I call RollbackTrans in each of my catch routines? Could RollbackTrans() throw an exception?
_RecordsetPtr pRst = NULL;
try
{
TESTHR(pRst.CreateInstance(__uuidof(Recordset)));
pRst->Open(_bstr_t(strQuery), m_pConnection, adOpenStatic ,
adLockOptimistic, adCmdText);
m_pConnection->BeginTrans();
int count = pRst->GetRecordCount();
if ( count == 1 ) {
// Modify record using PutCollect
}
pRst->Update();
pRst->Close();
pRst=NULL;
m_pConnection->CommitTrans();
}
catch (_com_error &e)
{
GenerateError(e.Error(), e.ErrorMessage(), e.Description(),__FILE__,__LINE__);
}
catch( CException* e)
{
GenerateError(e,__FILE__,__LINE__);
}
catch (...)
{
GenerateError(__FILE__,__LINE__);
}
|
|
|
|
|
Hi
Do I call RollbackTrans in each of my catch routines?
Looking at your sample, the answer is yes. But I have one question: why your try/catch block and begintrans/committrans enclose all your recordset operation?
Regards,
Wanderley
|
|
|
|
|
There are several cases in my database where I need to guarantee that the data read from one ConnectionPtr is the latest data written to the database. This generally happens when I need to generate an ID for something and don't use an AutoNumber. One example of this is when I need to generate a Patient ID when the user has not entered a valid one and the case has been forcibly closed because of inactivity.
As I said in my original description, because of caching that occurs in the connection at least when using JET to gaurantee you have the latest data from one ConnectionPtr to another you have to flush then cache (on reads) and do begintrans/committrans on all writes that need to be the latest. There is a MS KB article Q200300 that explains the problem as it refers to JET, it is a couple of years old and I have no idea if it applies to MSDE also.
Below is taken from the KB article:
In certain situations you may need to use two separate ADO connections when writing and reading data from an Access database using the Jet OLEDB Provider. For example, if you have two separate processes that are writing and reading to the same Access database, there is no way to share a single connection. In this situation, you can synchronize writes and reads with separate ADO connections if you follow these guidelines:
The writer must start a transaction, using ADO's Connection.BeginTrans, prior to writing the data.
The writer must make the database updates and then commit the transaction (using ADO's Connection.CommitTrans).
The reader must call JRO.JetEngine.RefreshCache passing in it's connection prior to attempting to read the data.
|
|
|
|
|
Hi
I asked that because I use a different approach for try/catch and begin/rollback/committrans and even for custom generated PKIDs (I create it at stored procedure level).
Sorry, I couldn't help this time. 
Regards,
Wanderley
|
|
|
|
|
Thanks. Actually you have been very helpful. I have not used any stored procedures (except the system ones) yet.. I've only started to migrate from JET to MSDE last friday and T-SQL is new to me.
|
|
|
|
|
Hi
One more thing: do you need to be sure that you have the latest data to create unique IDs? Do you keep a table of IDs and increment it whenever you need?
I said I have a different approach because I just use try/catch blocks where it's more likely (is that right? english is my second language ) to have trouble. In your sample, I would use it only when opening the recordset. And I use begin/commit/rollback when I'm actually writing data (before addnew and after update, for example).
Regards,
Wanderley
|
|
|
|
|
I have built a database that has some tables that use the money datatype. The pain in the arse in when I retrieve the data using SQL it always returns 4 decimal places.
Is there some way that I force SQL Server to only store 2 decimal places? Or is the something I can do in the SQL statement to truncate/round it to 2 decimal places? Either will do as I only store 2 decimal places worth of value in there anyway.
Michael Martin
Pegasystems Pty Ltd
Australia
martm@pegasystems.com
+61 413-004-018
"Don't belong. Never join. Think for yourself. Peace"
- Victor Stone
|
|
|
|
|
Hi
Is there a specific reason to use money? You could use numeric instead of money - this way you can define how many decimal places you need.
Do you need to make calculations after you retrieve the data? If not, you could use SELECT STR(<your field="">, 8, 2) FROM [...]
HTH
Regards,
Wanderley
|
|
|
|
|
Wanderley
thanks for the information looks like what I need. There isn't any reason to use money other than I was lazy, saw it and used it. I will try it as numeric, otherwise I will use the SELECT trick. 
Michael Martin
Pegasystems Pty Ltd
Australia
martm@pegasystems.com
+61 413-004-018
"Don't belong. Never join. Think for yourself. Peace"
- Victor Stone
|
|
|
|
|
I'm having a problem migrating from JET to MSDN 2000 in my application. I keep getting timeout errors and the following error message "cannot start more transactions on this session." Is this the result of the 5 connection limit on MSDE? I am only opening and using the program in one application that has multiple threads and opens the database with a connection for each thread that needs db access.
|
|
|
|
|
I think I found the problem. It was with my transactions. I called _pConnection->BeginTrans() inside a try / catch block. When an exception was raised because of a duplicate index, the m_pConnection->CommitTrans() was not being called and the transaction was still open. The next call (and all subsequent) call to BeginTrans would fail.
|
|
|
|
|
Can anyone tell me why recordset data from an Access table can't be displayed on a DataGrid control if the recordset cursor location is set to the server?
|
|
|
|
|
For a recordset to be bound to a grid, it must support bookmarks. A server side dynamic cursor on SQL Server does not support bookmarks. Try using a keyset cursor.
also see msdn article Q224192
http://support.microsoft.com/support/kb/articles/Q224/1/92.ASP
|
|
|
|
|
I have a web classifieds system that I wrote for a client and I am having some trouble with the aging out of accounts. THis system is built with ASP and interfaces with MSSQL Server 7.0.
What happens is this, a person subscribes for a period of 90 days. They pay their fee and get the run of the system for 90 days. When the 90 days are up they are no longer allowed access. They can still log in, but they are only given access to the pay area so they can re-up their subscription. If they do not re-up then after a while their account goes away entirely.
So I have three states of user:
Non-Subscriber
Subscriber
Expired Subscriber
Non-subscribers become subscribers by entering their user info and paying, that is simple enough and it works fine.
Subscribers last for 90 days and then they become Expired Subscribers. (They get flagged in the database as expired)
Expired Subscribers last for another 30 days and then they go away. (They are deleted from the database)
So my question:
What is the most efficient way to test for the age of the accounts? (I am comfortable with databases, but I do not claim to be an expert so I am curious about what you guys think SQL wise)
Also, how would you guys get the system to run the check routine regularly? Say once a day. This is an ASP based system and it is hosted on a virtual hosting provider so I cannot just set up a scheduled job to run on the server like I normally would. I am going to have to find a tricky way to do it.
TIA!
Jason Jystad
Cito Technologies
www.citotech.net
>------------------------------------------------<
"Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing."
-- Dirk Gently (Douglas Adams)
>------------------------------------------------<
|
|
|
|
|
Hi
I just store the date of my last check in a simple text file. When a user logs in, I compare the dates. If my check is out of date, I run some stored procedures and update the text file.
Not the best solution ever, but it works and you don't have to update your db manually.
HTH
Regards,
Wanderley
|
|
|
|
|
Does running the stored procedures slow down the user's login at all? In other words, does the stored procedure have to finish before the asp continues? Or can I just trigger the stored procedure and then continue on with login and browsing, from the script perspective. I have done only limited stored procedure work, so fogive me if I am being dense.
I assume the db server would happily go off and run the stored proc while the user continued with his session, but I am unsure. If the user can continue browsing the site after logging in without any percieved delay, then this would seem to be the solution I am looking for.
Thanks
Jason
Jason Jystad
Cito Technologies
www.citotech.net
>------------------------------------------------<
"Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing."
-- Dirk Gently (Douglas Adams)
>------------------------------------------------<
|
|
|
|
|
Hi
Does running the stored procedures slow down the user's login at all?
In my case, no - it will depend on how many users you have and if your table is properly indexed. You can run some tests against your db to check how long does it take. Remember: the SP will run only once a day. Even if there's a little delay, it will be just for the first user of the day (and it could be you, if you wake up early )
does the stored procedure have to finish before the asp continues?
I would say yes, because you may check your data against an out of date table, in case the SP is still running in background.
Regards,
Wanderley
|
|
|
|
|
Thanks for the help!
You gave me a couple of ideas I hadn't thought of before. That was what I needed, some fresh angles on the problem.
I wanted to do the check on session start, but I didn't want to slow down all the logins too much. It totally hadn't occured to me to flag myself with a file so it didn't always run. (Slaps self on forehead  ) Sometimes you just get too involved in a problem and forget the simple solutions.
That coupled with moving the code into an SP should do the trick nicely.
Thanks again,
Jason Jystad
Cito Technologies
www.citotech.net
>------------------------------------------------<
"Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing."
-- Dirk Gently (Douglas Adams)
>------------------------------------------------<
|
|
|
|
|
What is the most efficient method of identifying duplicate records in my database? The duplication is likely to be in more than one field but within the same table. I don't want to enforce non-duplication using a primary key as the duplicates won't neccessarily need to be removed.
I thought of building up a list of the unique entries and then comparing each record against this but it seems like it would take an awful long time.
Cheers
James Spibey
Well cover me in honey and throw me to the lesbians!
|
|
|
|
|
Hi
Imagine you have a table with 2 fields (name and age):
1. To find duplicate names, use SELECT [name], COUNT(*) AS TOTAL FROM [table] GROUP BY [name] HAVING COUNT(*)>1
2. If you need to check both fields, just add [age] in SELECT and GROUP BY.
HTH
Regards,
Wanderley
ps: COUNT(*) will give you the number of times your field is duplicated
|
|
|
|
|
Thanks for your response - it's a great help.
How would I adjust the query to print out the duplicate records in grouped by the duplicate names ie
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13
(Excessive amount of sample data, I know)
Cheers
James Spibey
Well cover me in honey and throw me to the lesbians!
|
|
|
|
|
Hi
Do you want just a list of unique names or you need to know the duplicated ones? From your example:
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13
John 12 9
Do you want Tom, Steve and John (without duplicates) or Tom and Steve?
Regards,
Wanderley
|
|
|
|
|
Hi Wanderley - thanks for the help so far,
I want to find all the records which are duplicated. Those which are duplicated, I want to be able to list each of the duplicated records giving the user the option to pick which one is the correct one. All the records for which there is only one entry should not be displayed. Does this make sense?
So I want to display the information as displayed above. The database would probably also contain many records which were not duplicated which have not been displayed.
Can this be done?
James Spibey
Well cover me in honey and throw me to the lesbians!
|
|
|
|
|
Hi
Assuming that you're checking for duplicated names, you could do something like this:
1. Create a combobox and fill it with all duplicated names using SELECT [Name] FROM [your table] GROUP BY [Name] HAVING COUNT(*)>1
2. Create a listbox (or listview) and fill it with all records that match with the name selected on combobox using SELECT * FROM [your table] WHERE [Name] = 'selected name on combo'
This way, you'll have all duplicated names on the combobox - whenever you select a name, you can clear and fill the listbox and select the correct one.
Is it too confusing? 
Please let me know if you still have problems.
Regards,
Wanderley
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
