Unknown Types value error

[acdhirr]

Hello people,

What is happening here? I have a MySQL DB with a table 'guestbook'.
When I try to update a column I keep getting a message 'Unknown Types
value'. It has something to do with the cfsqltype attribute of
cfqueryparam in QueryExecution.cfc I suppose but I cannot figure out
what...

<cfset transfer = application.transferFactory.getTransfer() />
<cfset transfer.list('guestbook')>

<cfset anEntry = transfer.get('guestbook',1)>
<cfset anEntry.setText('hi folks') />
<cfset transfer.save(anEntry)>

And then: Unknown Types Value

Am I missing something here? I am new to transfer, you know.

Any insights appreciated. Thanks,
Richard.

[Mark Mandel]

Can you please post the relevant details as per:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer

What is your table configuration as well?

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com

[acdhirr]

Status: solved.

Oh man... I am so sorry. It was late at night, fiddling around a bit,
and I wanted some results very fast. So I just picked an existing
datasource from the CFAdmin datasources list, that was using the built-
in MySQL driver.
Of course I should have used the jdbc Connector instead, for table
Metadata to be available. Stupid me. Sure I was missing something
there. I 'fixed' this issue as soon as I woke up from a good night
sleep.

Searching for 'Unknown Types value Transfer' in Google returned very
few results. That should have made me think.

I will be using Transfer a lot in upcoming projects. Excellent stuff.
Model Glue Unity with Transfer, I love it. It will take me some time
to grasp it's full potential, so from now on I am not going to make
too many silly mistakes anymore!

Thanks for the good work,
Richard

[Mark Mandel]

That's no big deal, I'm just still trying to work out what went wrong - I'm still not entirely sure what error you got.

Are you saying it gave you back the error that you weren't working on a supported database?

Or was it something else?

Mark

[acdhirr]

(System: CFMX7 on Windows XP, MySQL 4.1 on Fedora, Transfer 1.0 RC2)

OK this is what I got from ModelGlue debugging output:

Message Error Executing Database Query.
Detail Unknown Types value
Extended Info
Tag Context
D:\WWWRoot\transfer\com\sql\QueryExecution.cfc (115)
D:\WWWRoot\transfer\com\sql\QueryExecution.cfc (59)
...

When I run the same lines of code that cause the error outside
ModelGlue, the top 3 lines from the java trace state:

java.sql.SQLException: Unknown Types value
at org.gjt.mm.mysql.PreparedStatement.setObject(Unknown Source)
at org.gjt.mm.mysql.PreparedStatement.setObject(Unknown Source)
at
coldfusion.server.j2ee.sql.JRunPreparedStatement.setObject(JRunPreparedStatement.java:
208)
...

SQL from debug:
UPDATE guestbook SET entryid = (param 1) ,name = (param 2) ,date =
(param 3) ,text = (param 4) ,ipaddress = (param 5) ,isPublic = (param
6) ,abuse = (param 7) ,entryid = (param 8) WHERE recordid = (param 9)
(very informative)

I was pretty sure this error resulted from the datasource being
registered with the wrong (outdated) driver. The datasource used the
native MySQL 3.1 driver from the driver option list in CFMX
administrator. I never use this option anymore for any serious
production or developing whatsoever, but since I was only testing I
just randomly picked an old unused datasource - with the outdated
driver. The 'org.gjt.mm.mysql' in the Java trace shows that it's the
old driver being used and causing trouble (mm stands for Mark
Matthews, he wrote the Connector/J driver I believe back in 2002, but
even version 7 of CF doesn't include it - one has to install it by
hand). And indeed, using the newer Connector/J MySQL driver solves the
problem.

But there is more.

Now this is interesting: when I revert to the old driver (native MySQL
3.1) - as one would expect - I get the error message again. But when
in Transfer.xml I then replace all fields having type='boolean' with
type='numeric' - even the old driver works!

I was wrong in my previous post assuming that Transfer needs the
database driver to gather metadata for table columns. Metadata of
course is provided by Transfer.xml. I remembered stumbling onto a
similar issue outside the context of Transfer some time ago. It's the
cfqueryparam cfsqltype attribute having a value 'CF_SQL_BIT' that the
old driver can't cope with (function executeQuery in
QueryExecution.cfc).

MySQL has no boolean fields. It uses TINYINT instead. Still it makes
sense to call these fields 'boolean' in transfer.xml. Apparently the
Connector/J driver maps CF_SQL_BIT to the right data type.

So for MySQL databases one either has to install the Connector/J
driver or not be using the 'boolean' type at all in Transfer.xml, but
'numeric' instead. The latter would somehow break the database
abstraction, which is a pity.

So it's not Transfer that's to blame, for it supports MySQL quite
well, as far as I have seen until now, as long as you use an up to
date MySQL driver. Maybe CFMX 8 ships with a decent MySQL driver, I
don't know I'm still using MX7. For that matter this problem doesn't
even address Transfer, since it's about CFMX and MySQL working
together. However maybe it's an idea to mention the need for the
Connector/J driver for MySQL databases in the 'Supported Systems' page
of your docs. Just in case someone else trips over these booleans.

Richard

> E: mark.man...@gmail.com
> W:www.compoundtheory.com

[Mark Mandel]

Aha!

Thanks for expanding on that, I've not run into that before.

I tend to test using the mySQL 5 drivers, but at least now if someone runs into it, we have a record of what is going on.

Thanks for spending the time to have a good look into this! :oD

Mark