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 ---
Whil Hentzen wrote an eBook on working with SQLLite.
http://www.hentzenwerke.com/catalog/sqlite2gb.htm
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of José Olavo Cerávolo Sent: Wednesday, April 26, 2017 4:24 PM To: profoxtech@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/
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 rkaye@invaluable.com wrote:
Whil Hentzen wrote an eBook on working with SQLLite.
http://www.hentzenwerke.com/catalog/sqlite2gb.htm
--
rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of José Olavo Cerávolo Sent: Wednesday, April 26, 2017 4:24 PM To: profoxtech@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]
.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 joceravolo@yahoo.com 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]
On Wed, Apr 26, 2017 at 4:23 PM, José Olavo Cerávolo joceravolo@yahoo.com 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 <path and SQLite filename>
List the tables in the database with: .tables
List the schema for the table of interest with: .schema <tablename>
Then you can show us the SQL you're running from VFP and what results look like.