Re: 'Best practices' for managing data base connections - Database Discussion Boards

Re: How to write IF Condition in Stored Procedure

Not Active7-Oct-09 14:17

7-Oct-09 14:17

Congradulations, you found the correct forum to post a database question to. Now remove the one in the ASP.NET before someone answers there.

only two letters away from being an asset

Re: How to write IF Condition in Stored Procedure

Not Active7-Oct-09 14:20

Not Active

7-Oct-09 14:20

IF( EXISTS(SELECT ?? FROM [Table] WHERE ??? ) )

And this has had you stuck for days? OMG | :OMG:

only two letters away from being an asset

Re: How to write IF Condition in Stored Procedure

J4amieC7-Oct-09 23:07

J4amieC

7-Oct-09 23:07

Can you post what your database table looks like, and when you want to run this "proces" ie. is it when you update a row? How do you currently update the row? Is there just Old Value and NerwValue in the table?

This question has a lot of detail, but is very confusing. As usual, please post what you have so far in the way of code.

'Best practices' for managing data base connections

Gary Wheeler7-Oct-09 8:29

Gary Wheeler

7-Oct-09 8:29

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;

Re: 'Best practices' for managing data base connections [modified]

Luc Pattyn7-Oct-09 8:37

Luc Pattyn

7-Oct-09 8:37

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]

Smile | :)

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

Re: 'Best practices' for managing data base connections

Gary Wheeler7-Oct-09 8:44

Gary Wheeler

7-Oct-09 8:44

Thanks, Luc!

I had a feeling that was the case, but thought I would make sure.

Software Zen: delete this;

Re: 'Best practices' for managing data base connections

Luc Pattyn7-Oct-09 8:49

Luc Pattyn

7-Oct-09 8:49

you're welcome.

Smile | :)

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!

Re: 'Best practices' for managing data base connections

εїзεїзεїз7-Oct-09 10:57

εїзεїзεїз

7-Oct-09 10:57

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

Re: 'Best practices' for managing data base connections

David Skelly7-Oct-09 22:52

David Skelly

7-Oct-09 22:52

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.

Re: 'Best practices' for managing data base connections

Gary Wheeler8-Oct-09 0:33

Gary Wheeler

8-Oct-09 0:33

I'm using C++/MFC (stop laughing Roll eyes | :rolleyes:

). 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;

Re: 'Best practices' for managing data base connections

PIEBALDconsult12-Oct-09 6:44

PIEBALDconsult

12-Oct-09 6:44

Instantiate the connection once, but open and close it only as needed.

Problem with LOB Locators.

Code-o-mat7-Oct-09 6:37

Code-o-mat

7-Oct-09 6:37

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. <

FIle Format Problems

Vimalsoft(Pty) Ltd7-Oct-09 4:54

Vimalsoft(Pty) Ltd

7-Oct-09 4:54

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/

Re: FIle Format Problems

dan!sh 7-Oct-09 5:15

dan!sh

7-Oct-09 5:15

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

Re: FIle Format Problems

Vimalsoft(Pty) Ltd7-Oct-09 5:23

Vimalsoft(Pty) Ltd

7-Oct-09 5:23

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

Re: FIle Format Problems

dan!sh 7-Oct-09 6:03

dan!sh

7-Oct-09 6:03

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

Re: FIle Format Problems

Vimalsoft(Pty) Ltd7-Oct-09 6:19

Vimalsoft(Pty) Ltd

7-Oct-09 6:19

still the Same Error

Thanks

Re: FIle Format Problems

Ashfield8-Oct-09 1:29

Ashfield

8-Oct-09 1:29

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

Re: FIle Format Problems

Vimalsoft(Pty) Ltd8-Oct-09 1:31

Vimalsoft(Pty) Ltd

8-Oct-09 1:31

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.

Re: FIle Format Problems

Ashfield8-Oct-09 3:39

Ashfield

8-Oct-09 3:39

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 Smile | :)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: FIle Format Problems

Vimalsoft(Pty) Ltd8-Oct-09 4:00

Vimalsoft(Pty) Ltd

8-Oct-09 4:00

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

Smile | :)

Re: FIle Format Problems

Ashfield8-Oct-09 8:38

Ashfield

8-Oct-09 8:38

There are many ways to skin a cat - pleased you found one that worked for you Big Grin | :-D

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: FIle Format Problems

Vimalsoft(Pty) Ltd8-Oct-09 20:20

Vimalsoft(Pty) Ltd

8-Oct-09 20:20

Thank you

SQL Query Problem

Syed Shahid Hussain7-Oct-09 4:29

Syed Shahid Hussain

7-Oct-09 4:29

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]

Syed Shahid Hussain

Re: SQL Query Problem

dan!sh 7-Oct-09 4:53

dan!sh

7-Oct-09 4:53

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.