[BioSQL-l] Question about VARCHAR BINARY

[Florian Mittag]

Hi Hilmar,

thanks for the answer, we sortof solved the problem differently, because other 
databases (like PostgreSQL) do not specifiy those columns as VARBINARY, so we 
simply took those scheme as the foundation for our DB2 scheme.

On Saturday 04 July 2009 15:22, Hilmar Lapp wrote:
> Hi Florian,
>
> sorry this fell through the cracks, too much traveling. VARCHAR BINARY
> in MySQL means that the column is a VARCHAR type but values (and
> searches against them) are treated case-sensitive.

Oh, I didn't know that. I thought it was about handling non-displayable 
characters.


> Since treating VARCHAR values case sensitive is standard in SQL
> everywhere else and presumably also in DB2, you should map it to a
> simple VARCHAR type.
>
> Where did you find that MySQL's VARCHAR BINARY needs to be VARBINARY
> in DB2?

Chapter 11: MySQL converter

ftp://ftp.software.ibm.com/software/data/db2/migration/mtk/mtk_2050.pdf



Florian


> On Jun 4, 2009, at 9:52 AM, Florian Mittag wrote:
> > Hi everyone!
> >
> > I'm new to BioSQL and just started using it, when I noticed that
> > some tables
> > contain columns with type VARCHAR BINARY, e.g. the column "name" in
> > the
> > table "biodatabase". May I ask, what is the reason behind this?
> >
> > We switched to a DB2 database for some performance issues and set up
> > the
> > BioSQL schema, and MySQL's VARCHAR BINARY corresponds with DB2's
> > VARBINARY,
> > which is somewhat annoying because the content of those columns is
> > displayed
> > hexadecimal on command line interface and the visualizer we are
> > using just
> > shows "BINARY, x bytes".
> >
> > Since I'm new BioSQL I just might miss an important point, but it
> > seems to me
> > that there is no reason to have those columns defined as BINARY,
> > because only
> > text is stored in them.
> >
> > Could you please enlighten me? ;-)
> >
> >
> > Regards,
> >  Florian
> > _______________________________________________
> > BioSQL-l mailing list
> > BioSQL-l at lists.open-bio.org
> > http://lists.open-bio.org/mailman/listinfo/biosql-l