DB questions

Just starting out? Need help? Post your questions and find answers here.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

DB questions

Post by captain_skank »

Hi all,

I converting one of my apps from using mysql via odbc to postgres (cos handling the ODBC is a p.i.t.a ) :)

Everything works as it should, but it's made me think of a few things and wonder what should be best practice.

1. Should a connection be opened at the start and closed at the end of the program or opened and closed after every operation ? is there a performance hit.

2. My app is so large that i decided to break it into smaller programs that are called from the main program ( using runprogram ) - is there a way to carry an open database connection over between programs ?

3. is there any way of secruring the connection to the db other than using ssh ( which is also a p.i.t.a ) ?

Any thoughts ?

Cheers
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

Re: DB questions

Post by RSBasic »

captain_skank wrote:1. Should a connection be opened at the start and closed at the end of the program or opened and closed after every operation ? is there a performance hit.
If you want to execute many SQL queries in a short time, leave the SQL connection open, otherwise it will be very slow if you always establish and close the connection.
captain_skank wrote:2. My app is so large that i decided to break it into smaller programs that are called from the main program ( using runprogram ) - is there a way to carry an open database connection over between programs ?
Maybe with Shared Memory?
Image
Image
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: DB questions

Post by captain_skank »

Thanks for the reply.
If you want to execute many SQL queries in a short time, leave the SQL connection open, otherwise it will be very slow if you always establish and close the connection.
Of course - but is there any advantage/disadvantage in opening and closing connections at need rather than leaving a connection open throughout the operation of the program even though it may not be in use for 70% of the time ?
Maybe with Shared Memory?
Oooh that's a bit above my pay grade :) - how would that work with an open db connection ?

Again, thanks for the info.

cheers
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: DB questions

Post by Marc56us »

In the case of a multi-user application, it is always preferable to close the connection as soon as possible after a SQL query sequence.
Indeed, for each open connection, the server blocks a certain amount of RAM.
In general we limit the number of concurrent connections on a server so as not to saturate the RAM.
If the application crashes before manual disconnection, it will be necessary to wait for the timeout for the more active connections to be closed.
On a normal network, opening is almost instantaneous, so close and open on demand and keep the connection active only for a sequence of requests.
:wink:
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: DB questions

Post by captain_skank »

Marc56us wrote:In the case of a multi-user application, it is always preferable to close the connection as soon as possible after a SQL query sequence.
Indeed, for each open connection, the server blocks a certain amount of RAM.
In general we limit the number of concurrent connections on a server so as not to saturate the RAM.
If the application crashes before manual disconnection, it will be necessary to wait for the timeout for the more active connections to be closed.
On a normal network, opening is almost instantaneous, so close and open on demand and keep the connection active only for a sequence of requests.
:wink:
I'd never considered the preallocation of RAM on the server ( mainly becasue the server I run this on has RAM to burn ).

Also by opening and closing the connection as required makes my life easier in the long run now I've thought about it a little more - so I think i'm going with this option at the moment.

Thanks for the input / advice.

Now all i need to do is secure the communication to the server and all's good.

Cheers
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: DB questions

Post by infratec »

For that reason I wrote a PostgreSQL_SSL_Proxy

At the moment it uses the gnutls dlls to do the ssl stuff.
But it is only 99.5% stable and ... I'm not allowed to share it.

As soon as I start this Proxy I can connect via localhost:5432 to the database.
The Proxy is a pbi file which creates a thread for handling the in/out packages.

Since gnutls are more then 1 dll and it don't want to tell me how many data is available,
I want to switch to libssl or cryptlib. But up to now with no success.
If everything is working fine, we offer this pbi as a 'product'.

a small snippet:

Code: Select all

Structure PostgreSQL_SSLProxy_ParameterStructure
  PostgresServer$
  PostgresPort.i
  LocalPort.i
  Con.i
  Thread.i
  Connected.i
  Exit.i
EndStructure

If SSLWanted
  If PostgreSQL_SSLProxyStart(@SSLProxyParameter)
    DB = OpenDatabase(#PB_Any, "host=127.0.0.1 port=" + Str(SSLProxyParameter\LocalPort) + " dbname="+ DBName$, DBUser$, DBPassword$, #PB_Database_PostgreSQL)
    If DB = 0
      MessageRequester("Error", Str(#PB_Compiler_Line) + ": Not able to open SSL database: " + DBName$)
      End
    EndIf
  Else
    MessageRequester("Error", Str(#PB_Compiler_Line) + ": SSL connection failed: " + SSLProxyParameter\PostgresServer$)
    End
  EndIf
Else
  DB = OpenDatabase(#PB_Any, "host=" + DBServer$ + " port=5432 dbname="+ DBName$, DBUser$, DBPassword$, #PB_Database_PostgreSQL)
  If DB = 0
    MessageRequester("Error", Str(#PB_Compiler_Line) + ": Not able to open database: " + DBName$)
    End
  EndIf
EndIf

Or Fred add a lot of ssl stuff and it is no longer needed to write something arround.

Bernd
Post Reply