dBASE Web Applications - Creating MySql Tables from dBASE

Author

Creating MySql Tables from dBASE

Claus Mygind

2005-08-15, 8:23 pm

I've installed MySql 4.1.13 with the MyOBDC-3.51.11-2 drivers. I noticed
when I want to create a SQL table from dBASE, there is not a complete list
of field types available such as "Date" fields. Is the general practice
when creating SQL tables to create them on the SQL server? What's the best
option?

I also noticed that when I create a table on the server I cannot modify that
structure in dBASE.

David Ball

2005-08-16, 8:23 pm

Hello Claus

> Is the general practice
> when creating SQL tables to create them on the SQL server? What's the
best
> option?

In my limited experience, I've found the MySQL Control Centre to be more
stable than the dBASE Designer (and has better 'features', as you've
discovered). I am also exploring the MySQL Query Browser, which if I recall
correctly, is supposed to replace the Control Centre.

> I also noticed that when I create a table on the server I cannot modify
that
> structure in dBASE.

Currently, I use the MySQL tools for design and desktop browsing/editing,
and dBASE (or php) for web access. Desktop browsing / navigation with dBASE
appears to cause instability, but if I use a where clause to return 1 rowset
at a time, it does not cause me problems.

Still on the learning curve,
David Ball
www.ChelseaData.ca

Claus Mygind

2005-08-19, 1:23 pm

David,
Thanks.
Can you give some info on downloading the needed software to use MySQL
Tools?
Is it MySQL Administrator?

Also can you give some tips on importing "Date" fields? I seem to have some
problems here.

"David Ball" <Thanks.But@NoSpam.4.me> wrote in message
news:L8hjfMroFHA.320@news-server...
> Hello Claus
>
> best
>
> In my limited experience, I've found the MySQL Control Centre to be more
> stable than the dBASE Designer (and has better 'features', as you've
> discovered). I am also exploring the MySQL Query Browser, which if I
recall
> correctly, is supposed to replace the Control Centre.
>
> that
>
> Currently, I use the MySQL tools for design and desktop browsing/editing,
> and dBASE (or php) for web access. Desktop browsing / navigation with
dBASE

> appears to cause instability, but if I use a where clause to return 1
rowset
> at a time, it does not cause me problems.
>
> Still on the learning curve,
> David Ball
> www.ChelseaData.ca
>
>

Claus Mygind

2005-08-19, 1:23 pm

I downloaded the Administrator. That seems to have what you are talking
about as wells as other tools to manager the server.

Still trying to figure out how to copy dates from a .dbf table to a SQL
table? Any help will be appreciated.

Also suggestions on any good reference books in addition to the online
reference manual?

"Claus Mygind" <cmygind@tsccorp.com> wrote in message
news:MSptWzMpFHA.1484@news-server...
> David,
> Thanks.
> Can you give some info on downloading the needed software to use MySQL
> Tools?
> Is it MySQL Administrator?
>
> Also can you give some tips on importing "Date" fields? I seem to have
some
> problems here.
>
> "David Ball" <Thanks.But@NoSpam.4.me> wrote in message
> news:L8hjfMroFHA.320@news-server...
> recall
modify[color=darkred
]
browsing/ editing,[color=darkr
ed]
> dBASE
> rowset
>
>

David Ball

2005-08-20, 8:23 pm

Hello Claus

"Claus Mygind" <cmygind@tsccorp.com> wrote in message
news:9L1m6HNpFHA.328@news-server...
> I downloaded the Administrator. That seems to have what you are talking
> about as wells as other tools to manager the server.

The old "Control Center" did both server administration and allowed table
browsing. An all-in-one sort of thing. More recently, it has been divided
into 2 products - MySQL Administrator and MySQL Query Browser - to
specialize in different things, I guess. According to the manual chapter
8.6:

mysqlcc is deprecated and it is recommended that users choose the new MySQL
Administrator and MySQL Query Browser, found at
http://dev.mysql.com/downloads/.

I haven't used the Administrator myself, so I can't help with deciding which
is better at what tasks. My cynical side would say that the commercial mySQL
company finds it beneficial to make things as vague and obscure as possible,
to maximize support income from people who don't have time or inclination to
spend on the dense documentation. In my experience, I have been able to get
things to work by some means or other, and I am grateful that they do have
such complete documentation.

> Still trying to figure out how to copy dates from a .dbf table to a SQL
> table? Any help will be appreciated.

<several hours later...>

I was able to do it !

FIRST:
In mySQL, I had to tell it to enclose fields with " instead of '. I did this
in Control Centre; I couldn't find it in Query Browser. I am not using
Administrator so you're on your own for that. How did I figure that out?
Well, that's what the several hours was... trial and error. I tried doing
things several different ways (see the reference section below the
signature) and I succeeded but I didn't know why. I had to shut everything
down, reboot and start from scratch to find out where the secret ingredient
was. It turned out to be the field delimiter that I had set based on a user
comment showing how to use mysqlimport (which I didn't need to use in the
end).

SECOND:
In dBASE:
use :FB:games05.dbf // a dBASE level 7 table
copy to :my14:games05 // mySQL database table

THIRD:
Return to mySQL, refresh the view and see what the target field contains. It
should be varchar(10).
If you see varchar(8), then you still have a problem that I can't help you
with. I could not continue until I got varchar(10). Varchar(8) will not
contain complete dates; varchar(10) should contain formatted yyyymmdd string
data. Go to the field editor (right click on table name and select Edit) and
change the field type to date - double click on varchar(10) and type date.
It REALLY wants to set it to datetime for some reason, so delete the time
portion and press enter. You will get a confirmation dialog box with the SQL
command:
ALTER TABLE `test`.`games2` MODIFY COLUMN `Dat_` DATE NOT NULL;
Press Execute then Close on the next window.
In the main window, press the Execute button at the top to refresh the
browse window. You should now be seeing properly formatted mySQL dates !

> Also suggestions on any good reference books in addition to the online
> reference manual?

I'm still overwhelmed by the online stuff. I've found the user comments to
be useful at times.

Good luck
David Ball
www.ChelseaData.ca

<Stuff I wrote during the composition of this reply - I left it here for
reference purposes>

I found this in the user comments:
--------------------- Posted by Philippe Jausions on February 4 2005 4:57pm

If you are one of the many people trying to import a CSV file into MySQL
using mysqlimport under MS-Windows command/DOS prompt, try the following:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --l
ines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE
YOUR_TABLE.csv
---------------------

so I tried copying the dbase table to a csv file and using some of these
options. While I was doing this, I thought I might as well try the direct
route - change the field type and see what happens. I noticed that the table
copy was producing slightly different results, but I don't know why. In my
first import, the dBASE date field type became varchar(8) and many of the
rows contained nulls, although all of the source fields were full. In the
later imports, the field type was varchar(10) and all of the field values
were imported properly. I would suggest checking your imported table in
mySQL to ensure that you are getting the complete field contents (even if
they are in the wrong format). Perhaps some of the option settings in the
mySQL import changed something ... ? I will keep trying to figure out what
specific change made this dBASE copy operation work differently on
subsequent attempts. (That's what I did in the final reply)

In the Query Browser, changing field type is so difficult - you have to
actually type it in.<g> I closed Query Browser and went back to the Control
Center where you get a listbox. I changed the field type and went to look
at it with the browse and ... success! All dBASE dates converted to mySQL
date type.

Claus Mygind

2005-08-22, 9:23 am

David,
Thanks this info is alot of help. I installed the Administrator and It
works fine. Acutally it makes things alot clearer. I will have to look for
the Query tool.

I did find a conversion tool on the web from www.convert-in.com/dbf2sql.htm
.. You can download a demo for free. Works like the real thing but will
only copy first 5 records. But you can use to just create and empty file
structure which you can modify before using dBASE to append the records. It
does a decent job but logic fields are converted to enumerated fields enmu
'T,F". But dates and memo fields are brought in just fine. Won't work with
dbf version 7 tables.

With regards to the Date field, MySQL does have a type "Date" field. With
it you can use the date functions Year( ) and Month( ) etc. and the
converter does create that type of field.

"David Ball" <Thanks.But@NoSpam.4.me> wrote in message
news:uYh9cAcpFHA.1040@news-server...
> Hello Claus
>
> "Claus Mygind" <cmygind@tsccorp.com> wrote in message
> news:9L1m6HNpFHA.328@news-server...
>
> The old "Control Center" did both server administration and allowed table
> browsing. An all-in-one sort of thing. More recently, it has been divided
> into 2 products - MySQL Administrator and MySQL Query Browser - to
> specialize in different things, I guess. According to the manual chapter
> 8.6:
>
> mysqlcc is deprecated and it is recommended that users choose the new
MySQL

> Administrator and MySQL Query Browser, found at
> http://dev.mysql.com/downloads/.
>
> I haven't used the Administrator myself, so I can't help with deciding
which

> is better at what tasks. My cynical side would say that the commercial
mySQL

> company finds it beneficial to make things as vague and obscure as
possible,
> to maximize support income from people who don't have time or inclination
to
> spend on the dense documentation. In my experience, I have been able to
get
> things to work by some means or other, and I am grateful that they do have
> such complete documentation.
>
>
> <several hours later...>
>
> I was able to do it !
>
> FIRST:
> In mySQL, I had to tell it to enclose fields with " instead of '. I did
this
> in Control Centre; I couldn't find it in Query Browser. I am not using
> Administrator so you're on your own for that. How did I figure that out?
> Well, that's what the several hours was... trial and error. I tried doing
> things several different ways (see the reference section below the
> signature) and I succeeded but I didn't know why. I had to shut everything
> down, reboot and start from scratch to find out where the secret
ingredient
> was. It turned out to be the field delimiter that I had set based on a
user
> comment showing how to use mysqlimport (which I didn't need to use in the
> end).
>
> SECOND:
> In dBASE:
> use :FB:games05.dbf // a dBASE level 7 table
> copy to :my14:games05 // mySQL database table
>
> THIRD:
> Return to mySQL, refresh the view and see what the target field contains.
It
> should be varchar(10).
> If you see varchar(8), then you still have a problem that I can't help you
> with. I could not continue until I got varchar(10). Varchar(8) will not
> contain complete dates; varchar(10) should contain formatted yyyymmdd
string
> data. Go to the field editor (right click on table name and select Edit)
and
> change the field type to date - double click on varchar(10) and type date.
> It REALLY wants to set it to datetime for some reason, so delete the time
> portion and press enter. You will get a confirmation dialog box with the
SQL
> command:
> ALTER TABLE `test`.`games2` MODIFY COLUMN `Dat_` DATE NOT NULL;
> Press Execute then Close on the next window.
> In the main window, press the Execute button at the top to refresh the
> browse window. You should now be seeing properly formatted mySQL dates !
>
>
> I'm still overwhelmed by the online stuff. I've found the user comments to
> be useful at times.
>
> Good luck
> David Ball
> www.ChelseaData.ca
>
>
> <Stuff I wrote during the composition of this reply - I left it here for
> reference purposes>
>
> I found this in the user comments:
> --------------------- Posted by Philippe Jausions on February 4 2005
4:57pm
>
> If you are one of the many people trying to import a CSV file into MySQL
> using mysqlimport under MS-Windows command/DOS prompt, try the following:
>
>
mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --l
> ines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE
> YOUR_TABLE.csv
> ---------------------
>
> so I tried copying the dbase table to a csv file and using some of these
> options. While I was doing this, I thought I might as well try the direct
> route - change the field type and see what happens. I noticed that the
table

> copy was producing slightly different results, but I don't know why. In my
> first import, the dBASE date field type became varchar(8) and many of the
> rows contained nulls, although all of the source fields were full. In the
> later imports, the field type was varchar(10) and all of the field values
> were imported properly. I would suggest checking your imported table in
> mySQL to ensure that you are getting the complete field contents (even if
> they are in the wrong format). Perhaps some of the option settings in the
> mySQL import changed something ... ? I will keep trying to figure out what
> specific change made this dBASE copy operation work differently on
> subsequent attempts. (That's what I did in the final reply)
>
> In the Query Browser, changing field type is so difficult - you have to
> actually type it in.<g> I closed Query Browser and went back to the
Control
> Center where you get a listbox. I changed the field type and went to look
> at it with the browse and ... success! All dBASE dates converted to mySQL
> date type.
>
>

Ivar B. Jessen

2005-08-22, 11:23 am

On Sat, 20 Aug 2005 15:43:48 -0400, "David Ball" <Thanks.But@NoSpam.4.me>
wrote:

>SECOND:
>In dBASE:
>use :FB:games05.dbf // a dBASE level 7 table
>copy to :my14:games05 // mySQL database table

I copied a table Selfield.dbf to a MySQL table SelMySQL whith your SECOND
method and show below the structure before and after copying:

Structure for table D:\vdbproj\Tables\Se
lfield.dbf
Table type DBASE
Version 7
Number of rows 3
Last update 20-08-2005
-------------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 Name CHARACTER 12 N
2 Number NUMERIC 8 2 N
3 ID AUTOINCREMENT 4 N
4 Dato DATE 8 N
5 DatoTid TIMESTAMP 8 N
6 Logisk LOGICAL 1 N
7 fFloat DOUBLE 8 N
8 fLong LONG 4 N
9 fMemo MEMO 10 N
-------------------------------------------------------------------------
** Total ** 64

Structure for table selmysql
Table type MySQL ODBC 3.51 Driver
Last update 22-08-2005
-------------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 Name VARCHAR 12 N
2 Number DOUBLE 22 31 N
3 ID INTEGER 11 N
4 Dato VARCHAR 10 N
5 DatoTid VARCHAR 30 N
6 Logisk TINYINT 1 N
7 fFloat DOUBLE 22 31 N
8 fLong INTEGER 11 N
9 fMemo LONGVARCHAR -1 N
-------------------------------------------------------------------------
** Total ** 583

The data before copying looks like this:

Record# Name Number ID Dato DatoTid Logisk fFloat fLong fMemo
1 Select,Field 123.45 1 21-12-2003 21-12-2003 11:14:15 .T. 100.00 200 memo
2 2 - - - - : : .F. 200.00 MEMO
3 Select,Field 323.45 3 23-12-2003 23-12-2003 13:14:15 .T. 300.00 600 memo

The data after copying looks like this:

Record# Name Number ID Dato DatoTid Logisk fFloat fLong fMemo
1 Select,Field 123.45 1 21-12-2003 21-12-2003 11:14:15 .T. 100.00 200 memo
2 2 - - - - : : .F. 200.00 MEMO
3 Select,Field 323.45 3 23-12-2003 23-12-2003 13:14:15 .T. 300.00 600 memo

In a Browse of the table SelMySQL the fields Number and fFloat are
displayed without decimals, the field Logisk is displayed as a 1 or a 0.
Everything else looks ok.

Ivar B. Jessen

David Ball

2005-08-23, 3:23 am

Hello Ivar

Oh well...

So much for consistency. My conversions worked yesterday. Today, I was
feeling happy with myself, but I had to compare something that I had done,
so I tried another conversion. I used a different level 7 dbf table with
date fields and converted it to mySQL. Unfortunately, after step 2, it was
still varchar(8). The setting in mySQL for field delimiter was set to double
quote. Darn!

I went to the BDE and checked some things. I made sure that 4 digit year was
set to TRUE as well as leading zeros for day and month set to TRUE.

I tried it again and it looked much better after step 2. Example:
12/30/2002. (varchar(10))
BUT
Now it's in mm/dd/yyyy format and I don't know why. (Yesterday, it was
yyyymmdd. BDE is set to Y/M/D with separator = - )
AND
Converting the field in mySQL to Date type ruined everything! Example:
0000-00-00

<much later, after trying many different things>

I hear Firebird is a good database to work with.

*sigh* I hate that. I thought I had it figured out yesterday, but I was
wrong. Back to the drawing board.

Thank you
David Ball
www.ChelseaData.ca

Sponsored Links