LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: PGeee

I have a DB store which loads data happily including a date field and a time field. The format of the date is ccyy-mm-dd and the format of the time is HH:MM:SS

If I run the BRG on another pc, Lavstorm changes the time field to a date format before loading to the table, so the database store fails. eg it changes 12:32:14 to 2012-32-14 and sets the time field to type date

If I run a DB Query on that same pc against the data in the table (all good data), again it reformats all the time fields to date format in the result of the query

I am assuming this is some kind of global setting somewhere. Any assistance greatly apprecaited

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

Can you give us more information about the PC's and the server(s). What OS is running on the PC's (XP, Vista or Windows 7)? What version of BRE is running on each PC? What is the BRAIN Server version? What hardware is BRAIN running on? Are you connecting to the same server with both PC's or is each connecting to a local BRAIN Server using the Lavastorm Desktop? How are you connecting to the DB (OCI, ODBC, JDBC)?

Sorry about the 20 questions, but all of this does matter.

Thanks,

Larry

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: PGeee

Hi Larry

Both pcs are XP Pro Version 2002 Service Pack 2
They are regular windows desktops using Celeron 3.46 Ghz
They are both using ODBC to connet to the same database using the same version of ODBC
I think the brain servers we run are both local as they are fired off from
"C:\Program Files\Lavastorm\BRAIN 4.1"

For the pc where it works
Lavastorm version is 4.1.3.0 Build 253
Server verison is Brain server 4.1.3

For the pc where it does not work
Lavastorm version is 4.1.5.0 Build 268
Server verison is Brain server 4.1.5


Your assistance apprecaited

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mermacora

I had an onsite visit with PGeee (aka Paul) today to try to figure this out.

The problem appears to be related to limited support of MySQL DB time typed columns. When a time column is included in the SQL SELECT of a DB Query node, the values are returned in BRAIN as a date typed value - e.g. a DB value of "13:57:39" is returned in BRAIN as "2013-57-39".

I'm not sure if this is an issue in the ODBC driver or LD, but it occurs in both LD version 4.1.3 and 4.1.5. I've asked Paul to confirm the version of the MySQL database on the backend server, but both LD installations are connecting using v3.51 of the MySQL ODBC driver.

I suggested that Paul casts the DB time value to a string in the SQL to work-around this issue.

Any other suggestions or advice would be greatly appreciated.

Cheers,
Mario

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: PGeee

i've dug in a bit deeper

Both pcs have the same problem when it comes to SQL SELECTs. This should make it simpler to diagnose

Even if I just run SELECT CURRENT_TIME, the field in the brd file is returned as bdate, not btime. This is the simplest incarnation of the symptom which is bedevelling me.

if I export to xls the same symptom. The xls column appears as (eg)
CURRENT_TIME:date
2014-56-02


My SQL server details:-

My SQL version: MySQL 4.0.15
Network Name: vus037.rr.in.telstra.com.au
IP: 192.74.156.92

ODBC Version - 3.51.06 - only checked boxes in the options are "Don't Optimise column width" and "Return Matching Rows"

I ran an ODBC trace log and have attached alongisde xls and brd files

to Recap

PC1 - XP Pro
Lavastorm version is 4.1.3.0 Build 253
Server version is Brain server 4.1.3

PC2 - XP Pro
Lavastorm version is 4.1.5.0 Build 268
Server version is Brain server 4.1.5

MySQL Browser retrives data with no issue

Your assistance appreciated
Attachments:
time to date issue.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

It appears that we have a bug in pulling time from a TIME typed field in MySQL. This has been logged, but I think you could probably do a work around by using the date accessors to pull the data out and recreate a time type, inside a filter node. I realize this isn't ideal, but should keep you running until this bug is fixed.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rpcwong

Would this help?

SELECT

DATE(yourTimefield) as yourTimefield_Date,
TIME(yourTimefiled) as yourTimefield_Time

It works on our mysql DB.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: PGeee

hi, and thanks for suggestions. The issue was not directly with a select, as it's easy to reformat the field returned. The select was only in the trail to illustrate the bug in Lavastorm.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: bds

I've have a customer who is trying to insert a timestamp into a table column defined as type time. The DB LOader fails with an invalid type message. I queried the table and found dates were being inserted where I expected to find times. In addition the BRD viewer shows the column that I expected to be of type time to be a date. Subsequently I were able to use the DB Loader to insert records setting the time defined field (from the perspective of MYSQL) to todays date. I'm currently trying to replicate the situation on my own PC and come up with a workaround. Does anyone have any ideas?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: bds

I tried inserting a time into the column defined as type time and all worked well when using JDBC. Obviously the bug exists with the ODBC interface