• Welcome, Guest. Please login.
 
August 26, 2019, 01:28:49 am

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - David L Morris

1
Fredrick, you might look again at the date and time functions available in SQLite. Quote - you may choose to represent your datetimes in the database as Julian dates or as datetime formatted strings.  Julian dates are doubles but they are converted to and from string when written to and read from the database.

Representating your datetimes as Julian dates has the advantage that
1. you get the most time resolution for the fewest bytes
2. you can compute datetime differences with simple arithmetic
3. it is the native format inside the SQLite date and time functions
4. it is the format returned from the internal now() function

Unquote

3
Hj Andrew,  You might want to test something like this SQL and extend it to insert the results into your new table
.
select '07', sum(Pitch1) from YourTable where strftime('%m',RecordTime) = '07'

This should get total for July
Hope it helps
David
4
Hello Marty

If you can get the DATA0030.TXT file from Tim as suggested by Gary, it contains 30000 records. I have played with PBCC40 to convert the file to csv and load it into an SQLite database.  I am sure you will not need all the indexes, just the INTEGER PRIMARY KEY will suffice.  The data loads in less then a minute. A "select * from clients where state = 'WI' " returns 2748 records in 29.13ms , so indexes on all fields is a waste to me.

Regards
David
5
Thanks Fred.  I found the version that was installed was 1.10.  After I uninstalled the 1.10 server and installed 1.30, I no longer have the problem.

David
6
When trying to run both examples from the bin folder, or compile and run from the samples folder with PBWin 8.0 , I get an error message indicating :-

"SQLite or SQLitening retuned the following unexpected message!
1 = unrecognized token: " "
Statement = Sel Select * from One.T1"

The token seems to be the unprintable LF or CR.
This error does not present when running in Version 120.
Perhaps others might have investigated this?

Regards
David
7
Thanks Fred.  slOpen ":memory:" worked well.  I can see some benefits in using a memory database rather than ARRAY operations.  Also, should one be able to ATTACH file databases to the ":memory:" database and operate as normal on the file databases?
8
I like the SQLite date functions and find them accurate and simple.  Thought it would be good to include SQLitening in any PB project so that all date needs are serviced.  The example code below works, however, SQLitening requires a database be open to execute any SQL commands. This is not really a serious problem as a dummy db could be created on progam start up.  Is there a problem in this simple use of SQLitening?

[Code/]
'  ==========================================================================
'  |                                                                        |
'  | SQLitening MyDateUse.bas                                                  |
'  |                                                                        |
'  ==========================================================================

#DIM ALL

#COMPILE EXE "..\Bin\MyDateUse.Exe"
#INCLUDE "..\Inc\SQLitening.Inc"

'==============================<[ Main ]>==============================
FUNCTION PBMAIN()

   ' Open the sample database

   slOpen "c:\SQLite3\DLM_Data\myledger.db3"
   
   slSel "Select julianday('now','localtime')

   ' Process records

   DO WHILE slGetRow
      ? "Julianday('now') = ";slF(1)
   LOOP
   
   slSel "SELECT date('now','start of year','+9 months','weekday 2')"

   DO WHILE slGetRow
      ? "date('now','start of year','+9 months','weekday 2') = ";slF(1)
   LOOP

   slSel "select strftime('%Y-%m-%d %H:%M:%S', 'now','localtime')"

   DO WHILE slGetRow
      ? "select strftime('%Y-%m-%d %H:%M:%S', 'now','localtime') = ";slF(1)
   LOOP
   
   slSel "SELECT datetime(1092941466, 'unixepoch', 'localtime')"

   DO WHILE slGetRow
      ? "datetime(1092941466, 'unixepoch', 'localtime') = ";slF(1)
   LOOP

   ? "Press any key to end";
   WAITKEY$

END FUNCTION
9
I found this link of interest:

http://www.databasedev.co.uk/data_models.html

Hope it helps others.

David
10
I imagine Fred will be looking at this soon.  I see in the function code in SQLitening.bas, there is a different code, based on whether the database is remote or local. Some ASM is used to clean up the returned QUAD when local but not when remote.  Note Fred's comment:         
"'Problem: C returns quads using the EDX:EAX registers, whereas
         'PB returns quad values in st(0) of the FPU. Following will
         'move value from EDX:EAX into a Quad "
I am interested in Fred's advice before looking further
11
Thanks Paul.  I seem to have all the files expanded now and will start my learning curve again.
12
So, what shall we try next?
13
I simply use the Vista Windows Explorer, right click on the download folder, then select the menu item "Extract All..." to extract compressed (zipped) folders. The process creates the new folder, then complains "There is already a file with the same name as the folder name you specified.  Specify a different name."  I do that but it makes no difference.

Do you want to try an unzipped download for me to try?

Regards

David
14
Hello there.  I have Vista Home Premium and have not experienced a problem with your previous zipped files, but this latest refuses to do the simple "Extract All". ANy ideas would be appreciated.

Thanks
15
I am getting error 1 when using the latest SQLite3.dll (423kb) where earlier version (356kb) works OK with Paul's Testclient.exe.  I am about to download Paul's latest sql3_cs_0010 examples to try out.

Good luck