Re: [sqlite] .net Sqlite lib reading date fields not working?

[Eric Schneider]

Could treat it as datetime

-Original Message-
From: Joe Mistachkin
Sent: 5/30/2013 9:44 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?


eschneider FE 
>
> Solved it.
>

Great.  I'm curious, which connection string properties did you end up
using?

>
> You cannot use  parameter DbType.DateTime2 and must use DbType.DateTime
for
> the lib to format the date upon insert.
>

Right, DbType.DateTime2 is not recognized by System.Data.SQLite.

--
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?

[Joe Mistachkin]

eschneider FE 
>
> Solved it.
>

Great.  I'm curious, which connection string properties did you end up
using?

>
> You cannot use  parameter DbType.DateTime2 and must use DbType.DateTime
for
> the lib to format the date upon insert.
>

Right, DbType.DateTime2 is not recognized by System.Data.SQLite.

--
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?

[eschneider FE]

Solved it.

You cannot use  parameter DbType.DateTime2 and must use DbType.DateTime for
the lib to format the date upon insert.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of eschneider FE
Sent: Thursday, May 30, 2013 1:21 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] .net Sqlite lib reading date fields not working?

Joe,

So are you say we can specify the exact format string in the connection
string? 

How would that look? 
; DateTimeFormatString='XXX'

Would I need to download a new .NET API or Sqlite version?

Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, May 29, 2013 9:58 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-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?

[Joe Mistachkin]

eschneider FE wrote:
>
> So are you say we can specify the exact format string in the connection
> string? 
>

As of yesterday, yes.

>
> How would that look? 
> ; DateTimeFormatString='XXX'
>

Probably something like:

"Data
Source=test.db;DateTimeFormat=CurrentCulture;DateTimeKind=Utc;DateTimeFormat
String=M/d/ h:mm:ss tt;"

>
> Would I need to download a new .NET API or Sqlite version? 
>

Yes, the code is only available in the Fossil source code repository at the
moment.

--
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?

[eschneider FE]

Joe,

So are you say we can specify the exact format string in the connection
string? 

How would that look? 
; DateTimeFormatString='XXX'

Would I need to download a new .NET API or Sqlite version?

Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, May 29, 2013 9:58 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] PRAGMA reverse_unordered_selects and GROUP_CONCAT

[Clemens Ladisch]

Mark Lawrence wrote:
> I'm hitting an issue with ordering and GROUP_CONCAT that seems to
> depend on whether the reverse_unordered_selects pragma is enabled,
> and/or non-aggregate terms are also selected.
>
> reverse_unordered_select  with non-agg  result
>     --
> ONNoBad
> OFF   NoGood
> ONYes   Good
> OFF   Yes   Good
>
> The attached file should show some details.

The result is correct in all four cases.

 says:
| The order of the concatenated elements is arbitrary.


Regards,
Clemens
___
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]

I used a connection string for ;DateTimeKind= DateTimeKind.Utc but my
inserts are still incorrect:

"91","Eric","Schneider","Paul","11/27/1972 12:00:00 AM","1"


I don't think the inserts for parameter are formatting the date. I don't
believe this is UCT format.

I have also confirmed that my UI that I use to display the data does not
alter the date format.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, May 29, 2013 10:58 PM
To: 'General Discussion of SQLite Database'
Subject: 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



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fw: problem sqlite3 c++ please

[Fehmi Noyan ISI]

specific to your question,
if you want to import/export with C API, you can try these extensions

https://github.com/fnoyanisi/sqlite3_capi_extensions




 From: Fehmi Noyan ISI 
To: General Discussion of SQLite Database  
Sent: Thursday, May 30, 2013 10:32 PM
Subject: Re: [sqlite] Fw: problem sqlite3 c++ please
 

Hi,

The command you entered, starting with dot, are sqlite shell commands, i.e. if 
you need similar functionality, you either

* need to check SQLite C/C++ API documentation for a fucntion 
or
* need to implement your own function



From: Maxime Gerum 
To: "sqlite-users@sqlite.org"  
Sent: Thursday, May 30, 2013 12:01 AM
Subject: [sqlite] Fw: problem sqlite3 c++ please





Hello, i’m french, sorry if i make errors of langage



In my school project, i use sqlite3 with c++, i can make any request such as 
create table or select *from, no problems.



But i would export my database in a csv file, in sqlite3> .mode csv

                                                                                
                   .separator ,

                                                                                
                   .output client.csv

                                                                                
                   select *from client;



it works, but in c++, if i make request[4]= ”.mode csv”

                                                                          
“.separator ,”

                                                                          
”.output client.csv”

                                                                          
“select *from client”



only select *from client works,



i have an error : near ”.” : syntax error



Please help me,





Maxime




Envoyé depuis Windows 8
___
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] Fw: problem sqlite3 c++ please

[Fehmi Noyan ISI]

Hi,

The command you entered, starting with dot, are sqlite shell commands, i.e. if 
you need similar functionality, you either

* need to check SQLite C/C++ API documentation for a fucntion 
or
* need to implement your own function



 From: Maxime Gerum 
To: "sqlite-users@sqlite.org"  
Sent: Thursday, May 30, 2013 12:01 AM
Subject: [sqlite] Fw: problem sqlite3 c++ please
 




Hello, i’m french, sorry if i make errors of langage



In my school project, i use sqlite3 with c++, i can make any request such as 
create table or select *from, no problems.



But i would export my database in a csv file, in sqlite3> .mode csv

                                                                                
                   .separator ,

                                                                                
                   .output client.csv

                                                                                
                   select *from client;



it works, but in c++, if i make request[4]= ”.mode csv”

                                                                          
“.separator ,”

                                                                          
”.output client.csv”

                                                                          
“select *from client”



only select *from client works,



i have an error : near ”.” : syntax error



Please help me,





Maxime




Envoyé depuis Windows 8
___
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] Fw: problem sqlite3 c++ please

[Stephan Beal]

On Wed, May 29, 2013 at 4:31 PM, Maxime Gerum  wrote:

>
> it works, but in c++, if i make request[4]= ”.mode csv”
>

.mode is a command from the shell program sqlite3, and is not valid SQL.
The C API only knows SQL, so you'll have to write the import/export code
yourself or steal it from the shell.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem sqlite3 c++ please

[Simon Slavin]

On 29 May 2013, at 3:31pm, Maxime Gerum  wrote:

> it works, but in c++, if i make request[4]= ”.mode csv”

The commands which start with '.' are not part of SQLite.  They work only in 
the shell tool.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA reverse_unordered_selects and GROUP_CONCAT

[Mark Lawrence]

Hi,

I'm hitting an issue with ordering and GROUP_CONCAT that seems to
depend on whether the reverse_unordered_selects pragma is enabled,
and/or non-aggregate terms are also selected.

reverse_unordered_select  with non-agg  result
    --
ONNoBad
OFF   NoGood
ONYes   Good
OFF   Yes   Good

The attached file should show some details. SQLite version is 3.7.12.1
on Debian. Is this a known or expected behaviour?

Mark.
-- 
Mark Lawrence
CREATE TABLE t(
id integer
);

INSERT INTO t VALUES(1);
INSERT INTO t VALUES(2);
INSERT INTO t VALUES(3);

PRAGMA reverse_unordered_selects = ON;

SELECT
GROUP_CONCAT(ordered.id)
FROM
(SELECT
id
FROM
t
ORDER BY
id ASC
) AS ordered
;


PRAGMA reverse_unordered_selects = OFF;

SELECT
GROUP_CONCAT(ordered.id)
FROM
(SELECT
id
FROM
t
ORDER BY
id DESC
) AS ordered
;


PRAGMA reverse_unordered_selects = ON;

SELECT
GROUP_CONCAT(ordered.id), 1 AS x
FROM
(SELECT
id
FROM
t
ORDER BY
id ASC
) AS ordered
GROUP BY
x
;


PRAGMA reverse_unordered_selects = OFF;

SELECT
GROUP_CONCAT(ordered.id), 1 AS x
FROM
(SELECT
id
FROM
t
ORDER BY
id DESC
) AS ordered
GROUP BY
x
;

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fw: problem sqlite3 c++ please

[Maxime Gerum]

Hello, i’m french, sorry if i make errors of langage

 

In my school project, i use sqlite3 with c++, i can make any request such as 
create table or select *from, no problems.

 

But i would export my database in a csv file, in sqlite3> .mode csv


   .separator ,


   .output client.csv


   select *from client;

 

it works, but in c++, if i make request[4]= ”.mode csv”

  
“.separator ,”

  
”.output client.csv”

  
“select *from client”

 

only select *from client works,

 

i have an error : near ”.” : syntax error

 

Please help me,

 

 

Maxime


 

Envoyé depuis Windows 8
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm wondering how to use Sqlite3 API to make change on configuration

[Simon Slavin]

On 30 May 2013, at 6:32am, Sunki Cheon  wrote:

> 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).

For those playing along at home,




(not sure why the second page has that URL).

> Before installing sqlite

SQLite does not have any 'installation'.  It is not memory resident.  There's 
no database server.  The calls which consult and change your files are called 
straight from your application and execute inside your application.

> 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?

You can modify the SQLite source code if you want, and change the defaults in 
that, but they're spread out in various places and you'd have to read the 
documentation carefully.

> Or should I have to go to dbt2 source code and find where the dbt2 execute
> the sqlite function or configuration?

The sqlite3_config() interface allwows you to change these settings by making 
your own calls from software written in C.  So assuming your benchmark software 
is written in C you would probably find where the benchmark has its 
sqlite3_initialize() command and put one or more sqlite3_config() commands in 
just before that.  Please check the values returned to be sure that SQLite has 
correctly processed your requests for changes.

> Or should I have to detect the default values some where in the sqlite
> source code and change that?

Unless you are willing to dive into SQLite's source code and learn things that 
would be useful only for very picky uses of SQLite I would not recommend you do 
this.  For most uses of SQLite, sqlite3_config() does it better.

While we're here I just wanted to mention that SQLite is pretty good at picking 
its own default settings.  It's not just one-size-fits all, it choses different 
settings depending on several things it finds out about how your storage system 
works and some aspects of your operating system.  So in your benchmarking 
please try the default settings as well as trying to come up with better 
settings of your own.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users