Query returning memo fields instead of strings ( MariaDB )

[Claudio Costa]

Hi again.

Im using an MariaDb database with VFP but when doing a select the query is returning memo fields instead of strings.
The numeric fields are returned ok.
So , whats going on ?

 

[Joe Crescenzi]

You'll get similar results with other ODBC queries, including XLS/XLSX drivers.

The easiest solution is to implicitly cast each column. For Remote Views, you'll only need to do this once when you define the view.

 

[zazzi]

You can use CAST() to force the output field type e.g. in SQL passthrough:

SELECT CAST(field_returned_as_memo AS VARCHAR(100)) field_returned_as_varchar from MyTable

 

[Claudio Costa]

And where I put the SELECT CAST line ?

in the SQLEXEC ?

sorry , im a newby in MySql / MariaDB.

 

[Chriss Miller]

Maximum for a VFP DFB char field is 254 characters, so anything longer is turned into a memo. So if your MariaDB is overdimensioned in these fields, shorten them. If that's okay with the data, the memo field type is what you'll need and want and there's nothing wrong about that.

 

[Chriss Miller]

In general to have control about the result field types on the VFP side it's most elegant to use remote views or cursoradapter instead of SQLEXEC.

Anyway, if your MariaDB column is shorter than 255 it will arrive as char or varchar in VFP with no need to CAST, if you need to CAST to get a char/varchar instead of a memo field, you risk the case that strings are cut off. To lLive with Memo is the better advice. Or, if all data in the MariaDB table could also be stored in a shorter field, shorten it there, not while selecting the data.

 

[Claudio Costa]

Hello.

the fields size in MariaDb are 20 and 50 characters , so they should appear ok in VFP , right ?

Edit : I changed the fields in MariaDB to char type and now its working ok !

 

[Chriss Miller]

Still not clear what you had. If it was varchar(20) and varchar(50) you have to do CursorSetProp("MapVarchar",.T.,0) and you should also have CursorSetProp("MapBinary",.T.,0). Before doing any query, once at the start of a datasession.

Anyway, memo does no harm, you can bind both a textbox and an editbox to a memo field and it will display the text, not "Memo", that's only happening in a Browse window. So I question your problem and your Fox knowledge.

 

[Claudio Costa]

Im with Fox for several years , but Im newby with MySql, MariaDB.

I was using tinytext on text fields, now that Ive changed to char is returning ok.

Thanks everybody for your help and patience.

 

[Chriss Miller]

Makes me wonder why would anyone still use tinytext, which is limited to 256 bytes, so it's just like varchar(256) and it will turn to memo, because 256>254, as simple as that. And once you look up what tinytext in a MySQL or MariaDB can store, you know how it doesn't compare to simple char/varchar field. The rule is simple, any character type field of length 255 or higher maps to memo on the VFP side, as the limit in VFP is 254.

You also shouldn't get confident about changing to char changes to char in VFP always, too. It is just depending on the length limits. Again, this is getting clear once you know VFPs limit of char/varchar lengths more than anything else. MySQL/MariaDB does support char/varchar(255) and longer and they also will turn to memo.

You talked about 20 and 50 character fields, it's getting insanely evasive by coming up with more. I understand your problem is solved, fine. Next time you ask a question, please provide enough details to enable answering it.