conversion status

James K. Lowden jklowden at speakeasy.org
Fri Aug 30 00:37:42 EDT 2002


All, 

I tested the dbconvert() tonight.  I thought you might be interested in
what I found.  

Executive summary: B+.  No segfaults.  Mostly correct outcomes when the
return code indicates success.  Unexotic conversions mostly succeed. 
Failures trigger the error handler with oh-so-helpful messages.  

You can reproduce my results if you like, by looking at this page:

http://www.SchemaMania.org/freetds/cvs/freetds/src/dblib/unittests/

You need: convert_acid.c and eg_handlers.h
also:	  convert_acid.make (a shell script)
and	  convert_acid.output (my results)
and	  killer_query.sql 

The query returns one row of 18 datatypes.  The program executes a query
(from stdin) and attempts to convert each value in the result set to every
known datatype.  It consults dbwillconvert() first, and doesn't attempt to
convert incompatible types.  

Anyone think any of this should be in cvs?

== the results (with TDS 5.0) ==

Forget Binary, image, numeric, and decimal.  They're mostly broken.  

input		status	comment
=====		======	=======
char 		OK	No numeric/decimal, no money. 
text, varchar	Perfect!
bit		OK  	No numeric/decimal. 
tinyint		OK	No numeric, image.
int, smallint	OK	No numeric/decimal, image/binary.
real, float	fair	No numeric, image, integers; maybe money.  
datetime	OK	No image/binary.
smalldatetime	OK	No image/binary.
money		fair	No numeric, image, integers.  
money		wrong	float & real yield -283649471676416.
smallmoney	OK	No numeric/decimal, image/binary.	
smallmoney	odd	201203.04 -> real yields 201203.046875 
numeric		broken	char/varchar only. 
decimal		broken	char/varchar, float/real only.
timestamp	broken	char/varchar only. 
image,varbinary	broken	char, text, binary only.  

== conclusions ==

0.  Everything can be made into a char/varchar!
1.  Money needs attention.  Should convert to int, should correctly
convert to floating point.  This is just a debugging effort; the code is
there.  
2.  What happened to float -> int? 
3.  Numeric/decimal is not ready.  

As far as I'm concerned, 1 & 2 have to work in 0.60.  Numeric is the other
big hole, but I don't consider it essential.  I know, I've many times said
"any<->any in 0.60".  I wasn't thinking about legal weird conversions like
"datetime<->image".  If we crossed those three items off the list (as
done), I'd consider the job complete.  

And, if someone wants to make numeric work in 0.60, you've still got a
couple of weeks! 

The mere fact that my testing was worth bothering with is evidence of how
far we've come.  I'm going to return to updating the UG in preparation for
our release.  As soon as money works, I'll arrange a pre-release.  

I hope you're having fun.  I sure am.  

Regards,

--jkl



More information about the FreeTDS mailing list