• Welcome, Guest. Please login.
 
August 26, 2019, 01:18:17 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.

Topics - David L Morris

1
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
2
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
3
I found this link of interest:

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

Hope it helps others.

David
4
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
5
Hi again Paul. I have my earlier test program and database to work using your examples.  The database has 9 tables.  Basically the program does three steps.  1. User selects a client 2. A product list is presented for the client and the user selects a product 3. A list is presented showing all TV commercials shown across all TV stations in the single week, what stations, time shown and duration. 

I have done this since I retired and have the same program made up using SQLite2 with Don Dickinson's tools, Tsunami and now with SQLite3 and your examples.

The last step 3 is much slower with your code.  The SQL joins several tables and you might like to have a look at the database and code - it is a zip file of 2320kb.  Let me know and I can send it through.

The SQL code and population of the list box is:-

CALLBACK FUNCTION ShowSpotKeys()
    LOCAL sSQL AS STRING, Msg AS STRING, Pad AS STRING , TheResult AS STRING
    LOCAL nRows     AS LONG
    LOCAL nCols     AS LONG
    LOCAL i         AS LONG
    LOCAL j         AS LONG
    LOCAL rs        AS LONG ' holds the recordset
    LOCAL sText AS STRING, lc AS LONG, Res AS LONG
    LOCAL AdvertID AS STRING
    CONTROL SEND ghDlg, 103, %LB_GETCOUNT, 0, 0 TO lc
    IF lc < 1 THEN EXIT FUNCTION
    MOUSEPTR 11
    LockWindowUpdate ghDlg
    LISTBOX RESET ghDlg, 104

    IF CBCTLMSG = %LBN_SELCHANGE THEN
        LISTBOX GET TEXT CBHNDL, 103 TO stext
        AdvertID = TRIM$(MID$(sText,51))
        IF VAL(AdvertID) > 0 THEN
            sSQL = "select SpotDateTime, NetworkName, CallSign, KeyNumber, Duration, ProgramName " + $CRLF _
                   + "from MTV20030105Spots, MTV20030105Stations, MTV20030105Programs "+ $CRLF _
                   + "where MTV20030105Spots.AdvertisementID " + $CRLF _
                   + "in (select AdvertisementID from MTV20030105AdLink where productid = " + AdvertID + ")" + $CRLF _
                   + "and MTV20030105Stations.StationID = MTV20030105Spots.StationID " + $CRLF _
                   + "and MTV20030105Programs.ProgramID = MTV20030105Spots.ProgramID "

          '  msgbox sSql
            errcode = sql3_select( hSession, sSQL, rs )
            IF errcode THEN
               ? "Error:" & STR$(errcode) & " during sql3_select (Select).":  GOTO ExitOut3
            END IF
            nRows = sql3_rsRowCount( rs )
            nCols = sql3_rsColCount( rs )
           ' msgbox str$(nRows) & " Rows found"
           ' Iterate the recordset
            FOR i = 1 TO nRows
               sql3_rsMoveTo rs, i
               ' Get the data for each column in the row.
               TheResult = SPACE$(110)
                   MID$(TheResult,1)  = sql3_rsGetAt( rs, 1)'"SpotDateTime")
                   MID$(TheResult,21) = sql3_rsGetAt( rs, 2)'"NetworkName")
                   MID$(TheResult,31) = sql3_rsGetAt( rs, 3)'"CallSign")
                   MID$(TheResult,41) = sql3_rsGetAt( rs, 4)'"KeyNumber")
                   MID$(TheResult,58) = sql3_rsGetAt( rs, 5)'"Duration")
                   MID$(TheResult,62) = sql3_rsGetAt( rs, 6)'"ProgramName")
                   IF TRIM$(TheResult) <> "" THEN
                        LISTBOX ADD ghDlg, 104, TheResult
                   END IF
            NEXT

        END IF

        Msg$ = STR$(nRows) + " spots displayed"
        CONTROL SET TEXT ghDlg, 105, Msg

    END IF
ExitOut3:
    IF rs THEN sql3_rsDestroy rs
    LockWindowUpdate 0
    MOUSEPTR 0
END FUNCTION
               
6
Hi Paul.  I have tested your examples well.  Today, I tried with an existing database which has multi tables covering television commercials of clients, products and advertisers during a single week of broadcasts.  I had previously used this database to do a concept development using Don Dickinson's SQLite example code.  That development works quite well, however, I modified your catalog.bas to connect but get the error.  Perhaps you con advise what error 26 really means before I continue?

Moments later!! - I realise the database is an SQLite2 created in 2004 and NOT an SQLite3.  I might arrange an upgrade.