Re: Reading from SQLite

[Ted Roche]

So, what's your theory? You have the data, you'll need to make the
hypotheses and test them out. Perhaps they are storing the dates as a
offset in seconds since June of 2003, or perhaps there's something
else going on. If you know the correct date for a couple of records,
you should be able to match them against these 9-digit elements and
work out the pattern.

In SQLite3, if you issued a "SELECT * FROM " do you see the
same values you see in VFP?

I don't know why they would be coming back as memo fields.

So, I just installed SQLite3 and the ODBC driver, created a test
database and table with a date and datetime, populated it, created a
DSN for VFP to connect to, queried the database table and got back a
date and a datetime.

You'll need to supply more information, or work it out for yourself.



On Thu, Apr 27, 2017 at 2:36 PM, José Olavo Cerávolo
<jocerav...@yahoo.com> wrote:
> Ok, here's the information I get with SQLite3, the Dates are stored with 9 
> characters.
>
> database page size:  4096
> write format:2
> read format: 2
> reserved bytes:  0
> file change counter: 347
> database page count: 532
> freelist page count: 0
> schema cookie:   45
> schema format:   4
> default cache size:  0
> autovacuum top root: 39
> incremental vacuum:  1
> text encoding:   1 (utf8)
> user version:0
> application id:  0
> software version:3014000
> number of tables:23
> number of indexes:   14
> number of triggers:  0
> number of views: 0
> schema size: 8265
> Subject: Re: Reading from SQLite
> Message-ID:
> 

Re: Reading from SQLite

[Stephen Russell]

Try this page as well:

http://www.sqlite.org/lang_datefunc.html

Time Strings

A time string can be in any of the following formats:

   1. *-MM-DD*
   2. *-MM-DD HH:MM*
   3. *-MM-DD HH:MM:SS*
   4. *-MM-DD HH:MM:SS.SSS*
   5. *-MM-DD**T**HH:MM*
   6. *-MM-DD**T**HH:MM:SS*
   7. *-MM-DD**T**HH:MM:SS.SSS*
   8. *HH:MM*
   9. *HH:MM:SS*
   10. *HH:MM:SS.SSS*
   11. *now*
   12. *DD*


On Thu, Apr 27, 2017 at 1:04 PM, Ted Roche  wrote:

> On Thu, Apr 27, 2017 at 12:17 PM, José Olavo Cerávolo
>  wrote:
> > Hi Ted,
> > The dates on the existing table are not .NULL..When I get the date using
> what you suggested, datetime(yourfield,'unixepoch','localtime'), I get
> this date on an MEMO field 1984-10-05 23:00:00. This date is not the actual
> date on the other application. The date should be in 2017.The actual value
> stored on the actual SQLite table is 459316800.
>
> Well, that's weird. THE SQLite function datetime() is expecting a
> number of 10 digits, so that's not the encoding scheme. Can you use a
> tool like SQLite3 to look at the actual data in the SQLite table and
> confirm this isn't a problem with ODBC or Fox's intepretation of the
> data?
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAJidMYLCMF8cnUsoXAAGAuaDkrEuTNS-vXwdLGJQ9efy=cw...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Reading from SQLite

[José Olavo Cerávolo]

Ok, here's the information I get with SQLite3, the Dates are stored with 9 
characters.

database page size:  4096
write format:    2
read format: 2
reserved bytes:  0
file change counter: 347
database page count: 532
freelist page count: 0
schema cookie:   45
schema format:   4
default cache size:  0
autovacuum top root: 39
incremental vacuum:  1
text encoding:   1 (utf8)
user version:    0
application id:  0
software version:    3014000
number of tables:    23
number of indexes:   14
number of triggers:  0
number of views: 0
schema size: 8265
Subject: Re: Reading from SQLite
Message-ID:
    

Re: Reading from SQLite

[Ted Roche]

On Thu, Apr 27, 2017 at 12:17 PM, José Olavo Cerávolo
 wrote:
> Hi Ted,
> The dates on the existing table are not .NULL..When I get the date using what 
> you suggested, datetime(yourfield,'unixepoch','localtime'), I get this date 
> on an MEMO field 1984-10-05 23:00:00. This date is not the actual date on the 
> other application. The date should be in 2017.The actual value stored on the 
> actual SQLite table is 459316800.

Well, that's weird. THE SQLite function datetime() is expecting a
number of 10 digits, so that's not the encoding scheme. Can you use a
tool like SQLite3 to look at the actual data in the SQLite table and
confirm this isn't a problem with ODBC or Fox's intepretation of the
data?

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4sokfifY57-mWO4ZGW1wv=o_j0mnkk5dxmmjwekryh...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Reading from SQLite

[José Olavo Cerávolo]

Hi Ted,
The dates on the existing table are not .NULL..When I get the date using what 
you suggested, datetime(yourfield,'unixepoch','localtime'), I get this date on 
an MEMO field 1984-10-05 23:00:00. This date is not the actual date on the 
other application. The date should be in 2017.The actual value stored on the 
actual SQLite table is 459316800. 
There's a disconnect from what the other application is saving to what I am 
able to read. I am not able to talk to the other application programmer. I've 
read a lot of documentation about SQLite, but nothing that could actually help 
me.
Thanks,
Jose.

Message: 7
Date: Wed, 26 Apr 2017 16:58:56 -0400
From: Ted Roche <tedro...@gmail.com>
To: profoxt...@leafe.com
Subject: Re: Reading from SQLite
Message-ID:
    <cacw6n4ted4w_w0ik59nd+c30yh+tnqhn6gfdq+zgesxuymt...@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"

.NULL. is a perfectly valid date.

If you're getting numbers, you're likely getting dates stored as days
since some arbitrary date. Try:

select datetime(yourfield,'unixepoch','localtime') from yourtable;

José Olavo Cerávolo
http://www.ceravoloconsulting.com/

--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/547081955.12729827.1493309867...@mail.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Reading from SQLite

[Ted Roche]

On Wed, Apr 26, 2017 at 4:23 PM, José Olavo Cerávolo
 wrote:
> Hi Guys,
> I sent a message but it guess it was misplaced.I am trying to read a SQLite 
> database, but I have problems with the data.I can connect to it, read the 
> tables, but the values are messed up.I can't get the Dates, it comes out as 
> .NULL or with weird numbers.I've looked at the documentation, but nothing is 
> working.I am using the SQLite ODBC for SQLite version 3.
> Thanks in advance,
> Jose.

Locate and run the SQLite3.exe to give us some more info:

SQLite3 

List the tables in the database with:
.tables

List the schema for the table of interest with:
.schema 

Then you can show us the SQL you're running from VFP and what results look like.

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4t2AVRWA8bwyO+Ti9JPOU3qzNDfE7pH5n_FMXx90=m...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Reading from SQLite

[Ted Roche]

.NULL. is a perfectly valid date.

If you're getting numbers, you're likely getting dates stored as days
since some arbitrary date. Try:

select datetime(yourfield,'unixepoch','localtime') from yourtable;

On Wed, Apr 26, 2017 at 4:23 PM, José Olavo Cerávolo
 wrote:
> Hi Guys,
> I sent a message but it guess it was misplaced.I am trying to read a SQLite 
> database, but I have problems with the data.I can connect to it, read the 
> tables, but the values are messed up.I can't get the Dates, it comes out as 
> .NULL or with weird numbers.I've looked at the documentation, but nothing is 
> working.I am using the SQLite ODBC for SQLite version 3.
> Thanks in advance,
> Jose.
>  José Olavo Cerávolohttp://www.ceravoloconsulting.com/
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>   text/plain (text body -- kept)
>   text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cacw6n4ted4w_w0ik59nd+c30yh+tnqhn6gfdq+zgesxuymt...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Reading from SQLite

[Stephen Russell]

You may have the difficulty of a VFP date is not a SQL date.

getdate() is the system datetime.

-- convert a date to String

SELECT convert(varchar(25), getdate(), 120)

-- date part only

SELECT CONVERT(date, getdate())


On Wed, Apr 26, 2017 at 3:33 PM, Richard Kaye  wrote:

> Whil Hentzen wrote an eBook on working with SQLLite.
>
> http://www.hentzenwerke.com/catalog/sqlite2gb.htm
>
>
> --
>
> rk
> -Original Message-
> From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of José
> Olavo Cerávolo
> Sent: Wednesday, April 26, 2017 4:24 PM
> To: profoxt...@leafe.com
> Subject: Reading from SQLite
>
> Hi Guys,
> I sent a message but it guess it was misplaced.I am trying to read a
> SQLite database, but I have problems with the data.I can connect to it,
> read the tables, but the values are messed up.I can't get the Dates, it
> comes out as .NULL or with weird numbers.I've looked at the documentation,
> but nothing is working.I am using the SQLite ODBC for SQLite version 3.
> Thanks in advance,
> Jose.
>  José Olavo Cerávolohttp://www.ceravoloconsulting.com/
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cajidmyjnqutisyb+-ta__yjqjtjf9cwvx+fn8tg46a3u+ri...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Reading from SQLite

[Richard Kaye]

Whil Hentzen wrote an eBook on working with SQLLite.

http://www.hentzenwerke.com/catalog/sqlite2gb.htm


--

rk
-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of José Olavo 
Cerávolo
Sent: Wednesday, April 26, 2017 4:24 PM
To: profoxt...@leafe.com
Subject: Reading from SQLite

Hi Guys,
I sent a message but it guess it was misplaced.I am trying to read a SQLite 
database, but I have problems with the data.I can connect to it, read the 
tables, but the values are messed up.I can't get the Dates, it comes out as 
.NULL or with weird numbers.I've looked at the documentation, but nothing is 
working.I am using the SQLite ODBC for SQLite version 3.
Thanks in advance,
Jose.
 José Olavo Cerávolohttp://www.ceravoloconsulting.com/

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/bn4pr10mb09136f1044bff4c70fa800a6d2...@bn4pr10mb0913.namprd10.prod.outlook.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.