Home » U++ Library support » U++ SQL » MSSQL binary data
| MSSQL binary data [message #19224] |
Fri, 21 November 2008 15:24  |
 |
mirek
Messages: 14255
Registered: November 2005
|
Ultimate Member |
|
|
Well, I needed to store image files into MSSQL and found we have no real support there yet...
I guess this should be addressed soon, anyway, for now, here is very simple function to workaround the insert/update (fetch seems to work):
SqlVal MsSqlBinary(const String& data)
{
return SqlVal("convert(varbinary(max), '0x" + HexString(data) + "', 1)", SqlS::FN);
}
Mirek
[Updated on: Fri, 21 November 2008 15:25] Report message to a moderator |
|
|
|
| Re: MSSQL binary data [message #19228 is a reply to message #19224] |
Fri, 21 November 2008 21:23  |
|
mirek
Messages: 14255
Registered: November 2005
|
Ultimate Member |
|
|
| luzr wrote on Fri, 21 November 2008 09:24 |
Well, I needed to store image files into MSSQL and found we have no real support there yet...
I guess this should be addressed soon, anyway, for now, here is very simple function to workaround the insert/update (fetch seems to work):
SqlVal MsSqlBinary(const String& data)
{
return SqlVal("convert(varbinary(max), '0x" + HexString(data) + "', 1)", SqlS::FN);
}
Mirek
|
Well, this simpler variant works too:
SqlVal MsSqlBinary(const String& data)
{
return SqlVal("0x" + HexString(data), SqlS::HIGH);
}
Interestingly, it seems like similar method should work for Oracle and MySql as well:
http://www.herongyang.com/jdbc/Oracle-BLOB-SQL-INSERT.html
Something to add to SqlExp. So far, with Oracle, I was always using SqlRaw, but that means no SqlExp...
Mirek
|
|
|
|
|
|
|
|
|
|
|
|
| Re: MSSQL binary data [message #22812 is a reply to message #19274] |
Tue, 18 August 2009 16:18 |
|
|
Can anybody make a simple example how to insert and select binary data from table?
I'm interesting in Oracle, Postgres and sqlite.
Sorry for my incopetence.
Thanks in advance.
Ion Lupascu.
Add:
I found that it can add as string in the folowing format:
INSERT INTO Image
(ID, Subject, Body)
VALUES (1, 'some subject'
, 'C9CBBBCCCEB9C8CABCCCCEB9C9CBBB') //Oracle format
//, 0xC9CBBBCCCEB9C8CABCCCCEB9C9CBBB) //SQL Server format
//, x'C9CBBBCCCEB9C8CABCCCCEB9C9CBBB') // MySQL format
WHERE C9CBBBCCCEB9C8CABCCCCEB9C9CBBB is binary data.
| luzr wrote on Sun, 23 November 2008 23:28 |
| captainc wrote on Sun, 23 November 2008 14:55 |
Those are good benefits. I'm interested, what kind of database do you use for binary data? What kind of settings do you set on the database? What is the schema like?
|
Ehm, usually I use DB per customer request.
Over time, I was storing binary data into Oracle, PGSQL and now in MSSQL. In fact, any single commercial app I have ever developed did it.
No special setting on DB. It should handle it just fine.
Nothing special about the schema either. It is just large binary column, that is all.
In fact, so far the only 'special' thing about large binary data in DB was the way of moving those data to DB - SqlRaw and no SqlExp.
Mirek
|
[Updated on: Tue, 18 August 2009 16:29] Report message to a moderator |
|
|
|
| Re: MSSQL binary data [message #22837 is a reply to message #22812] |
Fri, 21 August 2009 14:18 |
|
mirek
Messages: 14255
Registered: November 2005
|
Ultimate Member |
|
|
| tojocky wrote on Tue, 18 August 2009 10:18 |
Can anybody make a simple example how to insert and select binary data from table?
I'm interesting in Oracle, Postgres and sqlite.
|
Except for oracle, you just place SqlBinary into your insert
SQL * Insert(IMAGE)(ID, id)(BODY, SqlBinary(data));
As for Oracle, this works too, but is hindered by oracle maximum statement length limit, which is ~32000 - means you can store only about 15KB.
Another option is to use SqlRaw, but in that case you cannot use SqlExp:
SQL.Execute("insert IMAGE(ID, BODY) values (?, ?)", id, SqlRaw(data));
This should work for all dbs... Usually, it is also more effective, but at the same time less convenient 
Mirek
[Updated on: Fri, 21 August 2009 14:20] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sun Apr 27 23:34:15 CEST 2025
Total time taken to generate the page: 0.01097 seconds
|
Members
Pages
Help
Register
Login
Home
