I loaded a bunch of data into a table but the blob column ended up with
the text '5265666163746f72' instead of the binary data x'5265666163746f72'.

I've tried various combinations of CONVERT() and CAST() but don't seem
able to update the table with the /binary/ representations so that when
I select the value out I should get "Refactor".

Is there an obvious answer I'm missing?

--
Visit <http://blogs.instreamco.com/anything.php> to read
my rants on technology and the finance industry. Visit
<http://tggagne.blogspot.com/> for politics, society and culture.

Thomas Gagne wrote:
> I loaded a bunch of data into a table but the blob column ended up with
> the text '5265666163746f72' instead of the binary data x'5265666163746f72'.
>
> I've tried various combinations of CONVERT() and CAST() but don't seem
> able to update the table with the /binary/ representations so that when
> I select the value out I should get "Refactor".
>
> Is there an obvious answer I'm missing?
>

How are you trying to load the data, and what is an example of the data
you're loading?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> <snip>
>
> How are you trying to load the data, and what is an example of the
> data you're loading?
>
The data is being bulk-exported from Sybase and bulk-loaded into MySQL.
When the blob is exported from Sybase we get the hex string,
"5265666163746f72." When the string is LOADed into MySQL it seems to be
inserted not as binary data, but as character data. As a result,
programs expecting binary data are getting character data instead.

In the INPUT file, there's a row:

1^1^5265666163746f72

Of course, the two 1s are loaded into two columns as integers
wonderfully. The problem is how to get that data loaded into MySQL
wonderfully, or, now that it's loaded--converting it into binary so that
its value is x'5265666163746f72' which will translate as "Refactor"
rather than "5265666163746f72."

--
Visit <http://blogs.instreamco.com/anything.php> to read
my rants on technology and the finance industry. Visit
<http://tggagne.blogspot.com/> for politics, society and culture.

Thomas Gagne wrote:
> Jerry Stuckle wrote:
>> <snip>
>>
>> How are you trying to load the data, and what is an example of the
>> data you're loading?
>>
> The data is being bulk-exported from Sybase and bulk-loaded into MySQL.
> When the blob is exported from Sybase we get the hex string,
> "5265666163746f72." When the string is LOADed into MySQL it seems to be
> inserted not as binary data, but as character data. As a result,
> programs expecting binary data are getting character data instead.
>
> In the INPUT file, there's a row:
>
> 1^1^5265666163746f72
>
> Of course, the two 1s are loaded into two columns as integers
> wonderfully. The problem is how to get that data loaded into MySQL
> wonderfully, or, now that it's loaded--converting it into binary so that
> its value is x'5265666163746f72' which will translate as "Refactor"
> rather than "5265666163746f72."
>

Your problem is not that MySQL is importing it incorrectly - it's that
Sybase is exporting in a not-traditional format that MySQL doesn't
understand.

The easiest way i can think of is just to reformat the date as
x'5265...' then load it. This should import it as hex data.

Alternatively, I think you'll have to write a short script to load the
data into your database (unless you can coax Sybase to export in a more
db-friendly format).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> <snip>
> Your problem is not that MySQL is importing it incorrectly - it's that
> Sybase is exporting in a not-traditional format that MySQL doesn't
> understand.
>
> The easiest way i can think of is just to reformat the date as
> x'5265...' then load it. This should import it as hex data.
>
> Alternatively, I think you'll have to write a short script to load the
> data into your database (unless you can coax Sybase to export in a
> more db-friendly format).
>
What would that format be? I've tried x'5265666163746f72' and
0x'5265666163746f72' but both are loaded with the preceding x or 0x and
not as binaries.

The table (tw_blob) is defined at int, int, blob. The input row now
looks like:

1^1^x'5265666163746f72'

but the data still seems to be loaded as character and not binary.

--
Visit <http://blogs.instreamco.com/anything.php> to read
my rants on technology and the finance industry. Visit
<http://tggagne.blogspot.com/> for politics, society and culture.

On Wed, 09 Apr 2008 15:53:29 -0400, Thomas Gagne wrote:
> This is a multi-part message in MIME format.
> --------------030405070801010106050900
> Content-Type: text/plain; charset=UTF-8; format=flowed
> Content-Transfer-Encoding: 7bit

You think you could cut that out? Mostly we don't really need
HTML-encoded messages here, to get our points across.


>
> Jerry Stuckle wrote:
>> <snip>
>> Your problem is not that MySQL is importing it incorrectly - it's that
>> Sybase is exporting in a not-traditional format that MySQL doesn't
>> understand.
>>
>> The easiest way i can think of is just to reformat the date as
>> x'5265...' then load it. This should import it as hex data.
>>
>> Alternatively, I think you'll have to write a short script to load the
>> data into your database (unless you can coax Sybase to export in a
>> more db-friendly format).
>>
> What would that format be? I've tried x'5265666163746f72' and
> 0x'5265666163746f72' but both are loaded with the preceding x or 0x and
> not as binaries.
>
> The table (tw_blob) is defined at int, int, blob. The input row now
> looks like:
>
> 1^1^x'5265666163746f72'
>
> but the data still seems to be loaded as character and not binary.

mysql> create table tablex (my_col blob(40));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tablex (my_col) values (x'5265666163746f72');
Query OK, 1 row affected (0.01 sec)

mysql> select my_col from tablex;
+----------+
| my_col |
+----------+
| Refactor |
+----------+
1 row in set (0.00 sec)

What question are you really trying to ask? How to construct an
appropriate LOAD DATA? How to represent it as something on output? "Why
does MySQL represent blobs as text on output?"

--
Cunningham's First Law:
Any sufficiently complex deterministic system will exhibit
non-deterministic behaviour.

Jerry Stuckle wrote:
> <snip>
>
> Alternatively, I think you'll have to write a short script to load the
> data into your database (unless you can coax Sybase to export in a
> more db-friendly format).
>
"gawk" is my friend.

BEGIN {
FS = "^"
RS = "~"
print "truncate table store.TW_Blob"
print ";\ninsert into store.TW_Blob (primaryKey, blobType, blobData)
values"
}

{
printf "(%d, %d, 0x%s)", $1, $2, $3
if ($RC % 100 == 0)
print ";\ninsert into store.TW_Blob (primaryKey, blobType,
blobData) values"
else
printf ",\n"
}

END {
print ";"
}


--
Visit <http://blogs.instreamco.com/anything.php> to read
my rants on technology and the finance industry. Visit
<http://tggagne.blogspot.com/> for politics, society and culture.

Peter H. Coffin wrote:
> On Wed, 09 Apr 2008 15:53:29 -0400, Thomas Gagne wrote:
>
>> This is a multi-part message in MIME format.
>> --------------030405070801010106050900
>> Content-Type: text/plain; charset=UTF-8; format=flowed
>> Content-Transfer-Encoding: 7bit
>>
>
> You think you could cut that out? Mostly we don't really need
> HTML-encoded messages here, to get our points across.
>
Sorry about that.
>
>
>> <snip>
>
> <snip>
>
> What question are you really trying to ask? How to construct an
> appropriate LOAD DATA? How to represent it as something on output? "Why
> does MySQL represent blobs as text on output?"
>
>
I was trying to figure out how to get a LOAD DATA INFILE to work with
blobs. Ultimately, I couldn't, but I was able to use gawk to convert it
to a bunch of INSERT statements that did work.


--
Visit <http://blogs.instreamco.com/anything.php> to read
my rants on technology and the finance industry. Visit
<http://tggagne.blogspot.com/> for politics, society and culture.