MySQL time/date value gets gobbled up

Hi all, 

I have been working at this for a very long time.  I have had no success in
querying a certain table in my mysql database.  One table I can access fine -
there are no special "primary" keys set up in it and no
"NULL" values in it.  

The older table I have been able to query fine and convert to a .dbf file.  The
mysql format for that table is this:

1)datetime    Datatype: DATETIME  not null    default value 0000-00-00 00:00:00
2)id      Datatype:  TINYINT(4)  not null   flags: unsigned, zerofill  default
value: 0
3) data    Datatype:  FLOAT    flags: unsigned, zerofill  default value: NULL
....25) same as number 3


The other table that I need to access causes problems in dbase plus.  This table
was changed to a different format and for some reason dbase isn't handling it
very well.    I get an "Error: Bad field type".   What causes this? 
the mysql table I am trying to access does have "NULL" fields and
contains 2 primary keys:  a date/time and location number. I have been able to
access it once or twice, but it is all jarbled... the date/time fields come up
as  02/03/2278 BC 00:00:00

The newer table in mysql  contains the following:

1)primary key date/time   Datatype=datetime  not null
2)primary key    id           Datatype= CHAR(10)   not null   Flags: binary,
ascii, unicode
3)id      Datatype=CHAR(10)     Flags:  binary, ascii, unicode
4)data   Datatype=DOUBLE     Flags: unsigned, zerofill  default: NULL
...
15)data  Datatype=INTEGER  Flags:  unsigned, zerofill default: -1
...
30)updatedatetime   Datatype=TIMESTAMP  not null    default value:  CURRENT_INFO
    

has anyone ever encountered this?

"D Noble" wrote
>
> The other table that I need to access causes problems in dbase plus.  This

> table was changed to a different format and for some reason dbase isn't 
> handling it very well.    I get an "Error: Bad field type".  
What causes 
> this?  the mysql table I am trying to access does have "NULL"
fields and 
> contains 2 primary keys:  a date/time and location number. I have been 
> able to access it once or twice, but it is all jarbled... the date/time 
> fields come up as  02/03/2278 BC 00:00:00
>
> The newer table in mysql  contains the following:
>
> 1)primary key date/time   Datatype=datetime  not null
> 2)primary key    id           Datatype= CHAR(10)   not null   Flags: 
> binary, ascii, unicode
> 3)id      Datatype=CHAR(10)     Flags:  binary, ascii, unicode
-------
Looks like Unicode is the problem. dBASE cannot deal with unicode directly.

Try to convert the data with the CAST function:

SELECT CAST(id AS Character) AS ID, ...

Roland

In article <C1i58vwiIHA.1104@news-server>, davidweather1976@yahoo.com 
says...
> Hi all, 
> 
> I have been working at this for a very long time.  I have had no success in
querying a certain table in my mysql database.  One table I can access fine -
there are no special "primary" keys set up in it and no
"NULL" values in 
it.  
> 
> The older table I have been able to query fine and convert to a .dbf file. 
The mysql format for that table is this:
> 
> 1)datetime    Datatype: DATETIME  not null    default value 0000-00-00
00:00:00
> 2)id      Datatype:  TINYINT(4)  not null   flags: unsigned, zerofill 
default value: 0
> 3) data    Datatype:  FLOAT    flags: unsigned, zerofill  default value:
NULL
> ....25) same as number 3
> 
> 
> The other table that I need to access causes problems in dbase plus.  This
table was changed to a different format and for some reason dbase isn't handling
it very well.    I get an "Error: Bad field type".   What causes this?
 
the mysql table I am trying to access does have "NULL" fields and
contains 2 primary keys:  a date/time and location number. I have been able to
access it once or twice, but it is all jarbled... the date/time fields come up
as  
02/03/2278 BC 00:00:00
> 
> The newer table in mysql  contains the following:
> 
> 1)primary key date/time   Datatype=datetime  not null
> 2)primary key    id           Datatype= CHAR(10)   not null   Flags:
binary, ascii, unicode
> 3)id      Datatype=CHAR(10)     Flags:  binary, ascii, unicode
> 4)data   Datatype=DOUBLE     Flags: unsigned, zerofill  default: NULL
> ...
> 15)data  Datatype=INTEGER  Flags:  unsigned, zerofill default: -1
> ...
> 30)updatedatetime   Datatype=TIMESTAMP  not null    default value: 
CURRENT_INFO     
> 
> has anyone ever encountered this?
> d


David,

Which version of dBASE are you using? Which version of MySQL? Which ODBC 
driver? Which MySQL "engine"? What kind of dBASE code are you using to

access the table?

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

A couple of puzzling things about your question:

1. You say you have 2 primary keys in the MySQL table.  Two separate keys 
are impossible,  Do you mean you have a primary index composed of those 2 
keys or two fields that have separate indexes?
> 1)primary key date/time   Datatype=datetime  not null
> 2)primary key    id           Datatype= CHAR(10)   not null   Flags: 
> binary, ascii, unicode

2. Your description of  the subsequent field attributes you indicate that 
for example id is both ascii and unicode.  That is not possible. It could be 
designated as one or the other but not both.  Are you using the MySQL Table 
Editor?  If so are you indicating that the checkboxes for both ascii and 
unic are both checked?

3. You say for your integer field it is both unsigned and zerofill with a 
default value of -1.  That is not permitted.  Are you sure the that is the 
description?  Again if you are working with the Table Editor are the 
checkboxes checked or unchecked?

Also can you tell us which storage Engine you are using MyISAM or InnoDB 
etc.?

Is it fair to assume that you are converting to dBase level 7 table format, 
since you are working with a timeStamp? As timeStamp is not available in 
level 5 tables.

Have you checked the "Data type mappings for CREATE TABLE" in the help
file 
for field conversions? Search index on "mappings" and select
"data type 
(local SQL)" for field conversions.  This may help you to create a reverse

datapump similar to the "dbf to MySQL data pump v 1.5"  I created to
move 
the data from dBase to MySQL with proper field conversion.  You can view the 
source code in the latests version of dUFLP or find it in the binaries news 
group.


Initally try to limit the columns you import to eliminate the error.  Then 
do a Roland suggest to try to change the data format of the troubling 
fields.

Claus