MySQL string connection to FoxPro

[MSiddeek]

Hi,

when I tried to connect local / remote MySQL database from VFP using following string connection it returns -1

lnHandle=SQLSTRINGCONNECT("Driver={MySQL OBDC 8.0 Unicode Driver};Server=127.0.0.1;Database=w27;User=root;Password=")

but I can connect to both of these database using SQLCONNECT(MySQLOBDEC) DNS Connection created in "ODBC Data Sources (32-bit)"

I am using the same drive IP, Prot No, database, username, and the password in both occasion.

Please help me to solve this issue.

Note I am using Windows 10 pro.

MSiddeek

 

[Mike Lewis]

You presumably know how to use the Windows ODBC Admin tool. I assume you used that to create "MySQLOBDEC" in your second example.

So, just go to the File DSN tab, and follow the same steps. You will end up with a file containing the connection details. This is an ordinary text file. You can copy the information from that file and paste it directly into the SQLSTRINGCONNECT() parameter (remembering to add the surrounding quotes and to remove the CRLFs).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

After you get -1 call [highlight #FCE94F][tt]AERROR(laError)[/tt][/highlight] and inspect the error information in the debugger locals window (where you can drill down array elements) or simply output the array info with [highlight #FCE94F][tt]List Memory Like laError[/tt][/highlight].

One simple reason could be when you have set SQLSetProp(0,"DispLogin",3) you'd not get prompted for missing login information. Instead, it'll let this connection throw an error and you get -1. And that setting is also recommended, as you usually don't want to have an OS dialog pop up in your own UI. Similarly SQLSetProp("DispWarnings,.f.). IIRC these settings are even required on Windows Server 2012 (R2) as these system dialogs cause problems in the VFP process, so you better have all additional information available, ie user and password, usually or promp for them with your own login form and pass them into the other SQLCONNECT parameters (see into the VFP help).

Since any SQL Passthrough function doesn't trigger the CATCH or ON ERROR handling, as the error happens in another process (even on the server, sometimes) you have to get active and look and fetch is by AERROR after SQLConnect or SQLEXEC returns -1. You don't see that information, even if you have error handling established. This also means whenever you go for SQL...() functions and don't get the result you expect, check the information you get from AERROR(). Very general rule, notr only "of thumb".

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[MSiddeek]

Thanks Mike
Thanks Olaf

The problem is wording in the string, which should be UI instead of User. I found out this after checking the dsn file.

Olaf, I tried to catch the error with AERROR() but it did not throw anything.

MSiddeek.

 

[Olaf Doschke]

I don't know what you tried. not ON ERROR AERROR, but this:

lnHandle=SQLSTRINGCONNECT("Driver={MySQL OBDC 8.0 Unicode Driver};Server=127.0.0.1;Database=w27;User=root;Password=")
If lnHandle<0
   AERROR(laError)
   LIST MEMORY LIKE laError
   set step on
ENDIF

As said, there is no error thrown, you won't get triggered general error handling nor go into a CATCH block, you have to check IF lnHandle<0 and then read the error. You also have to do it then, after it happened, not beforehand.

When SQL(STRING)CONNECT return value sets lnHandle<0 you have guaranteed error information of any kind you retrieve with AERROR().

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name