Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

VFP can connect to MySQL, yes?

Hi all. Have a form where I enter the connection details for mysql. Everything works great and I can query the db fine.

I will have a need from time to time to connect to a different db and I was wondering if there is a simple way to verify that I can actually connect to the db after entering new connection details - a 'test connection' button that will execute a simple query and return 1 if connected, 0 if the connection failed - something like that.

I just need the query details.
0
formadmirer
Asked:
formadmirer
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
peterhuppCommented:
I use the code below to test the connection.  The first time it runs it will create a temporary procedure #testconnection which it will subsequently call and return a 1 if the connection exists.  Very quick and lightweight.  I have only tested this with MSSQL, but should work with mysql.

 
 LOCAL llError, TestReturnValue
  llError = .f.
  TestReturnValue = 0
  try
	  IF SQLEXEC(m.lnTestHandle,"exec #testconnection ?@TestReturnValue")<1 ;
	     AND SQLEXEC(m.lnTestHandle,"create procedure #testconnection @rtnvalue bit output as set @rtnvalue=1")<1   
	     llError = .t.
	  ENDIF
  CATCH
     llError = .t.
  endtry
  IF m.llError
    llReturnValue = .f.
  ENDIF

Open in new window

0
 
CaptainCyrilCommented:
lcServer="localhost"   && IP Address of server
lcDatabase="tabs"
lcUser = "tabs"
lcPassword = "mypassword"
lcStringConn="Driver={MySQL ODBC 3.51 Driver}"+;
             ";Server="+lcServer+;
             ";Database="+lcDatabase+;
             ";Uid="+lcUser+;
             ";Pwd="+lcPassWord

lcStringConn="Driver={MySQL ODBC 5.1 Driver}"+;
             ";Server="+lcServer+;
             ";Database="+lcDatabase+;
             ";Uid="+lcUser+;
             ";Pwd="+lcPassWord

*-- Don't prompt for login
SQLSETPROP(0,"DispLogin",3)
lnHandle=SQLSTRINGCONNECT(lcStringConn)

IF lnHandle  > 0
         *-- open connection, get the data, then close the connection
         
         lcSQL = "SELECT * FROM survey"
         
         SQLEXEC(lnHandle, lcSQL ,"crTemp")
         SQLDISCONNECT(lnHandle)
         
         SELECT crTemp
         LOCATE
         BROWSE NORMAL NOWAIT
      
ELSE
         =AERROR(laError)
         MESSAGEBOX("Error at Connecting"+CHR(13)+;
               "Description:"+laError[2])
ENDIF
0
 
formadmirerAuthor Commented:
I'm splitting the points between you two.
peterhupp I wasn't able to get your code to work with MySQL, could be a difference between it and MS SQL, I don't know, but had it worked your code was what I was looking for.

CaptainCyril, I already have a working connection, I was just looking for a simple 1 or 0 if I was connected successfully or not. But, that's good code so you've got to get some points for it.

Anyway, I found out that by simply sending
lcQuery = "SELECT VERSION()"
into my connection string I can easily verify if I have successfully connected or not, which is what I wound up using. Thanks though for the help.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
peterhuppCommented:
the reason why I used a stored procedure is that it returns a value instead of a cursor.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Actually you don't need to test. SQLStringConnect will tell you if it succeeded connecting. So Cyrils code is totally sufficient. If the database doesn't exist or is shut down you will not get a connection, if you have a connection, you have a connection.

Bye, Olaf.
0
 
CaptainCyrilCommented:
You get a failed/successful return on each call. It could be that the database is not there, server is down or whatever. Also when you run a query there could be a bug in the syntax. VFP can return you the error in details if you wish or if you are satisfied with True/False it can do that as well.
0
 
Olaf DoschkeSoftware DeveloperCommented:
As a side note: Most any other Database also allows a syntax of SELECT expression without any FROM clause. This looks a bit like VFPs SELECT command to select a workarea, but that' VFP exclusive. What it does is, it returns a scalar value in a single record cursor to VFP.

This way you can also get Server system time for example in T-SQL (MS SQL SERVER) with SELECT Getdate() and in MySQL you can get it via SELECT NOW(),CURDATE(),CURTIME()
You can also SELECT VERSION() in MySQL to get it's version number.

Bye, Olaf.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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