Stephen, At 12:53 AM 11/12/01 -0800, you wrote: >Hi Heikki, > >Comments inserted in text below: > >> Steve, >> >>>Date: Mon, 12 Nov 2001 08:52:54 >>>To: [EMAIL PROTECTED] >>>From: Heikki Tuuri <[EMAIL PROTECTED]> >>>Subject: Re: Cannot add blob data to innodb table >>> >>>Stephen, >>> >>>>Hi, >>>> >>>>I have an Access97 table containing binary fields (gifs) that I would >>>>like to migrate to an innodb format via MyODBC (2.50.39) and MySQL-Max >>>>(3.23.44). The MySQL query log shows binary-like characters being >>>>received but there never seems to be progression to the next record. >>>>After 5 or so minutes, the append query in Access97 quits and an ODBC >>>>error window complaining about a lost connection pops-up. When I >>>>convert the table to myisam type, the append query works. Is there >>>>something in my.cnf I need to adjust to fix thisproblem? >>> >>>inserting binary BLOBs should work. What is an 'append' query in MS >>>Access? >> Is it translated to an INSERT in MySQL? Could you paste a copy of what >> the MySQL query logs shows? > >The create statement from the query log is: > >CREATE TABLE tbl_boards(BID INT NOT NULL,_Group INT NULL,Code CHAR(10) >NULL,Description CHAR(50) NULL,Board_Size CHAR(50) NULL,Product_Line >CHAR(30) NULL,Featuring CHAR(255) NULL,Colour CHAR(255) NULL,Picture >LONGBLOB NULL,Label LONGBLOB NULL, PRIMARY KEY (BID)); > >and the INSERT statement is: > >INSERT INTO `tbl_boards` >(`BID`,`_Group`,`Code`,`Description`,`Board_Size`,`Product_Line`,`Picture`, `Label`)VALUES (1,1,'1','Colour Board - Glasstyle','450mm x 450mm x >6mm','Glasstyle','^U^\3\0^B\0\0\0^S\0^L\0^T\0\'\0˙˙˙˙Photo Editor >Photo\0MSPhotoEd.3\0^A^E\0\0^B\0\.... etc. > >>> >>>How big is the BLOB? What is the CREATE TABLE statement? Note that a >>>BLOB >> bigger than > 64 kB needs to be defined a MEDIUMBLOB or LONGBLOB in the >> CREATE TABLE statement. > >The blobs are about 250-400K each, the average row length is 850K and the >max_allowed_packet is 1M. It could be that some of the records are >1M. I >will check that. However, as I mentioned before, the INSERT works with >myisam tables. > >>> >>>If you try inserting an ASCII text file does that work? >>> >>>How big you have set >>> >>>max_allowed_packet >>> >>>in my.cnf? That restricts the size of rows communicated from a client >>>to >> the server. >>> >>>>Thanks, >>>>Stephen >>> >>>Regards, >>> >>>Heikki >>>http://www.innodb.com >> >> actually, are you running MySQL on Windows? Then the problem might be >> the bug introduced in 3.23.42: to access InnoDB tables you must use the >> same case of letters in the database name as you used in the CREATE >> TABLE statement. Make sure you consistently use lower case in database >> names. The bug is fixed in upcoming 3.23.45. >> >> Does mysql.err contain anything? >> > >I am running mysql-max on Linux (RedHat7.1) and mysql.err does not have any >error-related messages. > >> Regards, >> >> Heikki >> >Thanks, >Stephen
how do you communicate the binary strings to MySQL? From the manual I found: " If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences: NUL ASCII 0. You should represent this by `\0' (a backslash and an ASCII `0' character). \ ASCII 92, backslash. Represent this by `\\'. ' ASCII 39, single quote. Represent this by `\''. " ASCII 34, double quote. Represent this by `\"'. If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT statement. See section 8.4.2 C API Function Overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 8.2.2 The DBI Interface. You should use an escape function on any string that might contain any of the special characters listed above! " Try also inserting rows to your table from the mysql command line client. Does that work? Does MyODBC work if you insert simple ASCII strings (not binary strings) in place of the BLOBs: 'jhghjghgjgjgjkgkjhhj'. Regards, Heikki --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
