On Fri, 2003-12-26 at 19:26, Angus March wrote:
> I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very
> vague error: something about a problem "near '' on line 1". I'm forumating
> the query string w/
>
> sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body)
> VALUES (%ld,%ld,", sessionID,fileType);
>
> Then w/calls to things like memcpy, and unformatted reads from a stream, I
> append the blob to the end of that, and finalize it w/a ')'. I'm very
> careful about what I do w/my pointers, and not to use string-handling
> functions on the blob data. I also use mysql_real_query(), which is where
> the error is returned. From what I can tell from the on-line documentation,
> this is the right way to handle blobs. I also tried:
>
> sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body)
> VALUES (%ld,%ld,0)", sessionID,fileType); //exact same as before, but the
> blob is a mere '0'
>
> Is this right? I can use a char * to point to binary data, can't I?
> I'm running Linux RH9, if that makes a difference.
If you are using MySQL 4.1, it would be a lot easier to use "Prepared
SQL statements" as outlined in section 11.1.4 of the manual, which
allows you to pass variable parts of queries in a length-specified
binary format, rather than requiring that all the data in the query be
escaped for MySQL.
Short of using 4.1, you could do something like you've done above with
sprintf but, but with the whole query. I find it a little easier to be
sure that the whole query is valid by having the entire query in a
single string rather than trying to build the query by appending
successive parts. In this case, you can also test that the query works
with data that doesn't need to be escaped without butchering your code
(you can just change the format string in the snprintf to some literal
data to verify that it works.
-->8-----pseudo-code------8<----
char *blob_data = "some big buffer of blob data";
unsigned long blob_data_length = you-should-know-this-value;
blob_data_escaped = (char *) malloc(blob_data_length * 2+1);
escaped_length = mysql_real_escape_string(
(MYSQL *) mysql,
(char *) blob_data_escaped,
(const char *) blob_data,
(unsigned long) blob_data_length);
/*
blob_data_escaped[escaped_length] should be the only null byte
in blob_data_escaped now, so the snprintf below should work just
fine
*/
query_length = some-huge-amount-that-can-hold-
escaped_length-plus-the-rest-of-the-query;
query = (char *)malloc(query_length);
snprintf(query,
query_length,
"insert into s (i,f,b) values (%d, %f, '%s')",
some_integer,
some_float,
blob_data);
-->8-----pseudo-code------8<----
See the query string? Other than the fact it contains the printf escape
sequences, it's a syntacticly correct query, closing parens and all.
You could change that to:
snprintf(query,
query_length,
"insert into s (i,f,b) values (%d, %f, '%s')",
1,
2.0,
"blob\\'test\\0has embedded null byte");
to make sure the query is syntacticly correct. If you print out the
query (so you know what it is), then try pasting it into the mysql
client, you should be able to run it without problems (as a way to test
where there are problems with it). The C library functions that print
stuff out are bound by the same string-interpretation limits that the
MySQL library is, so if your query comes up short, or syntacticly
incorrect due to those limitations, you'll see it.
You could also abstract this out the pseudo-code I've provided above to
a function that formats a query for you if you give it all the data
values and the lengths of the input buffers and whatnot (thereby making
it work somewhat more like the prepared statement support in 4.1).
(BTW, you should get in the habit of using snprintf rather than sprintf,
if your platform supports it, in order to avoid buffer overruns).
--
Andy Bakun <[EMAIL PROTECTED]>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
