OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

entering and retreiving time values with ODBC MySql

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Luctor
General User
General User


Joined: 06 Jul 2004
Posts: 14

PostPosted: Sun Aug 22, 2004 4:48 am    Post subject: entering and retreiving time values with ODBC MySql Reply with quote

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
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Aug 22, 2004 5:43 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Luctor
General User
General User


Joined: 06 Jul 2004
Posts: 14

PostPosted: Sun Aug 22, 2004 6:06 am    Post subject: Reply with quote

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
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Aug 22, 2004 6:50 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Luctor
General User
General User


Joined: 06 Jul 2004
Posts: 14

PostPosted: Sun Aug 22, 2004 9:31 am    Post subject: Reply with quote

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
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Aug 22, 2004 6:21 pm    Post subject: Reply with quote

Yes, data browser is showing full TIME column data (haven't had the need for formatting)

Just for a test of this feature here Wink
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
View user's profile Send private message Visit poster's website
Luctor
General User
General User


Joined: 06 Jul 2004
Posts: 14

PostPosted: Mon Aug 23, 2004 12:51 am    Post subject: Reply with quote

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
View user's profile Send private message
Luctor
General User
General User


Joined: 06 Jul 2004
Posts: 14

PostPosted: Mon Aug 23, 2004 1:07 am    Post subject: Problem SOLVED !!! Reply with quote

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
View user's profile Send private message
softwarezman
General User
General User


Joined: 20 Sep 2004
Posts: 14
Location: Idaho Falls, ID

PostPosted: Mon Sep 27, 2004 2:31 pm    Post subject: Reply with quote

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
View user's profile Send private message
softwarezman
General User
General User


Joined: 20 Sep 2004
Posts: 14
Location: Idaho Falls, ID

PostPosted: Mon Sep 27, 2004 2:46 pm    Post subject: Reply with quote

Ok so that almost fixed my problem Very Happy. 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
View user's profile Send private message
Luctor
General User
General User


Joined: 06 Jul 2004
Posts: 14

PostPosted: Tue Sep 28, 2004 3:08 am    Post subject: Reply with quote

softwarezman wrote:
Ok so that almost fixed my problem Very Happy. 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 Very Happy
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
View user's profile Send private message
softwarezman
General User
General User


Joined: 20 Sep 2004
Posts: 14
Location: Idaho Falls, ID

PostPosted: Tue Sep 28, 2004 5:14 am    Post subject: Reply with quote

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
View user's profile Send private message
Luctor
General User
General User


Joined: 06 Jul 2004
Posts: 14

PostPosted: Tue Sep 28, 2004 5:52 am    Post subject: Reply with quote

Idea In the table designer you can also define a default format for each column

Rob
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group