not able to upload file when using DB_CONTENT
not able to upload file when using DB_CONTENT
I would like to upload the file contents using DB_CONTENT directly into my SQL Server database.
However I am confused about the datatype required by that field, is it binary (varbinary) or string (nvarchar)?
The documentation on doing this is a little sparse in my opinion.
Regardless of if I use varbinary or nvarchar, if I upload any file, the server returns an error but without any description as if it just aborts. No errors in the console or PHP logs, nothing.
I have traced the point of the abort to line 687 in Upload.php; $res = $q->exec();.
If i var_dump a variable before and after this call it only shows the former but not the latter.
My guess is it has to do with line 627; $q->set($column, file_get_contents($upload['tmp_name']));
This reads the file contents into a string but it doesn't play well with uploading to the database maybe because it contains some bad characters.
If I use datatype nvarchar(max) for the field, and change line 627 to:
$q->set( $column, base64_encode(file_get_contents($upload['tmp_name'])) );
it uploads correctly.
If I do this with datatype varbinary, the insert fails (but atleast with an error message) because cannot implicitely convert varchar to varbinary without a CONVERT statement.
so the question is, what is the correct method to use DB_CONTENT?
Answers
The PHP documentation states that
file_get_contentsis binary safe. I would have expectedvarbinaryto be the correct data type to use.What we really need is the error message with the rejection from the server.
Have you tried adding:
at the top of the script with the
Editor::inst(...)in it? That should hopefully get some error message into the response if it is coming from PHP. If it is coming from the SQL server it should be part of the JSON.Is it a 500 error it is returning?
Allan
I tried adding the error reporting, still no errors are shown other than:
A server error occurred while uploading the file.I see a couple of posts suggesting using
bin2hex()anddecbin()but that would just return another string which wouldn't upload into a varbinary field without a CONVERT.So far only been able to do this using
base64_encode().
So the fact that the response from the server is empty is why you are getting the "A server error occurred" message. Editor always expects valid JSON.
I don't know why that is happening though - I had thought that all code paths for Editor would result in a valid response, or error text if something fails!
It does sound like you'll need to work one of your workarounds at the moment I'm afraid. I'll try to reproduce the error here.
Allan
Ok sounds good, I will keep the
base64_encode()workaround for now.I tried some more debugging.
The binary data upload fails to provide meaningful error message for me, except if data() is called and the result shown using
var_dump.Then it shows a
102 SQL syntax errorand a malformed JSON return likelybecause it can't interpret the binary unless DB is in UTF8 collation.
As I am using a SQL Server 2014 version, which does not support UTF8, this upload fails.
Ahh! That's fantastic debugging - thank you! I'm not sure I'd ever have found that since we use SQL Server 2022 for our testing at the moment.
It sounds like the base64 encode is going to be the way to go for you if you need to store the file data in the db.
Allan