Re: [sqlite] System.Data.SQLite blob column exception
It is storing files of various types, some text and some with binary data. The code is generic to handle any type of file with any extension, some user defined. My C# code cannot accurately know in advance whether it is retrieving text or binary data. The result set could have multiples of text files and multiples of binary files in any order. If the first row contains a binary file, GetFieldType returns byte[] for all rows, text or binary, since it uses an internally cached value for that SQL statement. When checking this, GetSQLiteType pulls the SQLiteType from _fieldTypeArray[i] if that position is not null. Tim On Fri, May 6, 2011 at 11:40 AM, Robert Simpson wrote: > So you're always wanting to call GetBytes() even if the column is text? > What about calling GetFieldType() beforehand to determine whether or not to > call GetString() or GetBytes()? > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield > Sent: Friday, May 06, 2011 9:31 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] System.Data.SQLite blob column exception > > I am using a DbDataReader to return the columns for a SQL SELECT > statement. For the blob column, I am calling reader.GetOrdinal to get > the column number, calling reader.GetBytes to get the size of the > data, allocating the byte[], and then calling reader.GetBytes to read > into the buffer. > > Tim > > On Fri, May 6, 2011 at 11:12 AM, Robert Simpson > wrote: >> How are you fetching the data? Are you using the generic reader[x] on a >> datareader? Are you using a DataSet/DataTable? >> >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield >> Sent: Friday, May 06, 2011 8:58 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] System.Data.SQLite blob column exception >> >> On Fri, May 6, 2011 at 7:44 AM, Simon Slavin wrote: >>> >>> On 6 May 2011, at 3:43am, Tim Butterfield wrote: >>> >>>> The blob values are always inserted and read as a byte[]. If those >>>> bytes are those of a text file, it fails. If I add a leading 0x00 >>>> byte to force it to be binary, it works. >>> >>> Are you binding your parameters ? If so, check that you're using >> sqlite3_bind_blob() when binding something to your BLOB column. Binding > it >> as text will not trigger an error, since SQLite is quite happy to bind > text >> into a BLOB column. But if you take blobby data, and bind it using >> sqlite3_bind_blob() into a column defined as a BLOB SQLite should be >> retaining and reporting it as as blob, not as text. >>> >>> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than >> binding parameters, them make sure you're using the correct format to >> specify you have blob data (is it \x ?) rather than just supplying text. >> >> I'm using the System.Data.SQLite interface and not the direct C >> interface methods. All of my inserts are done using DbParameter (@p1, >> @p2, etc.) and DbCommand insert statements. The DbParameter.Value is >> the C# object type. In the case of the blob column, that type is a C# >> byte[], which sets the parameter type to DbType.Binary whether the >> first byte is 0x00 or a more textual value. >> >> Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
So you're always wanting to call GetBytes() even if the column is text? What about calling GetFieldType() beforehand to determine whether or not to call GetString() or GetBytes()? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield Sent: Friday, May 06, 2011 9:31 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] System.Data.SQLite blob column exception I am using a DbDataReader to return the columns for a SQL SELECT statement. For the blob column, I am calling reader.GetOrdinal to get the column number, calling reader.GetBytes to get the size of the data, allocating the byte[], and then calling reader.GetBytes to read into the buffer. Tim On Fri, May 6, 2011 at 11:12 AM, Robert Simpson wrote: > How are you fetching the data? Are you using the generic reader[x] on a > datareader? Are you using a DataSet/DataTable? > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield > Sent: Friday, May 06, 2011 8:58 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] System.Data.SQLite blob column exception > > On Fri, May 6, 2011 at 7:44 AM, Simon Slavin wrote: >> >> On 6 May 2011, at 3:43am, Tim Butterfield wrote: >> >>> The blob values are always inserted and read as a byte[]. If those >>> bytes are those of a text file, it fails. If I add a leading 0x00 >>> byte to force it to be binary, it works. >> >> Are you binding your parameters ? If so, check that you're using > sqlite3_bind_blob() when binding something to your BLOB column. Binding it > as text will not trigger an error, since SQLite is quite happy to bind text > into a BLOB column. But if you take blobby data, and bind it using > sqlite3_bind_blob() into a column defined as a BLOB SQLite should be > retaining and reporting it as as blob, not as text. >> >> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than > binding parameters, them make sure you're using the correct format to > specify you have blob data (is it \x ?) rather than just supplying text. > > I'm using the System.Data.SQLite interface and not the direct C > interface methods. All of my inserts are done using DbParameter (@p1, > @p2, etc.) and DbCommand insert statements. The DbParameter.Value is > the C# object type. In the case of the blob column, that type is a C# > byte[], which sets the parameter type to DbType.Binary whether the > first byte is 0x00 or a more textual value. > > Tim > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Tim Butterfield http://www.timbutterfield.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
I am using a DbDataReader to return the columns for a SQL SELECT statement. For the blob column, I am calling reader.GetOrdinal to get the column number, calling reader.GetBytes to get the size of the data, allocating the byte[], and then calling reader.GetBytes to read into the buffer. Tim On Fri, May 6, 2011 at 11:12 AM, Robert Simpson wrote: > How are you fetching the data? Are you using the generic reader[x] on a > datareader? Are you using a DataSet/DataTable? > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield > Sent: Friday, May 06, 2011 8:58 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] System.Data.SQLite blob column exception > > On Fri, May 6, 2011 at 7:44 AM, Simon Slavin wrote: >> >> On 6 May 2011, at 3:43am, Tim Butterfield wrote: >> >>> The blob values are always inserted and read as a byte[]. If those >>> bytes are those of a text file, it fails. If I add a leading 0x00 >>> byte to force it to be binary, it works. >> >> Are you binding your parameters ? If so, check that you're using > sqlite3_bind_blob() when binding something to your BLOB column. Binding it > as text will not trigger an error, since SQLite is quite happy to bind text > into a BLOB column. But if you take blobby data, and bind it using > sqlite3_bind_blob() into a column defined as a BLOB SQLite should be > retaining and reporting it as as blob, not as text. >> >> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than > binding parameters, them make sure you're using the correct format to > specify you have blob data (is it \x ?) rather than just supplying text. > > I'm using the System.Data.SQLite interface and not the direct C > interface methods. All of my inserts are done using DbParameter (@p1, > @p2, etc.) and DbCommand insert statements. The DbParameter.Value is > the C# object type. In the case of the blob column, that type is a C# > byte[], which sets the parameter type to DbType.Binary whether the > first byte is 0x00 or a more textual value. > > Tim > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Tim Butterfield http://www.timbutterfield.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
How are you fetching the data? Are you using the generic reader[x] on a datareader? Are you using a DataSet/DataTable? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield Sent: Friday, May 06, 2011 8:58 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] System.Data.SQLite blob column exception On Fri, May 6, 2011 at 7:44 AM, Simon Slavin wrote: > > On 6 May 2011, at 3:43am, Tim Butterfield wrote: > >> The blob values are always inserted and read as a byte[]. If those >> bytes are those of a text file, it fails. If I add a leading 0x00 >> byte to force it to be binary, it works. > > Are you binding your parameters ? If so, check that you're using sqlite3_bind_blob() when binding something to your BLOB column. Binding it as text will not trigger an error, since SQLite is quite happy to bind text into a BLOB column. But if you take blobby data, and bind it using sqlite3_bind_blob() into a column defined as a BLOB SQLite should be retaining and reporting it as as blob, not as text. > > If you're creating a fully text 'INSERT' or 'UPDATE' command rather than binding parameters, them make sure you're using the correct format to specify you have blob data (is it \x ?) rather than just supplying text. I'm using the System.Data.SQLite interface and not the direct C interface methods. All of my inserts are done using DbParameter (@p1, @p2, etc.) and DbCommand insert statements. The DbParameter.Value is the C# object type. In the case of the blob column, that type is a C# byte[], which sets the parameter type to DbType.Binary whether the first byte is 0x00 or a more textual value. Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
On 6 May 2011, at 4:58pm, Tim Butterfield wrote: > I'm using the System.Data.SQLite interface and not the direct C > interface methods. All of my inserts are done using DbParameter (@p1, > @p2, etc.) and DbCommand insert statements. The DbParameter.Value is > the C# object type. In the case of the blob column, that type is a C# > byte[], which sets the parameter type to DbType.Binary whether the > first byte is 0x00 or a more textual value. Okay, then I believe that your problem is with the System.Data.SQLite interface, which is acting in a way that the SQLite C library doesn't. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
On Fri, May 6, 2011 at 7:44 AM, Simon Slavin wrote: > > On 6 May 2011, at 3:43am, Tim Butterfield wrote: > >> The blob values are always inserted and read as a byte[]. If those >> bytes are those of a text file, it fails. If I add a leading 0x00 >> byte to force it to be binary, it works. > > Are you binding your parameters ? If so, check that you're using > sqlite3_bind_blob() when binding something to your BLOB column. Binding it > as text will not trigger an error, since SQLite is quite happy to bind text > into a BLOB column. But if you take blobby data, and bind it using > sqlite3_bind_blob() into a column defined as a BLOB SQLite should be > retaining and reporting it as as blob, not as text. > > If you're creating a fully text 'INSERT' or 'UPDATE' command rather than > binding parameters, them make sure you're using the correct format to specify > you have blob data (is it \x ?) rather than just supplying text. I'm using the System.Data.SQLite interface and not the direct C interface methods. All of my inserts are done using DbParameter (@p1, @p2, etc.) and DbCommand insert statements. The DbParameter.Value is the C# object type. In the case of the blob column, that type is a C# byte[], which sets the parameter type to DbType.Binary whether the first byte is 0x00 or a more textual value. Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
On 6 May 2011, at 3:43am, Tim Butterfield wrote: > The blob values are always inserted and read as a byte[]. If those > bytes are those of a text file, it fails. If I add a leading 0x00 > byte to force it to be binary, it works. Are you binding your parameters ? If so, check that you're using sqlite3_bind_blob() when binding something to your BLOB column. Binding it as text will not trigger an error, since SQLite is quite happy to bind text into a BLOB column. But if you take blobby data, and bind it using sqlite3_bind_blob() into a column defined as a BLOB SQLite should be retaining and reporting it as as blob, not as text. If you're creating a fully text 'INSERT' or 'UPDATE' command rather than binding parameters, them make sure you're using the correct format to specify you have blob data (is it \x ?) rather than just supplying text. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
On Thu, May 5, 2011 at 7:16 PM, Simon Slavin wrote: > > On 6 May 2011, at 12:17am, Tim Butterfield wrote: > >> Since my blob column can >> contain either text or binary file data, both cases are valid. Does >> VerifyType need to add a valid DbType.Binary for case >> TypeAffinity.Text or is something else going on here? > > SQLite allows any column to contain values of any type. If > SQLiteDataReader.VerifyType is to respect that then I don't think it has any > use at all. On the other hand, if you choose to use it with your database, > then presumably you should be careful not to put anything but binary data in > a BLOB column. The blob values are always inserted and read as a byte[]. If those bytes are those of a text file, it fails. If I add a leading 0x00 byte to force it to be binary, it works. It's quite a kludge, but I could do that to work around this limitation. Is this a requirement for storing the variable sized contents of a variety of file types or there something else I should consider? Here's the approximate schema of this table: stored_file: row_id integer ..., name text, contents blob Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
On Fri, May 6, 2011 at 2:16 AM, Simon Slavin wrote: > SQLite allows any column to contain values of any type. If > SQLiteDataReader.VerifyType is to respect that then I don't think it has any > use at all. On the other hand, if you choose to use it with your database, > then presumably you should be careful not to put anything but binary data in > a BLOB column > In the interest of cross-db portability, i've sometimes found that it's convenience check a column's type for both string or blob. e.g. MySQL reports TEXT fields as a BLOB but sqlite3 happily handles them as strings. Likewise when binding values - try to bind as a blob, and if that fails try as a string. If THAT fails, then fail the operation. Of course when working with non-xpm graphical data (or similar), binding as a string will have undefined results (in my case i'm just dealing with JSON data, so the encoding is predefined). -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite blob column exception
On 6 May 2011, at 12:17am, Tim Butterfield wrote: > Since my blob column can > contain either text or binary file data, both cases are valid. Does > VerifyType need to add a valid DbType.Binary for case > TypeAffinity.Text or is something else going on here? SQLite allows any column to contain values of any type. If SQLiteDataReader.VerifyType is to respect that then I don't think it has any use at all. On the other hand, if you choose to use it with your database, then presumably you should be careful not to put anything but binary data in a BLOB column. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite blob column exception
SQLiteDataReader.VerifyType thows an exception if it does not find
matching DbType and TypeAffinity. I found a case where a select on
several columns, one of which is type blob, had TypeAffinity.Text for
that column instead of TypeAffinity.Blob. If the column has binary
data, TypeAffinity.Blob(4) is returned from sqlite3_column_type as
expected. But, if the column has only text data and not binary data,
that function returns TypeAffinity.Text (3), which causes VerifyType
to fail to match and throw the exception. Since my blob column can
contain either text or binary file data, both cases are valid. Does
VerifyType need to add a valid DbType.Binary for case
TypeAffinity.Text or is something else going on here?
Thanks.
In my local copy of the VerifyType code, I made that change, but also
added explanatory data to the exception message to provide more info
as to what was happening.
case TypeAffinity.Text:
...
if (type == DbType.Binary) return affinity;
break;
...
}
throw new InvalidCastException(String.Format("Unexpected DbType: {0}
for Affinity: {1}", typ, affinity);
--
Tim Butterfield
http://www.timbutterfield.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
