Re: [sqlite] sqlite3_randomness Quality
Hi Dr Hipp.
Thanks for the qualitative reply. I have seen comments about RC4 being biased
towards the first few bytes (some suggestions are to drop the first n bytes
etc). I just don't have the confidence from the replies that it is usable
as-is. You have convinced me of the repetition rate but, that aside, it is also
that the seed method on different platforms could be a cause of concern (a prng
will give the same sequence with the same seed). As said previously. On VxWorks
<6.3 this seed was always zero using the method defined (note this was with
older SQLite versions < 3.7.14). The plus here is that the RC4 algo is
initialised only once so subsequent calls produce new values even if they are
predictable. So based on the replies here (which I greatly appreciate), I have
decided to hash the sqlite random with the normal rand() then use that as the
seed for the FORTUNA algo to mitigate this scenario. That should then ensure
that the seed is always different.even if
the OS specific random function isn't that random (or indeed 0).
Regards
Udon Shaun
From: Richard Hipp
To: Udon Shaun ; General Discussion of SQLite Database
Sent: Sunday, May 26, 2013 12:02 PM
Subject: Re: [sqlite] sqlite3_randomness Quality
The PRNG in SQLite is RC4. That means that the output never repeats. (The
output of RC4 does repeat, in theory, but the repeats do not happen in your
lifetime. The cycle time is on the order of 10^100). The output is uniform
and unbiased - white noise. There are attacks against RC4 but all such attacks
involve analysis of multiple sessions using the same initial key. But the key
is never reused in SQLite, so those attacks are not applicable.
On Sun, May 26, 2013 at 2:54 AM, Udon Shaun wrote:
Hi Simon.
>
>>That's 2^64 values.
>That's not the same as the repetition/period. Also.sqlite3_randomness can
>supply up to n bytes so isn't limited to 8 bytes.
>
>>"You're not going to catch any one of us saying here publicly "This is
>good enough for all cryptographic purposes." since we have no intention
>of being implicated in some disaster."
>
>"Cryptographically secure PRNG" has specific test criteria so it's not
>unreasonable to ask whether it meets the spec or not (there are a couple
>though NIST, BSI et. al.). I suppose what you are really saying is the work
>has not been done therefore the answer is "No, it is not a CSPRNG" and the
>repetition rate is unknown for all platforms. ("we don't know" is an
>acceptable answer by the way although I would have thought it had been looked
>at because of the SQLite Encryption Extension)
>
>Oh well. It was worth a try. Like I said, it would have been a great bonus.
>
>thanks for your input.
>Udon Shaun
>
>
>
>
>
> From: Simon Slavin
>To: Udon Shaun
>Sent: Friday, May 24, 2013 9:27 PM
>
>Subject: Re: [sqlite] sqlite3_randomness Quality
>
>
>
>
>On 24 May 2013, at 9:03pm, Udon Shaun wrote:
>
>> Ok. Good start. So we are talking about 2^24?
>
>
>
>"The random() function returns a pseudo-random integer between
>-9223372036854775808 and +9223372036854775807."
>
>That's 2^64 values.
>
>
>> Has any work been done to ascertain the quality on each platform, or is it
>> just a case of it "seems" good enough for locking and SQL on ALL platforms
>> as Simon seems to be saying. (urandom is, after all, as near to random as
>> can be expected from a machine so technically you wouldn't need an RC4 on
>> top for that platform)
>
>Although seeding is done differently on each platform (derived from values
>supplied by different OSen), as Doctor Hipp wrote the function that is seeded
>is the widely-used well-analysed function described here:
>
>
>
>Take a look. If you feel it's secure enough for your purposes, go ahead and
>use it. You're not going to catch any one of us saying here publicly "This is
>good enough for all cryptographic purposes." since we have no intention of
>being implicated in some disaster.
>
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_randomness Quality
On 5/29/2013 8:54 AM, Udon Shaun wrote: I have decided to hash the sqlite random with the normal rand() then use that as the seed for the FORTUNA algo to mitigate this scenario. That should then ensure that the seed is always different.even if the OS specific random function isn't that random (or indeed 0). rand() needs to be seeded, too. If one can predict the seed for SQLite RNG and for rand(), then one can also predict the seed for FORTUNA. More generally, it doesn't matter how long and complicated a chain of generators you string together - you cannot make entropy where there was none before. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How To Add an API to SQLite Wrappers page?
It says on the page http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers "If you know of a driver or wrapper for SQLite that is not listed below, please feel free to add it to the list." But I can't seem to find an "Edit" button. How do I go about adding an API to the list? Do I need a login (if so where do I get one)? Do I tell someone (who?) and they add it ? Thanks in advance. Udon Shaun ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
I am not getting your replies Keith; I'm new so maybe it's just delayed.
To clarify, I think this is a .NET Sqlite lib problem, not a database issue;
sorry for the confusion.
I am currently inserting the data with the Sqlit .NET library using sql with
a parameter of datetime type, so If the date is being stored in the wrong
format, I would argue that would be a bug the .NET Sqlite lib?
Also since the exception is occurring with-in the .NET Sqlite lib so the
coercion is happening in the .NET lib., seems weird because I can call
datetime.parse and it works, so am not sure what the lib is doing
differently.
I'm currently using version: 1.0.86.0 of the .NET lib.
Eric
-Original Message-
From: eschneider FE [mailto:eschnei...@frozenelephant.com]
Sent: Saturday, May 25, 2013 1:32 PM
To: 'sqlite-users@sqlite.org'
Subject: .net reading date fields not working?
Hello,
Having problem reading dates using a data reader. Looks like a bug in
SqLite?
Schema:
CREATE TABLE [Errors] (
"ErrorId" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"Date" datetime NOT NULL,
"Error" text(2147483647) NOT NULL COLLATE NOCASE
)
Data:
"1" "5/25/2013 1:18:20 PM" "Test 333 5/25/2013 1:18:20 PM"
"2" "5/25/2013 1:18:30 PM" "Test 333 5/25/2013 1:18:30 PM"
Code:
Public Shared Function ObtainColumnValue(ByVal column As
DatabaseColumn, ByVal reader As IDataReader) As Object
If column Is Nothing Then Throw New
ArgumentNullException("column")
If reader Is Nothing Then Throw New
ArgumentNullException("reader")
If column.DatabaseColumnIndex <= 0 Then
Try
column.DatabaseColumnIndex =
reader.GetOrdinal(column.DatabaseColumnName)
Catch ex As IndexOutOfRangeException
Throw New DatabaseColumnNotFoundException("Column: " +
column.DatabaseColumnName + " not found for property: " +
column.MappedPropertyName, ex)
End Try
End If
Return reader(column.DatabaseColumnIndex)
End Function
Error:
Test Name: TestSimpleQuery
Test FullName: SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery
Test Source:
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLiteUn
itTest.vb : line 290
Test Outcome: Failed
Test Duration: 0:00:00.04931
Result Message:
Test method SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery threw
exception:
System.FormatException: String was not recognized as a valid DateTime.
Result StackTrace:
at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider
provider, DateTimeStyles style)
at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText,
SQLiteDateFormats format, DateTimeKind kind)
at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32
index)
at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt,
SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
at
FrozenElephant.Symbiotic.DataReaderHelper.ObtainColumnValue(DatabaseColumn
column, IDataReader reader) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb:l
ine 147
at FrozenElephant.Symbiotic.DataReaderHelper.LoadItem[T](T newItem,
IDataReader reader, IList`1 columns) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb:l
ine 102
at
FrozenElephant.Symbiotic.DataReaderHelper.DataReaderToObject[T](IDataReader
reader, IList`1 columns) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb:l
ine 91
at
FrozenElephant.Symbiotic.ObjectLoader.ObtainItems[T](IDatabaseTypesFactory
factory, ISqlQuery sql) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\ObjectLoader.vb:line
468
at SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery() in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLiteUn
itTest.vb:line 296
Frozen Elephant Inc.
739 N. Thomphson Dr. #207
Madison, WI 53704
Eric Schneider
(414) 975-3582
eschnei...@frozenelephant.com
www.FrozenElephant.com
Re: [sqlite] Compiling SQLite for Windows Phone 8 (revised)
> However, running inside the WinRT sandbox will introduce some complications > for dynamic loading For whatever it’s worth, my use case seems to work okay. I’m building my own extension as a DLL and am able to deploy it with a test WP8 app, load it via sqlite3_load_extension, and successfully use its functionality (obviously using my own build of SQLite with extensions enabled). This use case also works fine for me on WinRT, though there I’m also able to use the official SQLite distribution since it doesn’t disable extensions. Thanks again Joe, I really appreciate your help! From: Joe Mistachkin Sent: May 28, 2013 6:36 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Compiling SQLite for Windows Phone 8 (revised) a...@sourcegear.com wrote: > > Why is the default to disable extensions on WP8? > I'm actually not sure at this point. Previously, it was disabled due to lack of the necessary OS APIs. > > Is there something about the platform that makes extensions not work? > If the extensions are statically linked, things may work fine. However, running inside the WinRT sandbox will introduce some complications for dynamic loading, see: http://msdn.microsoft.com/en-us/library/windows/desktop/hh447159%28v=vs.85%2 9.aspx -- Joe Mistachkin ___ 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] .net Sqlite lib reading date fields not working?
So when using System.Data.SQLite and inserting data using a parameter of
datetime, I believe the System.Data.SQLite .net lib is doing a ToString() on
datetime when it should be using a specific date format supported by SQlite.
-Original Message-
From: eschneider FE [mailto:eschnei...@frozenelephant.com]
Sent: Tuesday, May 28, 2013 1:36 PM
To: 'sqlite-users@sqlite.org'
Subject: RE: .net Sqlite lib reading date fields not working?
I am not getting your replies Keith; I'm new so maybe it's just delayed.
To clarify, I think this is a .NET Sqlite lib problem, not a database issue;
sorry for the confusion.
I am currently inserting the data with the Sqlit .NET library using sql with
a parameter of datetime type, so If the date is being stored in the wrong
format, I would argue that would be a bug the .NET Sqlite lib?
Also since the exception is occurring with-in the .NET Sqlite lib so the
coercion is happening in the .NET lib., seems weird because I can call
datetime.parse and it works, so am not sure what the lib is doing
differently.
I'm currently using version: 1.0.86.0 of the .NET lib.
Eric
-Original Message-
From: eschneider FE [mailto:eschnei...@frozenelephant.com]
Sent: Saturday, May 25, 2013 1:32 PM
To: 'sqlite-users@sqlite.org'
Subject: .net reading date fields not working?
Hello,
Having problem reading dates using a data reader. Looks like a bug in
SqLite?
Schema:
CREATE TABLE [Errors] (
"ErrorId" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"Date" datetime NOT NULL,
"Error" text(2147483647) NOT NULL COLLATE NOCASE
)
Data:
"1" "5/25/2013 1:18:20 PM" "Test 333 5/25/2013 1:18:20 PM"
"2" "5/25/2013 1:18:30 PM" "Test 333 5/25/2013 1:18:30 PM"
Code:
Public Shared Function ObtainColumnValue(ByVal column As
DatabaseColumn, ByVal reader As IDataReader) As Object
If column Is Nothing Then Throw New
ArgumentNullException("column")
If reader Is Nothing Then Throw New
ArgumentNullException("reader")
If column.DatabaseColumnIndex <= 0 Then
Try
column.DatabaseColumnIndex =
reader.GetOrdinal(column.DatabaseColumnName)
Catch ex As IndexOutOfRangeException
Throw New DatabaseColumnNotFoundException("Column: " +
column.DatabaseColumnName + " not found for property: " +
column.MappedPropertyName, ex)
End Try
End If
Return reader(column.DatabaseColumnIndex)
End Function
Error:
Test Name: TestSimpleQuery
Test FullName: SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery
Test Source:
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLiteUn
itTest.vb : line 290
Test Outcome: Failed
Test Duration: 0:00:00.04931
Result Message:
Test method SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery threw
exception:
System.FormatException: String was not recognized as a valid DateTime.
Result StackTrace:
at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider
provider, DateTimeStyles style)
at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText,
SQLiteDateFormats format, DateTimeKind kind)
at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32
index)
at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt,
SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
at
FrozenElephant.Symbiotic.DataReaderHelper.ObtainColumnValue(DatabaseColumn
column, IDataReader reader) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb:l
ine 147
at FrozenElephant.Symbiotic.DataReaderHelper.LoadItem[T](T newItem,
IDataReader reader, IList`1 columns) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb:l
ine 102
at
FrozenElephant.Symbiotic.DataReaderHelper.DataReaderToObject[T](IDataReader
reader, IList`1 columns) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\DataReaderHelper.vb:l
ine 91
at
FrozenElephant.Symbiotic.ObjectLoader.ObtainItems[T](IDatabaseTypesFactory
factory, ISqlQuery sql) in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\ObjectLoader.vb:line
468
at SqlLiteTestProject.SqlLiteUnitTest.TestSimpleQuery() in
D:\Dev\FrozenElephant\SymbioticORM\Symbiotic\SqlLiteTestProject_VB\SqlLiteUn
itTest.vb:line 296
Frozen Elephant Inc.
739 N. Thomphson Dr. #207
Madison, WI 53704
Eric Schneider
(414) 975-3582
eschnei...@frozenelephant.com
www.FrozenElephant.com
Re: [sqlite] sqlite3_randomness Quality
HI Ivor. > it doesn't matter how long and complicated a chain of > generators you string together - you cannot make entropy where there was >none before. Agreed. However I have control over what rand() is seeded with (without hacking the SQLite codebase - that would be sacrilege) and have verified it is definitely not zero and not "similar" on each invocation for all platforms. The proof, however, will be after I run the chain through the full suite of NIST and "DieHarder" tests (didn't want to if I could get away with it). Initial "quick" statistical tests (on windows7) seem to indicate it should do well and ENT (http://www.fourmilab.ch/random/) agrees (Entropy 7.9991, ChiSQ 257.532, Exceed 44.38%). Udon Shaun - Message: 13 Date: Wed, 29 May 2013 09:11:56 -0400 From: Igor Tandetnik To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3_randomness Quality Message-ID: Content-Type: text/plain; charset=UTF-8; format=flowed On 5/29/2013 8:54 AM, Udon Shaun wrote: > I have decided to hash the sqlite random with the normal rand() then use that as the seed for the FORTUNA algo to mitigate this scenario. That should then ensure that the seed is always different.even if > the OS specific random function isn't that random (or indeed 0). rand() needs to be seeded, too. If one can predict the seed for SQLite RNG and for rand(), then one can also predict the seed for FORTUNA. More generally, it doesn't matter how long and complicated a chain of generators you string together - you cannot make entropy where there was none before. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_randomness Quality
On 5/29/2013 5:24 PM, Udon Shaun wrote: it doesn't matter how long and complicated a chain of generators you string together - you cannot make entropy where there was none before. Agreed. However I have control over what rand() is seeded with (without hacking the SQLite codebase - that would be sacrilege) and have verified it is definitely not zero and not "similar" on each invocation for all platforms. The proof, however, will be after I run the chain through the full suite of NIST and "DieHarder" tests (didn't want to if I could get away with it). Initial "quick" statistical tests (on windows7) seem to indicate it should do well and ENT (http://www.fourmilab.ch/random/) agrees (Entropy 7.9991, ChiSQ 257.532, Exceed 44.38%). If you know how to securely seed rand(), why don't you just seed Fortuna directly? And what's the place of SQLite in this picture? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote:
>
> So when using System.Data.SQLite and inserting data using a parameter of
> datetime, I believe the System.Data.SQLite .net lib is doing a ToString()
on
> datetime when it should be using a specific date format supported by
SQlite.
>
The DateTime parsing and formatting performed by System.Data.SQLite are
governed
by the DateTimeKind and DateTimeFormat properties provided in the connection
string.
The default values are "DateTimeKind.Unspecified" and
"SQLiteDateFormats.ISO8601",
respectively.
When using the ISO-8601 format, the date/time strings must conform to one of
the
"well-known" formats supported by System.Data.SQLite, specifically:
///
/// An array of ISO-8601 DateTime formats that we support parsing.
///
private static string[] _datetimeFormats = new string[] {
"THHmmssK",
"THHmmK",
"HH:mm:ss.FFFK",
"HH:mm:ssK",
"HH:mmK",
"-MM-dd HH:mm:ss.FFFK", /* NOTE: UTC default (5). */
"-MM-dd HH:mm:ssK",
"-MM-dd HH:mmK",
"-MM-ddTHH:mm:ss.FFFK",
"-MM-ddTHH:mmK",
"-MM-ddTHH:mm:ssK",
"MMddHHmmssK",
"MMddHHmmK",
"MMddTHHmmssFFFK",
"THHmmss",
"THHmm",
"HH:mm:ss.FFF",
"HH:mm:ss",
"HH:mm",
"-MM-dd HH:mm:ss.FFF", /* NOTE: Non-UTC default (19). */
"-MM-dd HH:mm:ss",
"-MM-dd HH:mm",
"-MM-ddTHH:mm:ss.FFF",
"-MM-ddTHH:mm",
"-MM-ddTHH:mm:ss",
"MMddHHmmss",
"MMddHHmm",
"MMddTHHmmssFFF",
"-MM-dd",
"MMdd",
"yy-MM-dd"
};
The DateTime.ParseExact method is used by System.Data.SQLite; therefore, if
the
DateTime string does not conform to one of the above formats, an exception
will
be generated.
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
I understand that Joe, but if I'm inserting using parameters; the Sqlite
.net lib is determining the format not me the developer, I'm just passing in
a DateTime. In fact other people are complaining they cannot insert using
parameters; I assume in that case the specify the check constraint.
I would also state this is an ORM, and this code works for all databases
except Sqlite; Currently Sql Server, Oracle, and MySql. I have work around
the issue, but should not have to.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, May 29, 2013 4:37 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote:
>
> So when using System.Data.SQLite and inserting data using a parameter of
> datetime, I believe the System.Data.SQLite .net lib is doing a ToString()
on
> datetime when it should be using a specific date format supported by
SQlite.
>
The DateTime parsing and formatting performed by System.Data.SQLite are
governed
by the DateTimeKind and DateTimeFormat properties provided in the connection
string.
The default values are "DateTimeKind.Unspecified" and
"SQLiteDateFormats.ISO8601",
respectively.
When using the ISO-8601 format, the date/time strings must conform to one of
the
"well-known" formats supported by System.Data.SQLite, specifically:
///
/// An array of ISO-8601 DateTime formats that we support parsing.
///
private static string[] _datetimeFormats = new string[] {
"THHmmssK",
"THHmmK",
"HH:mm:ss.FFFK",
"HH:mm:ssK",
"HH:mmK",
"-MM-dd HH:mm:ss.FFFK", /* NOTE: UTC default (5). */
"-MM-dd HH:mm:ssK",
"-MM-dd HH:mmK",
"-MM-ddTHH:mm:ss.FFFK",
"-MM-ddTHH:mmK",
"-MM-ddTHH:mm:ssK",
"MMddHHmmssK",
"MMddHHmmK",
"MMddTHHmmssFFFK",
"THHmmss",
"THHmm",
"HH:mm:ss.FFF",
"HH:mm:ss",
"HH:mm",
"-MM-dd HH:mm:ss.FFF", /* NOTE: Non-UTC default (19). */
"-MM-dd HH:mm:ss",
"-MM-dd HH:mm",
"-MM-ddTHH:mm:ss.FFF",
"-MM-ddTHH:mm",
"-MM-ddTHH:mm:ss",
"MMddHHmmss",
"MMddHHmm",
"MMddTHHmmssFFF",
"-MM-dd",
"MMdd",
"yy-MM-dd"
};
The DateTime.ParseExact method is used by System.Data.SQLite; therefore, if
the
DateTime string does not conform to one of the above formats, an exception
will
be generated.
--
Joe Mistachkin
___
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] .net Sqlite lib reading date fields not working?
Ok; re-reading last and saw connection string. So adding ;DateTimeKind=
DateTimeKind.Utc to connection string works. Why isn't this the default?
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of eschneider FE
Sent: Wednesday, May 29, 2013 5:39 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?
I understand that Joe, but if I'm inserting using parameters; the Sqlite
.net lib is determining the format not me the developer, I'm just passing in
a DateTime. In fact other people are complaining they cannot insert using
parameters; I assume in that case the specify the check constraint.
I would also state this is an ORM, and this code works for all databases
except Sqlite; Currently Sql Server, Oracle, and MySql. I have work around
the issue, but should not have to.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, May 29, 2013 4:37 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote:
>
> So when using System.Data.SQLite and inserting data using a parameter of
> datetime, I believe the System.Data.SQLite .net lib is doing a ToString()
on
> datetime when it should be using a specific date format supported by
SQlite.
>
The DateTime parsing and formatting performed by System.Data.SQLite are
governed
by the DateTimeKind and DateTimeFormat properties provided in the connection
string.
The default values are "DateTimeKind.Unspecified" and
"SQLiteDateFormats.ISO8601",
respectively.
When using the ISO-8601 format, the date/time strings must conform to one of
the
"well-known" formats supported by System.Data.SQLite, specifically:
///
/// An array of ISO-8601 DateTime formats that we support parsing.
///
private static string[] _datetimeFormats = new string[] {
"THHmmssK",
"THHmmK",
"HH:mm:ss.FFFK",
"HH:mm:ssK",
"HH:mmK",
"-MM-dd HH:mm:ss.FFFK", /* NOTE: UTC default (5). */
"-MM-dd HH:mm:ssK",
"-MM-dd HH:mmK",
"-MM-ddTHH:mm:ss.FFFK",
"-MM-ddTHH:mmK",
"-MM-ddTHH:mm:ssK",
"MMddHHmmssK",
"MMddHHmmK",
"MMddTHHmmssFFFK",
"THHmmss",
"THHmm",
"HH:mm:ss.FFF",
"HH:mm:ss",
"HH:mm",
"-MM-dd HH:mm:ss.FFF", /* NOTE: Non-UTC default (19). */
"-MM-dd HH:mm:ss",
"-MM-dd HH:mm",
"-MM-ddTHH:mm:ss.FFF",
"-MM-ddTHH:mm",
"-MM-ddTHH:mm:ss",
"MMddHHmmss",
"MMddHHmm",
"MMddTHHmmssFFF",
"-MM-dd",
"MMdd",
"yy-MM-dd"
};
The DateTime.ParseExact method is used by System.Data.SQLite; therefore, if
the
DateTime string does not conform to one of the above formats, an exception
will
be generated.
--
Joe Mistachkin
___
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote: > > Ok; re-reading last and saw connection string. So adding ;DateTimeKind= > DateTimeKind.Utc to connection string works. Why isn't this the default? > Primarily because making it the default would alter the behavior in a very subtle and non-backward compatible way. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
The default is a non-working default though. So inserting using best practices of SQL statement & parameters does not work, and queries also. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, May 29, 2013 6:13 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Ok; re-reading last and saw connection string. So adding ;DateTimeKind= > DateTimeKind.Utc to connection string works. Why isn't this the default? > Primarily because making it the default would alter the behavior in a very subtle and non-backward compatible way. -- Joe Mistachkin ___ 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] .net Sqlite lib reading date fields not working?
Just realized I left in the work around code, the date issue is still a problem. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of eschneider FE Sent: Wednesday, May 29, 2013 6:26 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? The default is a non-working default though. So inserting using best practices of SQL statement & parameters does not work, and queries also. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, May 29, 2013 6:13 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Ok; re-reading last and saw connection string. So adding ;DateTimeKind= > DateTimeKind.Utc to connection string works. Why isn't this the default? > Primarily because making it the default would alter the behavior in a very subtle and non-backward compatible way. -- Joe Mistachkin ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
That's. For parsing, what about inserts? -Original Message- From: Joe Mistachkin Sent: 5/29/2013 9:57 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ 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] .net Sqlite lib reading date fields not working?
Eric Schneider wrote: > > That's. For parsing, what about inserts? > 5/25/2013 1:18:20 PM The new connection string property should apply to any operation that requires converting a string to a DateTime or vice-versa (e.g. parsing or formatting). When inserting a row using a parameterized INSERT statement and a strongly typed parameter containing a DateTime, the actual DateTime object will be converted to a string prior to being physically inserted into the database. The parameters governing this conversion process are the DateTimeFormat (enum), DateTimeKind (enum), and DateTimeFormatString (string). By default, the DateTimeFormat (enum) is set to ISO8601, which will not be suitable for strings with a format like "5/25/2013 1:18:20 PM" (i.e. from your original message). Here are some possible solutions to your issue: 1. Set the DateTimeFormat connection string property to CurrentCulture. This is much more relaxed than ISO8601. Set the DateTimeKind to either Utc or Local, depending on which time zone your DateTime values are from. 2. Set the DateTimeFormatString to something that will be able to parse the DateTime values you have (e.g. "M/d/ h:mm:ss tt"). Again, you will also want to set the DateTimeKind to either Utc or Local. 3. Combine #1 and #2. Set DateTimeFormat to CurrentCulture, DateTimeKind to Utc or Local, and DateTimeFormatString to "M/d/ h:mm:ss tt". If none of the above work, there are some additional troubleshooting steps you can take to determine EXACTLY how parameter values are being bound to your statement (e.g. via the LogBind connection flag, which can also be enabled via the connection string). The documentation contains full details on the possible connection flags and their effects. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .net Sqlite lib reading date fields not working?
That connection string mod doesn't seem to help. Let me know what you need from me to clarify. -Original Message- From: Joe Mistachkin Sent: 5/29/2013 9:57 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] .net Sqlite lib reading date fields not working? eschneider FE wrote: > > Just realized I left in the work around code, the date issue is still a > problem. > I'm still not sure exactly what the problem actually is. That being said, I've just added a new connection string property named DateTimeFormatString to give people full control over the exact format string that will be used to parse and format DateTime objects for a particular connection. Perhaps this will assist with your issue? The changes can be seen here: https://system.data.sqlite.org/index.html/info/4f933521a1 -- Joe Mistachkin ___ 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
[sqlite] I'm wondering how to use Sqlite3 API to make change on configuration
Sqlite3 site says users can fix the configuration, like *scratch memory size, shared_memory, page cache size* etc, by using Sqlite3 API(sqlite3_config). But I'm not familiar with using sqlite and dbt2(I'm doing my school project with dbt2 benchmark on sqlite). Before installing sqlite we first execute configure file. I think we can change some configuration by modifying source file and doing ./configure OR doing ./configure with some options. Is it right? Or should I have to go to dbt2 source code and find where the dbt2 execute the sqlite function or configuration? Or should I have to detect the default values some where in the sqlite source code and change that? If you tell me some way to change configurations, it will be very grateful. Thanks in advance. Hanbit Lee ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
