Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » MSSQL binary data
MSSQL binary data [message #19224] Fri, 21 November 2008 15:24 Go to next message
mirek is currently offline  mirek
Messages: 13975
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 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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 #19231 is a reply to message #19228] Sat, 22 November 2008 00:04 Go to previous messageGo to next message
captainc is currently offline  captainc
Messages: 278
Registered: December 2006
Location: New Jersey, USA
Experienced Member
What kind of use cases store images in sql relational databases?
Re: MSSQL binary data [message #19260 is a reply to message #19231] Sun, 23 November 2008 18:37 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
captainc wrote on Fri, 21 November 2008 18:04

What kind of use cases store images in sql relational databases?


In this case, pictures of employees Smile

Anyway, I always like to store raw data (like images or long documents) into central database. I removes at least two maintainance variables

- stored in DB, I do not need predefined network path

- DB is backuped, I need not to worry about separate backup or about restoring the same snapshot of files and DB data

Mirek
Re: MSSQL binary data [message #19266 is a reply to message #19260] Sun, 23 November 2008 20:55 Go to previous messageGo to next message
captainc is currently offline  captainc
Messages: 278
Registered: December 2006
Location: New Jersey, USA
Experienced Member
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?
Re: MSSQL binary data [message #19274 is a reply to message #19266] Sun, 23 November 2008 22:28 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
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
Re: MSSQL binary data [message #22812 is a reply to message #19274] Tue, 18 August 2009 16:18 Go to previous messageGo to next message
tojocky is currently offline  tojocky
Messages: 607
Registered: April 2008
Location: UK
Contributor

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 Go to previous message
mirek is currently offline  mirek
Messages: 13975
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 Smile

Mirek

[Updated on: Fri, 21 August 2009 14:20]

Report message to a moderator

Previous Topic: It is necessary BLOB field in MySQL
Next Topic: Possible SqlOption Problem [Solved]
Goto Forum:
  


Current Time: Thu Mar 28 12:48:46 CET 2024

Total time taken to generate the page: 0.01619 seconds