| View previous topic :: View next topic |
| Author |
Message |
Luctor
General User
Joined: 06 Jul 2004
Posts: 14
|
 Posted: Sun Aug 22, 2004 4:48 am Post subject: entering and retreiving time values with ODBC MySql |
 |
|
Hi all,
I have a database with a table that uses time values.
Here's the output of mysql:
| Code: |
mysql> describe muziekles.rooster;
+------------+--------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+----------+----------------+
| roosterID | int(11) | | PRI | NULL | auto_increment |
| leerlingID | int(11) | YES | | 0 | |
| lesdag | varchar(100) | YES | | | |
| lestijd | time | YES | | 00:00:00 | |
| lesduur | time | YES | | 00:00:00 | |
+------------+--------------+------+-----+----------+----------------+
5 rows in set (0.02 sec)
|
When I enter time values in the datasource browser or in a form OOo, displays this :
| Code: |
roosterID leerlingID lesdag lestijd lesduur
1 1 donderdag 00:00
2 0 donderdag 00:00 00:00
4 0 donderdag 00:00 00:00
|
However, mysql displays the correct values i've entered (in OOo itself !!)
| Code: |
mysql> select * from muziekles.rooster;
+-----------+------------+-----------+----------+----------+
| roosterID | leerlingID | lesdag | lestijd | lesduur |
+-----------+------------+-----------+----------+----------+
| 1 | 1 | donderdag | 00:00:00 | 00:15:00 |
| 2 | 0 | donderdag | 09:45:00 | 00:15:00 |
| 4 | 0 | donderdag | 10:30:00 | 10:45:00 |
+-----------+------------+-----------+----------+----------+
3 rows in set (0.00 sec)
|
isql (odbc commandline utility) shows the same (correct) output.
Apperently openoffice has problems dealing with time values returned by the odbc driver.
I've read something about M$ Excel also having problems with MyODBC and you could use CONCAT() to convert the time value to a string.
This works also in OOo, but (not surpisingly) it isn't possible to update or insert time values this way.
I've found one issue which is sort of similar to this, although the issue refers to the input being converted faulty. http://www.openoffice.org/issues/show_bug.cgi?id=30577
Should this be issued as a bug ?
Rob
LFS 5.1, OOo 1.1.2, MySQL 4.0.20, MyODBC 3.51.07, unixODBC 2.2.9 |
|
| Back to top |
|
 |
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2478
Location: Chonburi Thailand Asia
|
| Posted: Sun Aug 22, 2004 5:43 am Post subject: |
|
|
Maybe i'm misunderstanding, but:
the column formats in the database browser can be adjusted to your needs, right-click on the column name for a menu.
HTH
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Luctor
General User
Joined: 06 Jul 2004
Posts: 14
|
| Posted: Sun Aug 22, 2004 6:06 am Post subject: |
|
|
I don't believe is has to do with formating.
I've just about tried all formats for the time columns.
Can someone reproduce the things i've described above ?
Rob |
|
| Back to top |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2478
Location: Chonburi Thailand Asia
|
| Posted: Sun Aug 22, 2004 6:50 am Post subject: |
|
|
| Code: | mysql> describe test.timetest
-> ;
+----------+------------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+----------+----------------+
| id | int(11) unsigned | | PRI | NULL | auto_increment |
| sometext | char(100) | | | | |
| time | time | | | 00:00:00 | |
+----------+------------------+------+-----+----------+----------------+
3 rows in set (0.00 sec)
mysql> select * from test.timetest;
+----+----------------------+----------+
| id | sometext | time |
+----+----------------------+----------+
| 1 | firstentry | 00:00:01 |
| 2 | secondRow:010203 | 01:02:03 |
| 3 | third entry 23:59:59 | 23:59:59 |
+----+----------------------+----------+
3 rows in set (0.17 sec)
|
Then a F4 in OOo:
shows same data as the above SELECT (no matter of formatting, alas).
Inserting in database browser (got an cursor error, but data was inserted:
| Code: | mysql> select * from test.timetest;
+----+------------------------+----------+
| id | sometext | time |
+----+------------------------+----------+
| 1 | firstentry | 00:00:01 |
| 2 | secondRow:010203 | 01:02:03 |
| 3 | third entry 23:59:59 | 23:59:59 |
| 4 | OOo Data browser entry | 01:02:04 |
+----+------------------------+----------+
|
Should I try it with a form, too?
| Code: | twinni@thinkmobil:/> rpm -qa | grep unixODBC
qt3-unixODBC-3.3.3-7
MyODBC-unixODBC-2.50.39-215
unixODBC-devel-2.2.3-92
unixODBC-gui-qt-2.2.3-92
unixODBC-2.2.3-92
|
not sure which driver is actually used, datasource is set up with option type: MySQL on first tab....
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Luctor
General User
Joined: 06 Jul 2004
Posts: 14
|
| Posted: Sun Aug 22, 2004 9:31 am Post subject: |
|
|
Does the datasource browser (F4) show you the correct values ?
I've made a little snapshot
[url=http://home.kabelfoon.nl/~rvdberg/snapshot1.gif]
 [/url]
here's the output for the same data in mysql
| Code: |
select * from timetest;
+----+-----------------+----------+
| ID | text | time |
+----+-----------------+----------+
| 1 | time = 10:00:00 | 10:00:00 |
| 2 | time = 11:45:01 | 11:45:01 |
| 3 | time = 23:23:00 | 23:23:00 |
+----+-----------------+----------+
3 rows in set (0.00 sec)
|
|
|
| Back to top |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2478
Location: Chonburi Thailand Asia
|
| Posted: Sun Aug 22, 2004 6:21 pm Post subject: |
|
|
Yes, data browser is showing full TIME column data (haven't had the need for formatting)
Just for a test of this feature here 
For command line output see message above.
Maybe it's time to test another ODBC driver....
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Luctor
General User
Joined: 06 Jul 2004
Posts: 14
|
| Posted: Mon Aug 23, 2004 12:51 am Post subject: |
|
|
| probe1 wrote: |
Maybe it's time to test another ODBC driver.... |
Only difference I could spot is that I'm using unixODBC 2.2.9 and you are using 2.2.3 |
|
| Back to top |
|
|
Luctor
General User
Joined: 06 Jul 2004
Posts: 14
|
| Posted: Mon Aug 23, 2004 1:07 am Post subject: Problem SOLVED !!! |
|
|
| Luctor wrote: | | probe1 wrote: |
Maybe it's time to test another ODBC driver.... |
Only difference I could spot is that I'm using unixODBC 2.2.9 and you are using 2.2.3 |
There's another one ..
I'm using libmyodbc3-3.51.07..
I've set up a datasource with the old driver libmyodbc-2.50.39.
It seems to work !!
However I still think this should be reported.
But is it an OOo bug or a MyODBC bug ? |
|
| Back to top |
|
|
softwarezman
General User
Joined: 20 Sep 2004
Posts: 14
Location: Idaho Falls, ID
|
| Posted: Mon Sep 27, 2004 2:31 pm Post subject: |
|
|
| It sure seems like a myODBC bug. I am running the latest version you can get from MySQL's website and I'm running into the EXACT same thing you are. How exactly did you fix it? I'm on a Windows machine at this time but the production server will go onto a unix box. Just trying to get it built here first... any help? |
|
| Back to top |
|
|
softwarezman
General User
Joined: 20 Sep 2004
Posts: 14
Location: Idaho Falls, ID
|
| Posted: Mon Sep 27, 2004 2:46 pm Post subject: |
|
|
Ok so that almost fixed my problem  . Now what i'm getting are decimals in place of the times?? Pretty darn weird really. For example I get 0.92 instead of 22:00:00 and 0.83 instead of 19:48:00. Any help on that one? anybody? |
|
| Back to top |
|
|
Luctor
General User
Joined: 06 Jul 2004
Posts: 14
|
| Posted: Tue Sep 28, 2004 3:08 am Post subject: |
|
|
| softwarezman wrote: | | Ok so that almost fixed my problem . Now what i'm getting are decimals in place of the times?? Pretty darn weird really. For example I get 0.92 instead of 22:00:00 and 0.83 instead of 19:48:00. Any help on that one? anybody? |
right click on the column name,
then you can choose the format.
I'm using gentoo linux now and it worked out of the box
| Code: |
qpkg odbc -i -I
dev-db/unixODBC-2.2.6 *
ODBC Interface for Linux [ http://www.unixodbc.org/ ]
dev-db/myodbc-3.51.06 *
ODBC driver for MySQL [ http://www.mysql.com/products/myodbc/ ]
|
Rob |
|
| Back to top |
|
|
softwarezman
General User
Joined: 20 Sep 2004
Posts: 14
Location: Idaho Falls, ID
|
| Posted: Tue Sep 28, 2004 5:14 am Post subject: |
|
|
| Freaking GENIOUS! Or i'm not that smart. Ok so now I feel pretty retarded... I felt like it had to be something that simple but dang... thank you so much! |
|
| Back to top |
|
|
Luctor
General User
Joined: 06 Jul 2004
Posts: 14
|
| Posted: Tue Sep 28, 2004 5:52 am Post subject: |
|
|
 In the table designer you can also define a default format for each column
Rob |
|
| Back to top |
|
|
|
