FoxPro and MySQL field size does not match

Hi everybody,

I am connecting via ODBC and sqlstringconnect to a MySQL server. Let's
assum I have a numeric field named my_id with length 3 defined in the
MySQL table "test_table". After connecting and doing a SQLExec "select
my_id from test_table" the resulting FoxPro table will have a field
length of 11 for the field my_id. That causes problems when displaying
the no in a combo box. What's wrong here?

Thanks for your help
Andi

Man-wai
Fri Jun 02 03:37:39 CDT 2006

andipfaff wrote:
> Hi everybody,
>
> I am connecting via ODBC and sqlstringconnect to a MySQL server. Let's
> assum I have a numeric field named my_id with length 3 defined in the
> MySQL table "test_table". After connecting and doing a SQLExec "select
> my_id from test_table" the resulting FoxPro table will have a field
> length of 11 for the field my_id. That causes problems when displaying
> the no in a combo box. What's wrong here?

hmm... shouldn't you use MyODBC?

--
.~. Might, Courage, Vision, SINCERITY. http://www.linux-sxs.org
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Ubuntu 5.10) Linux 2.6.16.18
^ ^ 16:37:01 up 10 days 1:37 0 users load average: 1.91 1.58 1.56
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Bernhard
Fri Jun 02 04:00:53 CDT 2006

Hi Andi,

> I am connecting via ODBC and sqlstringconnect to a MySQL server. Let's
> assum I have a numeric field named my_id with length 3 defined in the
> MySQL table "test_table". After connecting and doing a SQLExec "select
> my_id from test_table" the resulting FoxPro table will have a field
> length of 11 for the field my_id. That causes problems when displaying
> the no in a combo box. What's wrong here?
In MySQL there are some variations of datatypes that don't have an exact
matching type in Foxpro. So it is important, if you tell exactly which type you
use in MySQL.

Regards
Bernhard Sander

AA
Fri Jun 02 03:46:06 CDT 2006

If you have a remote view you can use DBSETPROP() with the DataType property
to change the field to Int to to Numeric(3).
-Anders

"andipfaff" <andreas.pfaff@swissonline.ch> skrev i meddelandet
news:1149236585.588439.201420@c74g2000cwc.googlegroups.com...
> Hi everybody,
>
> I am connecting via ODBC and sqlstringconnect to a MySQL server. Let's
> assum I have a numeric field named my_id with length 3 defined in the
> MySQL table "test_table". After connecting and doing a SQLExec "select
> my_id from test_table" the resulting FoxPro table will have a field
> length of 11 for the field my_id. That causes problems when displaying
> the no in a combo box. What's wrong here?
>
> Thanks for your help
> Andi
>

andipfaff
Fri Jun 02 06:51:33 CDT 2006

Hi,

I am using MyODBC (of cause), FVP 8.0, MySQL server 4.1. The type of
field (an example) ist "int(3) unsigned". Connecting to the server,
doing the "select * from my_table" and browsing in the resulting FoxPro
table will allow me to enter 11 digits in that field.


Bernhard Sander schrieb:

> Hi Andi,
>
> > I am connecting via ODBC and sqlstringconnect to a MySQL server. Let's
> > assum I have a numeric field named my_id with length 3 defined in the
> > MySQL table "test_table". After connecting and doing a SQLExec "select
> > my_id from test_table" the resulting FoxPro table will have a field
> > length of 11 for the field my_id. That causes problems when displaying
> > the no in a combo box. What's wrong here?
> In MySQL there are some variations of datatypes that don't have an exact
> matching type in Foxpro. So it is important, if you tell exactly which type you
> use in MySQL.
>
> Regards
> Bernhard Sander

Bernhard
Fri Jun 02 08:24:56 CDT 2006

Hi Andi

> I am using MyODBC (of cause), FVP 8.0, MySQL server 4.1. The type of
> field (an example) ist "int(3) unsigned". Connecting to the server,
> doing the "select * from my_table" and browsing in the resulting FoxPro
> table will allow me to enter 11 digits in that field.
There is no foxpro typ INT(3) unsigned. In MySQL help you can read, that the
size (3) is purely cosmetic, and MySQL stores an 4-byte Integer. You also could
enter some values into this field that have more places than the defined 3.
The closest type that foxpro has is the 4-byte integer which stores about 11
decimal digits.

If you really want 3 and only 3 digits to be stored, then use MySQL-Type
Decimal(3) which should lead to N(3, 0) in foxpro.
In a field of this type, also in MySQL, you could store negative values. This is
maybe not what you want.
But in Foxpro there exists no unsigned numeric type. So you have to check this
anyway by some lines of code, maybe some trigger.

Regards
Bernhard Sander

andipfaff
Fri Jun 02 09:18:04 CDT 2006

Hi Berhnard

in the meantime I've checked that out with the different types with the
results you have mentioned. Unfortunately I will have another problem
with Decimal(3): I will geht a 112,00000 or so instead of the no 112,
so there i change one problem against another.

As I have that problem only when using the values of that table in a
combo box wher I cannot format the data I can live with it.

Bernhard Sander schrieb:

> Hi Andi
>
> > I am using MyODBC (of cause), FVP 8.0, MySQL server 4.1. The type of
> > field (an example) ist "int(3) unsigned". Connecting to the server,
> > doing the "select * from my_table" and browsing in the resulting FoxPro
> > table will allow me to enter 11 digits in that field.
> There is no foxpro typ INT(3) unsigned. In MySQL help you can read, that the
> size (3) is purely cosmetic, and MySQL stores an 4-byte Integer. You also could
> enter some values into this field that have more places than the defined 3.
> The closest type that foxpro has is the 4-byte integer which stores about 11
> decimal digits.
>
> If you really want 3 and only 3 digits to be stored, then use MySQL-Type
> Decimal(3) which should lead to N(3, 0) in foxpro.
> In a field of this type, also in MySQL, you could store negative values. This is
> maybe not what you want.
> But in Foxpro there exists no unsigned numeric type. So you have to check this
> anyway by some lines of code, maybe some trigger.
>
> Regards
> Bernhard Sander

Imaginecorp
Sat Jun 03 20:50:56 CDT 2006

Lookup the new CAST() function, It seems to do what you want. Never tried
with a call to SQL server but on native vfp tables it works like a charm
Try
Select CAST(test_table.my_id as N(3,0)) from test_table
Mohammed

"andipfaff" <andreas.pfaff@swissonline.ch> wrote in message
news:1149236585.588439.201420@c74g2000cwc.googlegroups.com...
> Hi everybody,
>
> I am connecting via ODBC and sqlstringconnect to a MySQL server. Let's
> assum I have a numeric field named my_id with length 3 defined in the
> MySQL table "test_table". After connecting and doing a SQLExec "select
> my_id from test_table" the resulting FoxPro table will have a field
> length of 11 for the field my_id. That causes problems when displaying
> the no in a combo box. What's wrong here?
>
> Thanks for your help
> Andi
>