• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1207
  • Last Modified:

Need a Mysql persistent connection using ODBC

I have an application that connects to a mysql database over the web using ODBC.  It needs to have a persistent connection.  I am new to MySQL.  How do I tell the mysql server to keep the connection up once a connection is made?  I connect just fine, but when I try to do a select, the database has already closed.  I see PHP examples on the web, but I do not have the luxury of using PHP.  Please tell me how to accomplish this.  Thanks in advance.
0
FrankG77
Asked:
FrankG77
  • 7
  • 4
  • 2
  • +1
1 Solution
 
NeonDevilCommented:
pehaps you can connect with odbc_pconnect(dbname,user,pass)
0
 
FrankG77Author Commented:
Here is my connection string:

DRIVER={MySQL ODBC 3.51 Driver};SERVER=somewhere.com;PORT=3306;DATABASE=test; USER=test_user;PASSWORD=abc123;OPTION=3

What would I need to change in the above string in order to make the connection persistant?

Thanks
0
 
Marvin_CarredoCommented:
1. i need more info on this ... wats the language of that application that u are using?
2. or u may want to update your ODBC driver or maybe you can have a look at the ODBC Data Source Administrator
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Marvin_CarredoCommented:
or you might wanna change your option to 35 instead of 3
0
 
FrankG77Author Commented:
Tried option 35 ... got the same result...

I am a CTI (Computer Telephony Integration) developer.  The application that I am using routes calls.  It has a SQL plugin which allows me to connect to various databases using ODBC/ADO.  I do not have control over the code.  However, a configuration file is provided.  The string mentioned above is used to connect to the database  At the startup of the call routing application, a connection is made to the database using the connection string above.  When a call goes through the router, either a query or an stored procedure can be executed.  The problem is the connection is gone by the time a call goes through the router, as the connection to the database is not persistent.  Interestingly enough, this is not an issue with Oracle or M/S SQL.

Anyway, if I can either change the connection string (mentioned earlier) or change the ODBC source to build a persistent connection, I'm set.  I just don't know what is needed to accomplish this.

Please let me know if you need any further information.  Thanks again for your help.
0
 
FrankG77Author Commented:
I wonder if there is a way using the Mysql ODBC 3.51 driver of telling the connection to be persistent?  Any thoughts on this?
0
 
virmaiorCommented:
FrankG77, the string you are referencing is a ODBC connection string.  It allows you to connect to any DB system that your OS has drivers for.
this website provides some explanation of how the string works: http://www.activexperts.com/support/activmonitor/odbc/

you may wish to try adding:
PageTimeout=5000;

to your connection string (don't put any extra spaces in -- it's a finnicky interface).  
the value is in .10ths of seconds.  so 5000 would mean 500 seconds.  I don't know if setting it to 0 would make the connection persistent.
0
 
FrankG77Author Commented:
The pageTimeout didn't appear to help.
so ... I decided to try the DSN approach, instead of just using the driver.  Both approches time out in the same fashion.  Howerver, I'm wondering if there is a parameter I can put int the Mysql ODBC connector which will make the connection persistent?  I see on the web some other ODBC connectors have a check box for a persistent connection.  Any thoughts on this would be appreciated.  Thanks

If I'm real fast, I can call the router and it will work.  But if I wait very long (a minute), it times out.
0
 
virmaiorCommented:
the driver is the source of the timeout and using a DSN is the same as this approach, it just isn't formalized into a system DSN.

it might be a setting that needs to be changed on the MySQL server.  MySQL thinks it's doing your system resources a favor by automatically closing connection that are inactive.

I don't see any control for this anywhere... but maybe I'm just not looking at the right places
0
 
virmaiorCommented:
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Server_parameters.html
but you could try upping the
connect_timeout  

...

0
 
FrankG77Author Commented:
It sees like this would be a fairly common request.  It looks like it can be done with PHP ... but I can't use PHP in this instance.  Anyone else have any ideas?
0
 
FrankG77Author Commented:
I think I found it ... I just don't have the permissions to execute it ...

set global connect_timeout=-1

Your thoughts?
0
 
virmaiorCommented:
probably so.  if not -1 then something high, like 32767 (upper barrier of a signed 2-byte integer)
0
 
FrankG77Author Commented:
Well ... I installed my own instance of Mysql ... the default is obviously configured quite different from that of the ISV I use, as I am not getting the time out issue (it is working perfect). You worked pretty well with me on this and I appreciate your time.  full points - thanks.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now