SQLEXEC crashes VFP 1

[Claudio Costa]

My code:

STORE SQLCONNECT('WebBrowser', 'root', 'mysql') TO gnConnHandle

IF gnConnHandle <= 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
= MESSAGEBOX('Connection made', 48, 'SQL Connect Message')

SQLEXEC(gnConnHandle, 'SELECT * FROM artigos', 'mapa')
ENDIF

VFP crashes in the SQLEXEC line

Im using:

VFP 9
MySql ODBC 8.0 Unicode Driver.

Thanks.

 

[Steve Yu]

My code:

STORE SQLCONNECT('WebBrowser', 'root', 'mysql') TO gnConnHandle

IF gnConnHandle <= 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
= MESSAGEBOX('Connection made', 48, 'SQL Connect Message')

SQLEXEC(gnConnHandle, 'SELECT * FROM artigos', 'mapa')
ENDIF

VFP crashes in the SQLEXEC line

Im using:

VFP 9
MySql ODBC 8.0 Unicode Driver.

Thanks.

You can use aerror() function to see error messages:

lnresult = SQLEXEC(gnConnHandle, 'SELECT * FROM artigos', 'mapa')

if lnresult < 0
lnError = Aerror(laError)
*check error messages in array laError
else
*
endif

 

[Chriss Miller]

I assume with crashing you mean you get a C5 error and the VFP process really crashed. VFP isn't a Unicode application, except perhaps you're using VFPA. SYou say you use VFP9, so that's it, I think. You also get problems with the latest ANSI drivers, search VFP MySQL here in the forum and you'll see.

 

[Claudio Costa]

it crashs in the SQLEXEC(gnConnHandle, 'SELECT * FROM artigos', 'mapa') line so I cant get any error list

 

[Chriss Miller]

I searched myself and found the thread, where listed drivers they used and worked vs not worked:

Running SQLEXEC on MySQL Database -- Continued... - Microsoft: FoxPro

Hello All, In reviewing a prior thread (https://www.tek-tips.com/viewthread.cfm?qid=1829136), it seems as though I have a very similar dilemma. The primary difference is that I'd prefer not to utilize outdated ODBC connectors due to vulnerability concerns. I'll quickly recap below: MySQL...

www.tek-tips.com

You'll see I had questions about the specific construct of server and client, but that turned out to be okay, a Windows client using MySQL Connector/ODBC 8.0.33 works, using with a connection string with the Driver part "Driver={MySQL ODBC 8.0 ANSI Driver}"

A side topic there was Excel working with newer drivers, still, so the suspicion was it's not the drivers fault but VFP. And indeed that later drivers don't work with VFP has to do with VFPs ODBC layer, but the difference between (modern) Excel and VFP is Unicode vs ANSI more than anything else, and secondary the version of ODBC itself supported, ODBC in general not specific to any database.

So one way out to use more modern drivers, too, is to have an intermediate layer programmed in .NET, for example, and also using other drivers, like MariaDB drivers. I also talked about picking MariaDB as a server instead of MySQL, which even makes more sense, but I also think - not backed by experiments, but by compatibility intent of MariaDB development - that a more modern MariaDB driver could be used to access a MySQL server, not only a MariaDB server. Besides the 8.0.33 driver we're stuck with - as it seems - idoes not stop working with more recent Server versions.

From the other world of MSSQL I also know by experience that old MSSQL legacy ODBC drivers allwed me to use more modern MSSQL Server features, new T-SQL functions, for example. Becuase the ODBC driver is just a messanger between a client and the database engine on the server side. Only the VFP ODBC drivers you need to access VFP DBFs act as the server themselve and are not only a messnager, because there is no VFP server running queries. So in case of accessing any server database the only importance is whether the communication between VFPs client ODBC layer and the driver works and the communication between the ODBC driver and the remote database engine also works. Which means an overall later end of life.

If your major usage of VFP is as a client to database backends you're already clearly in the days of VFP needing legacy drivers the VFP ODBC layer can commnuicate with that are also capable to communicate with the more modernn SQL engines of the remote databases. I'm sure the next way out we'll need to use is putting the VFP ODBC layer to the rest and instead use other interop capabilities of VFP via OLE/COM or DECLARE DLL to use the ODBC layers of other runtimes, like that of C++ or .NET languages.

 

[Claudio Costa]

I dont understand.

SQLCONNECT('WebBrowser', 'root', 'mysql') TO gnConnHandle works , the connection is made it returns > 0

however

SQLEXEC(gnConnHandle, 'SELECT * FROM artigos', 'mapa') crashs VFP

 

[Chriss Miller]

By the way, the ANSI va UNICODE drivers are not catering for the encoding of the data, but Windows major distinction of processes using the Unicode vs ANSI aPI of Windows itself and secondary and related to that using ANSI (several code pages vs Unicode (UTF16 or UCS2) strings in the string parameters of functions. In terms of ODBC which is not only a protocol also the way ODBC commands themselves are encoded. So you can perfectly use MySQL data using Unicode or utf8mb3 or utf8mb4 encoding with the ANSI driver. VFP's UI controls would need strings converted to Ansi, then, but generating UTF8 HTML, for example, would show correctly in a Web Browser. The advantage of ANSI being mostly (except for a few Chinese/Japanse ANSI codepages) a 256 character encoding that uses single bytes for all its characters is, that all other encodings only have the disadvantage to be displayed wrongly in whatever ANSI codepage, but they stay the same bytes and so VFP as a transit layer from MYSQL to HTML, for example, works without a problem.

 

[Claudio Costa]

Just discover that QLEXEC(gnConnHandle,'INSERT INTO works fine.

Whats the problem with select * from ??

 

[Tore Bleken]

Too many fields?
One or more incompatible field types?
Illegal field names?
Just guessing....
Anyway, Select * should be avoided, it's better and faster to list only the necessary fields. If you use Thor, SuperBrowse can help you create the Select statement

 

[Claudio Costa]

Thank you Bleken !

I changed Select * From to Select just one field and it works .

Now I got to learn how to fill a grid with the sql result