Quantcast

Inserting image into SQLite

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Inserting image into SQLite

chris.s
I'm struggling trying to work out how to insert an image into a sqlite db.

So far I've got this...

UIImage img =......
NSData data = img.AsPng();
using (var command = new SqliteCommand())
{
  command.Connection = ....
  command.CommandText = "insert into imagetable (iref, myimage) values (1, @imagedata)";
  command.Parameters.AddWithValue("@imagedata", data);

//  also tried this....
//  command.Parameters.Add("@imagedata");
//  command.Parameters[0].Size = (int)data.Length;
//   command.Parameters[0].Value = data;

  command.ExecuteScalar();
}

Gives me an exception..

"Argument cannot be null.\Parameter name: s"

The column is defined as an 'Image' datatype .. "create table imagetable(iref int, myimage image)"

"data" is definitely not null and the db connection is all fine.

Suggestions?

Chris
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

Nic Wise
Store it on the file system and refer to the name in the database?

images in databases are generally a bad idea.

On Tue, Apr 17, 2012 at 16:01, chris.s <[hidden email]> wrote:

> I'm struggling trying to work out how to insert an image into a sqlite db.
>
> So far I've got this...
>
> UIImage img =......
> NSData data = img.AsPng();
> using (var command = new SqliteCommand())
> {
>  command.Connection = ....
>  command.CommandText = "insert into imagetable (iref, myimage) values (1,
> @imagedata)";
>  command.Parameters.AddWithValue("@imagedata", data);
>
> //  also tried this....
> //  command.Parameters.Add("@imagedata");
> //  command.Parameters[0].Size = (int)data.Length;
> //   command.Parameters[0].Value = data;
>
>  command.ExecuteScalar();
> }
>
> Gives me an exception..
>
> "Argument cannot be null.\Parameter name: s"
>
> The column is defined as an 'Image' datatype .. "create table
> imagetable(iref int, myimage image)"
>
> "data" is definitely not null and the db connection is all fine.
>
> Suggestions?
>
> Chris
>
> --
> View this message in context: http://monotouch.2284126.n4.nabble.com/Inserting-image-into-SQLite-tp4565016p4565016.html
> Sent from the MonoTouch mailing list archive at Nabble.com.
> _______________________________________________
> MonoTouch mailing list
> [hidden email]
> http://lists.ximian.com/mailman/listinfo/monotouch



--
Nic Wise
t.  +44 7788 592 806 | @fastchicken | http://www.linkedin.com/in/nicwise
b. http://www.fastchicken.co.nz/

Earnest: Self-employed? Track your business expenses and income.
http://earnestapp.com
Nearest Bus: find when the next bus is coming to your stop. http://goo.gl/Vcz1p
mobileAgent (for FreeAgent): get your accounts in your pocket.
http://goo.gl/IuBU
Trip Wallet: Keep track of your budget on the go: http://goo.gl/ePhKa
London Bike App: Find the nearest Boris Bike, and get riding! http://goo.gl/Icp2
_______________________________________________
MonoTouch mailing list
[hidden email]
http://lists.ximian.com/mailman/listinfo/monotouch
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

AnthonyLambert
In reply to this post by chris.s
http://stackoverflow.com/questions/5039343/save-image-data-to-sqlite-database-in-iphone

This is an article on how to do it in Objective C.... You just need to convert it to C# Monotouch.

The process will be mostly the same.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

Nic Wise
Chris, which SqLite lib are you using? You may find the problem is
that it doesn't deal with @paramname - sqlite uses ? internally....?



On Tue, Apr 17, 2012 at 16:38, AnthonyLambert <[hidden email]> wrote:

> http://stackoverflow.com/questions/5039343/save-image-data-to-sqlite-database-in-iphone
> http://stackoverflow.com/questions/5039343/save-image-data-to-sqlite-database-in-iphone
>
> This is an article on how to do it in Objective C.... You just need to
> convert it to C# Monotouch.
>
> The process will be mostly the same.
>
>
> --
> View this message in context: http://monotouch.2284126.n4.nabble.com/Inserting-image-into-SQLite-tp4565016p4565155.html
> Sent from the MonoTouch mailing list archive at Nabble.com.
> _______________________________________________
> MonoTouch mailing list
> [hidden email]
> http://lists.ximian.com/mailman/listinfo/monotouch



--
Nic Wise
t.  +44 7788 592 806 | @fastchicken | http://www.linkedin.com/in/nicwise
b. http://www.fastchicken.co.nz/

Earnest: Self-employed? Track your business expenses and income.
http://earnestapp.com
Nearest Bus: find when the next bus is coming to your stop. http://goo.gl/Vcz1p
mobileAgent (for FreeAgent): get your accounts in your pocket.
http://goo.gl/IuBU
Trip Wallet: Keep track of your budget on the go: http://goo.gl/ePhKa
London Bike App: Find the nearest Boris Bike, and get riding! http://goo.gl/Icp2
_______________________________________________
MonoTouch mailing list
[hidden email]
http://lists.ximian.com/mailman/listinfo/monotouch
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

chris.s
In reply to this post by AnthonyLambert
Those were about the only samples I could find. To the best of my 'c' knowledge my approach was the same, but the devil may be in the detail.

Thanks anyhow
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

chris.s
In reply to this post by Nic Wise
I'm just using what my mono touch is defaulting to, without checking I don't know specifically what lib I am using. I will checkout your alternative syntax in the morning, tho using my approach for an 'int' column works fine.

I might just save the images to local storage if it is problematic.

Thanks

Chris
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

Nic Wise
I've been using sqlite-net - works great.

On Tue, Apr 17, 2012 at 18:01, chris.s <[hidden email]> wrote:

> I'm just using what my mono touch is defaulting to, without checking I don't
> know specifically what lib I am using. I will checkout your alternative
> syntax in the morning, tho using my approach for an 'int' column works fine.
>
> I might just save the images to local storage if it is problematic.
>
> Thanks
>
> Chris
>
> --
> View this message in context: http://monotouch.2284126.n4.nabble.com/Inserting-image-into-SQLite-tp4565016p4565407.html
> Sent from the MonoTouch mailing list archive at Nabble.com.
> _______________________________________________
> MonoTouch mailing list
> [hidden email]
> http://lists.ximian.com/mailman/listinfo/monotouch



--
Nic Wise
t.  +44 7788 592 806 | @fastchicken | http://www.linkedin.com/in/nicwise
b. http://www.fastchicken.co.nz/

Earnest: Self-employed? Track your business expenses and income.
http://earnestapp.com
Nearest Bus: find when the next bus is coming to your stop. http://goo.gl/Vcz1p
mobileAgent (for FreeAgent): get your accounts in your pocket.
http://goo.gl/IuBU
Trip Wallet: Keep track of your budget on the go: http://goo.gl/ePhKa
London Bike App: Find the nearest Boris Bike, and get riding! http://goo.gl/Icp2
_______________________________________________
MonoTouch mailing list
[hidden email]
http://lists.ximian.com/mailman/listinfo/monotouch
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

cbailey
In reply to this post by chris.s
I've not used SQLite and haven't used ADO.NET for some time but I've not seen ExecuteScalar used to perform an Insert statement. Shouldn't you be using ExecuteNonQuery ?

As I say I'm not an expert but might be worth a try.

Cheers,
Curtis
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

chris.s
In reply to this post by chris.s
OK, a bit more digging and I think the problem actually might be with this bit of code...

NSData data = img.AsPng();

Inspecting 'data' after this statement, it shows as being 'null', tho it's Length property is set to a value.

Tweaking the code to ...

NSData data = NSData.FromString("Bob Says Hello")

... and the insert works.

img.SaveToPhotosAlbum() works as expected, so img definitely has a valid image.

I'd really rather not save the file to disk, the images are sent back to a central server and removed from the device. I already have database synching working and this would take care of it all without further changes.

Any more ideas?

Chris

PS ExecuteScalar and ExecuteNonQuery both work fine.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

Nic Wise
Any ideas why img.AsPng doesn't work then? I use img.AsJpeg a lot, and
it's been working fine.

Memory issue maybe? Can you round-trip the image? ie load it, save it
to a NSData (as png). Load it from the NSData, show it?



On Wed, Apr 18, 2012 at 16:29, chris.s <[hidden email]> wrote:

> OK, a bit more digging and I think the problem actually might be with this
> bit of code...
>
> NSData data = img.AsPng();
>
> Inspecting 'data' after this statement, it shows as being 'null', tho it's
> Length property is set to a value.
>
> Tweaking the code to ...
>
> NSData data = NSData.FromString("Bob Says Hello")
>
> ... and the insert works.
>
> img.SaveToPhotosAlbum() works as expected, so img definitely has a valid
> image.
>
> I'd really rather not save the file to disk, the images are sent back to a
> central server and removed from the device. I already have database synching
> working and this would take care of it all without further changes.
>
> Any more ideas?
>
> Chris
>
> PS ExecuteScalar and ExecuteNonQuery both work fine.
>
>
> --
> View this message in context: http://monotouch.2284126.n4.nabble.com/Inserting-image-into-SQLite-tp4565016p4568057.html
> Sent from the MonoTouch mailing list archive at Nabble.com.
> _______________________________________________
> MonoTouch mailing list
> [hidden email]
> http://lists.ximian.com/mailman/listinfo/monotouch



--
Nic Wise
t.  +44 7788 592 806 | @fastchicken | http://www.linkedin.com/in/nicwise
b. http://www.fastchicken.co.nz/

Earnest: Self-employed? Track your business expenses and income.
http://earnestapp.com
Nearest Bus: find when the next bus is coming to your stop. http://goo.gl/Vcz1p
mobileAgent (for FreeAgent): get your accounts in your pocket.
http://goo.gl/IuBU
Trip Wallet: Keep track of your budget on the go: http://goo.gl/ePhKa
London Bike App: Find the nearest Boris Bike, and get riding! http://goo.gl/Icp2
_______________________________________________
MonoTouch mailing list
[hidden email]
http://lists.ximian.com/mailman/listinfo/monotouch
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

chris.s
This post was updated on .
Thanks for the reply.

'Going round the houses' does work, even tho 'data' reports as being null despite having a valid Length property, so AsPNG() is fine other than data = null afterwards.

The actual image I'm saving is a 'captured signature', but I've eliminated any problem with that by loading an existing image on the device using...

NSData data = UIImage.FromFile(@"sampleimage.png").AsPNG();

which gives the same error sql error.

I'm now wondering if the setting of the parameter value using ...

command.Parameters[0].Value = data;

... is assigning null to the parameter value and causing the sql error since 'data' shows as being null despite it containing valid image data, especially when you recall the error is 'Argument cannot be null'.

Chris



 


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite

Nic Wise
It could also be that the SQLParameter system doesn't understand
NSData. It's just a byte[] under the hood, but you might have to do
something to pull that out.

On Wed, Apr 18, 2012 at 17:31, chris.s <[hidden email]> wrote:

> Thanks for the reply.
>
> 'Going round the houses' does work, even tho 'data' reports as being null
> despite having a valid Length property.
>
> The actual image I'm saving is a 'captured signature', but I've eliminated
> any problem with that by loading an existing image on the device using...
>
> NSData data = UIImage.FromFile(@"sampleimage.png").AsPNG();
>
> which gives the same error sql error.
>
> I'm now wondering if the setting of the parameter value using ...
>
> command.Parameters[0].Value = data;
>
> ... is assigning null to the parameter value and causing the sql error since
> 'data' shows as being null despite it containing valid image data,
> especially when you recall the error is 'Argument cannot be null'.
>
> Chris
>
>
>
>
>
>
>
>
> --
> View this message in context: http://monotouch.2284126.n4.nabble.com/Inserting-image-into-SQLite-tp4565016p4568237.html
> Sent from the MonoTouch mailing list archive at Nabble.com.
> _______________________________________________
> MonoTouch mailing list
> [hidden email]
> http://lists.ximian.com/mailman/listinfo/monotouch



--
Nic Wise
t.  +44 7788 592 806 | @fastchicken | http://www.linkedin.com/in/nicwise
b. http://www.fastchicken.co.nz/

Earnest: Self-employed? Track your business expenses and income.
http://earnestapp.com
Nearest Bus: find when the next bus is coming to your stop. http://goo.gl/Vcz1p
mobileAgent (for FreeAgent): get your accounts in your pocket.
http://goo.gl/IuBU
Trip Wallet: Keep track of your budget on the go: http://goo.gl/ePhKa
London Bike App: Find the nearest Boris Bike, and get riding! http://goo.gl/Icp2
_______________________________________________
MonoTouch mailing list
[hidden email]
http://lists.ximian.com/mailman/listinfo/monotouch
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting image into SQLite - Fixed!

chris.s
Ha, fixed it!

Firstly, since I found it worked if I did..

NSData data = NSdata.FromString("some text");

... I tried loading 'data' from various text and binary files on the disk. Text ones worked, binary didn't which suggested something about the binding.

After eating a bit of humble pie and paying closer attention to the exception details...

{System.ArgumentNullException: Argument cannot be null. Parameter name: s   at System.Text.Encoding.GetByteCount (System.String s) [0x00006] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System.Text/Encoding.cs:192    

at System.Text.UTF8Encoding.GetByteCount (System.String chars) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System.Text/UTF8Encoding.cs:912    

at Mono.Data.Sqlite.SqliteConvert.ToUTF8 (System.String sourceText) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteConvert.cs:76    

at Mono.Data.Sqlite.SQLite3.Bind_Text (Mono.Data.Sqlite.SqliteStatement stmt, Int32 index, System.String value) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:385    

at Mono.Data.Sqlite.SqliteStatement.BindParameter (Int32 index, Mono.Data.Sqlite.SqliteParameter param) [0x001b6] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteStatement.cs:217    

at Mono.Data.Sqlite.SqliteStatement.BindParameters () [0x0001c] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteStatement.cs:151  

at Mono.Data.Sqlite.SqliteCommand.BuildNextCommand () [0x000ac] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteCommand.cs:240 }

... this kind of back up that supposition.

Another look at the SqliteCommand.Parameter object shows it has a 'DBType' parameter, by default this was 'DBType.Object', I changed it to 'DBType.Binary' (not actually required), which then threw an exception when setting the parameter value, unable to cast data asbyte[], so by including System.Linq to provide an NSData.ToArray() (thanks Google) method and it worked!

So my final code.....

using System.Linq;

UIImage img =......
NSData data = img.AsPng();
using (var command = new SqliteCommand())
{
  command.Connection = ....
  command.CommandText = "insert into imagetable (iref, myimage) values (1, @imagedata)";

  command.Parameters.Add("@imagedata");
  command.Parameters[0].Size = (int)data.Length;
  command.Parameters[0].Value = data.ToArray();

  command.ExecuteScalar();
}


At the end of the day, it was just the way I was assigning data to the parameter value that needed attention.

Thanks for your help, we got there in the end!

Chris


Loading...