MySQL TIME type returns empty string

[seanhogge]

I have a field that contains a MySQL TIME value.

I'm using a coldfusion query to select this value from the database, and it is returning an empty string (according to cfdump). The other fields returned display normally. So it's as if I'm getting a partial recordset.

I have tried searching for the problem as related to CF MX7, as well as related to MySQL's server. Nothing appeared to address my problem, so I thought I'd take a stab out here.

If anyone knows of this problem and how to solve it, or even just a reference to where I can learn more about it, I would greatly appreciate your help.

[Ritesh]

Do you mean TIMESTAMP datatype?

[peterlaursen]

QUOTE

and it is returning an empty string

Good question is then: Does the database contain

1) time-values that makes sense ?

2) empty strings ?

3) NULL-values ??

What does SQLyog show when viewing data from DATA or RESULT-tab ?

TIMESTAMP(14) and DATETIME variables in MySQL are basically 14-character strings (that is how it is implemented with the database itself), but a client should be “smart” enough to distinguish between a string having a TIME-meaning and any other string. In a scripting language you should choose an appropriate method and language variable type.

I don't know enough about coldfusion to tell whether it has problems with MySQL TIME-variable types. But first of all chece that the data in the DB are as they should be!

[peterlaursen]

BTW

there might be something here that you can use:

http://www.google.com/search?q=coldfusion+…=utf-8&oe=utf-8

[seanhogge]

The data type was not a TIMESTAMP. It was a TIME type.

The data was hand entered by me (dummy data for testing during development). Everything appeared to have non-null values in SQLYog, and I am positive (barring hallucinatory optimism) they were formatted correctly.

When I ran an identical query (ColdFusion has a nifty feature that shows the SQL string sent to the database after all processing) by cutting and pasting into SQLyog, the data was returned without a hitch.

I'm feel it's a ColdFusion issue from what I've seen. Which makes this thread somewhat inappropriate here.

I will look through the Google results you showed me, though I've been through most of them. The new ones might have some leads. If I find anything definitive I will report it here anyway.

Coincidentally, I simply separated the DATETIME into a DATE field and a VARCHAR field for the time. I can control the user input, so that'll have to work for now. All the Date functions and comparisons work now.

Thanks for your help, I'm not as puzzled as to what caused this, and I think a little more work will yield an answer.

[peterlaursen]

QUOTE

Everything appeared to have non-null values in SQLYog

then I believe that something went wrong when you entered data!

One of the google results point of an important difference between ODBC and JDBC – that is that type checking is much stronger in JDBC. Values like 0000-00-00 and 2005-13-37 would never be acceptepted by JDBC as a legal value for DATE. The result of an attempt to enter such data would leave the DB values as NULL.

Equally JDBC will not accept 99-99-99 as TIME (hours-minutes-seconds)

[seanhogge]

You may be absolutely right Mr. Laursen.

I put in the value “00:08:25” for instance.

Coldfusion returns (JDBC DSN): {ts '1970-01-01 00:00:00'}

ASP (ODBC DSN): 7/28/2005

Obviously I am not understanding the TIME type. I was under the impression that it was HH:MM:SS.fraction. Why would Coldfusion think it a DATETIME and why would VBScript think it a date?

I feel like I'm being obtuse here, so if it's something stupid and obvious, don't hesitate to say so with as much mockery as you feel necessary.

[peterlaursen]

QUOTE

I put in the value “00:08:25” for instance.

How did you input it and what does SQLyog (and you could try command-line client too) say that the value is ?

I shall not say anything more (it is more then 10 years ago that I have been coding myself except for some HTML and the like) but just came across this discussion thread in the MySQL forums where typechecking issues in JDBC compared to ODBC was discussed in relation to Coldfusion.

[seanhogge]

That's quite understandable. As I've said, this discussion doesn't even really belong here.

I must say that you and Ritesh are some of the more helpful and knowledgable around. I hope you take my wasting your time as a compliment.

I just typed it into SQLyog first. Then I entered in through the command line. Same results. With 20:00:00 I get a null value or empty string, with 00:08:25, I get the dates.

Obviously, something is up with my data format. I know this is tedious at this point. Don't worry further about it. Once I figure it out, I'll let you guys know just for the hell of it. Thanks for the assistance!

[peterlaursen]

you are welcome!

but of course you are welcome too to buy a few site licenses for SQLyog 😀

to enter data into a datetime from SQLyog just enter it as a 14-character string

like “20050730111112” (year2005 month07 day30 hour11 minute11 second12)

Simply omit any input mask. I believe that works with JDBC too (?). But maybe VB wants an input mask – I don't know!

In the example below I typed those values

“20050730111112”

“20:00:00”

“00:08:25”

“00000000082500”

and after save and retrieve the are like image!

Maybe this would be some help for debugging your problem!

[seanhogge]

I will be buying a license soon! I've just started a company, so we're getting accounting squared away.

I've discovered that it was not my data entry that was the issue.

I was using a System DSN (Win2k3) through the ColdFusion Administrator (CFAdmin). When I switched the data source driver to a pre-configured “MySQL 3.x” driver, the data came back flawlessly with DATETIME and TIME types!

[Author]

Posts