|
My application currently opens its data base connection when it starts, and closes it when it exits. The application runs for long periods (days or even weeks). The problem lies in that it accesses the data base infrequently. Accesses may be minutes or even hours apart in time. In other instances, there may be several accesses within a few milliseconds of each other.
At some sites, it appears that the data base server (SQL Server 2005) is dropping the connection because my application is not keeping up a steady traffic of operations.
One option would be for me to change my code to open the data base connection, perform the operation, and close the connection, for each and every data base transaction.
This approach seems clumsy, however. Does anyone have any better suggestions?
Software Zen: delete this;
|
|
|
|
|
Gary Wheeler wrote: Does anyone have any better suggestions?
No. AFAIK open-use-close is the normal pattern. And connection pooling takes care of most performance issues that result from frequent opens/closes.
[ADDED] The above applies when using .NET Framework.[/ADDED]
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Local announcement (Antwerp region): Lange Wapper? Neen!
modified on Thursday, October 8, 2009 7:47 AM
|
|
|
|
|
Thanks, Luc!
I had a feeling that was the case, but thought I would make sure.
Software Zen: delete this;
|
|
|
|
|
you're welcome.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
will always prefer disconnected architecture means connect to db when their is needs to connect to db mostly in CRUD operations.
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
You could consider some sort of connection pooling strategy. Open-use-close is not very efficient when you are accessing the database very frequently. It is more efficient to pool and reuse open connections in this circumstance. One technique that connection pooling libraries use is to test the connection before returning it from the pool. Then if the connection is bad (e.g. timed out by the database) you can throw it away and open a new one. That way, if your application is making lots of database accesses you avoid the overhead of opening and closing connections all the time. When your application idles and does not access the database for a long time, the connection can be dropped and a new one will be opened, "invisibly" from the point of view of the code using the connection.
You don't say what language of framework your application uses, but there are connection pooling libraries available for .NET, Java, Python, Ruby and many others.
|
|
|
|
|
I'm using C++/MFC (stop laughing  ). The data base activity is an 'add-on' to a process control application. I'm thinking of moving the data base handling off into a separate thread of its own, so that it can do the open/CRUD/close without affecting the other stuff that's going on.
Software Zen: delete this;
|
|
|
|
|
Instantiate the connection once, but open and close it only as needed.
|
|
|
|
|
Hello people!
I am not sure this is the right place for this question or not but i try my luck, sorry if it not. So, the problem, we are writing a VC++ program in which we have to make some BLOB updates. Now, this program is multi-threaded, there are 2 threads which perform DB queries (thorough Oracle XE client) sometimes simulteniously but they both use the same connection, one of these threads performs BLOB updates, the other one never works with BLOBs, it only calls a stored procedure which does some updates on a table (and yes, this table can be updated by both of these threads at the same time, but they never work on the same columns simulteniously). This seems to work well most of the time but (there's always a but, isn't there) time to time we get errors like "The update cannot be performed because the database has changed" or "ORA-22990: LOB locators cannot span transactions Error source: 'OraOLEDB'". Our guess is that the problem is that the same table/row is being updated by the 2 threads, but how to overcome this? The problem is that one of these threads, which doesn't do BLOB updates, has to do its work at specific times, so saying, "while a BLOB update is in progress, don't let anyone else touch the DB" is not a solution, since the BLOB update can take a lot of time and not allowing the other thread to perform its queries will lead to problems.
I have to admit that i am not the "DB guy", but i visit CodeProject frequently and since it has a great forum for DB-related stuff, i thought i ask, maybe it has a pretty obvious and simple solution we don't know about and if so i can then forward it to the DB guys and we can fix the problem.
So, anyone can share her/his wisdom and help? Thanks in advance for any answers.
> The problem with computers is that they do what you tell them to do and not what you want them to do. <
> Sometimes you just have to hate coding to do it well. <
|
|
|
|
|
Good Day All
I have a File Format Defined like this
9.0
4
1 SQLCHAR 0 100 "," 0 ExtraField ""
2 SQLCHAR 0 100 "," 1 Descr SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 2 ABREV SQL_Latin1_General_CP1_CI_AS
and i use it like this
BULK INSERT dbo.TBL_CMPS FROM 'C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv'
WITH (
FORMATFILE = 'C:\Format.DAT',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n' );
and my Table Defination is like this
CREATE TABLE [dbo].[TBL_CMPS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DESCR] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ABREV] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
and my Error is
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "C:\Format.DAT".
Thank you
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I think if you change the column count after 9.0 to 3, it should work.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
i get this
Msg 4862, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Format.DAT" could not be read. Operating system error code (null).
I have give a Full Permission to Everyone, Admin, ASP.NET
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Try placing return(enter) after SQL_Latin1_General_CP1_CI_AS.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
still the Same Error
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Its looking for the file in c:\ on your SQL SERVER machine, not your pc. Have you copied the file there?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
yes. The First Part of my code is to code this file in that Directory,in debug mode, i can see that the file has been created there and it has proper permissions on it.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Just a thought,if you are creating the format file in code before calling your proc, are you sure your code has properly closed the file? While in debug can you stop just before running the proc and see if you can open the file with another program such as notepad? May be total rubbish, but its a thought
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Yes the File is totally closed. I have thought of another way to do this. well not always a good idea. The Reason why i use a format File is that there is an Identity Field. Now what i did is that i drop the field and imported the data and recreated the Field and it looks OK.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
There are many ways to skin a cat - pleased you found one that worked for you 
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thank you
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
hi
I want to make a query for this data can any one of u help me;
DATA:
Person Name value
XYZ a 10
XYZ b 5
XYZ a 15
XYZ b 4
XYZ c 1
XYZ a 5
XYZ d 10
XYZ a 10
...
...
This is the of one group;
now for I want this result for this data and so on;
Person Name Total
XYZ a 40
XYZ others 20
abc a ##
abc others ##
Please write a query for this.
Thanks ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Syed Shahid Hussain
|
|
|
|
|
Syed Shahid Hussain wrote: Please write a query for this.
Why?
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
very stupid,
if u dont know then say "i dont know sorry" what do u mean by WHY?
Syed Shahid Hussain
|
|
|
|
|
Syed Shahid Hussain wrote: if u dont know then say "i dont know sorry"
Instead, I do not reply if I do not know anything about the question.
Syed Shahid Hussain wrote: what do u mean by WHY?
Is this a quiz website? No.
Why should I do your work? If you had tried something and got stuck, then many would be interested to help you.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
d@nish wrote: Is this a quiz website?
brother it is a forum I think u dont know the meaning of forum, here people help each other without Why? and without any thing. I'd solved my problem but I want some suggestions from others.
Now if u know any idea about that then reply otherwise keep quiet
thank you very much.
Syed Shahid Hussain
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
