• Welcome, Guest. Please login.
 
July 04, 2020, 04:37:25 pm

News:

Welcome to the SQLitening support forums!


Lost as an Easter egg...

Started by Noble D. Bell, November 30, 2008, 08:51:19 pm

Previous topic - Next topic

Noble D. Bell

I guess I just don't get it.  :(

Can someone point me to some example code of how to create a small sqlite database in code, and be able to add records, save records, delete records, and search records?  I know there are some examples that come with the sl but for some reason I just cannot get my head around it right now.  I just want to do single user databases right now. I don't need the client/server functionality.

TIA
Thanks and God bless,
Noble D. Bell
http://www.noblebell.com
http://www.noblesoftware.com

Paul Squires

If you are having a hard time following the "ExampleC.bas" sample code (which does everything you are looking for) then try reading some of Joe's 'White Papers' over on his site. They may help trigger something for you. http://www.sqlitening.com/wiki/index.php?title=Main_Page

SQLitening tutorials are still pretty sparse but they are not the primary concern right now. Fred is concentrating on the engine itself before moving to writing more formal documentation and tutorials.

SQLitening can already do more than most database systems.

JoeByrne

Noble,

I've got some code I can post but it'll be awhile before I can get to it.  I'll put up a note here when its ready to go.

Fred Meier

This is a simple example that does what you requested.

#Compile Exe
#Include "SQLitening.Inc"

'Example code of how to create a small sqlite database in code,
'and be able to add records, save records, delete records, and
'search records?

Function PbMain()

   ' create a small sqlite database in code
   slOpen  'creates a tempory database in your temp folder
   slExe "Create Table T1 (F1, F2)

   ' add records
   slExe "Insert into T1 values (1, 2)
   slExe "Insert into T1 values ('A', 'B')
   slExe "Insert into T1 values (9, 'Z')
   slExe "Insert into T1 values ('X', 3)

   ' save (update) records
   slExe "Update T1 set F1 = 'Another Value' where rowid=1"
   slExe "Update T1 set F2 = 'GoodBy' where rowid=2"

   'delete records
   slExe "Delete from T1 where rowid=3"
   slExe "Delete from T1 where rowid=4"

   'search records
   slSel "Select rowid from T1 where F2 = 2"
   do while slGetRow
      msgbox "RowID " & slFN("rowid") & " has a 2 in F2"
   loop
   
End Function

JoeByrne

December 03, 2008, 02:46:15 am #4 Last Edit: December 03, 2008, 02:56:53 am by JoeByrne
Ok,

Here's an expanded version of what Fred posted.  This PB/CC example shows all the basic functions (Create, Import, Browse, Select-an-item, Update, and Delete) from a menu with sample data.  You'll be able to see 'real' type data to see how the various functions work.

The example uses a single table to store First and Last Names. (You could expand on this easily enough, but for demonstration purposes, I kept it simple).  I use the Record Set for every transaction to the table.  You don't have to always to this, but I've found its a REALLY good habit to get into.  If the record set concept is not clicking, let me know.

The code could be streamlined a lot.  I opted for the "Make it work first, make it better later" approach so you could get a good feel for how SQLitening works compared to other database engines you may have used in the past.

The sample data is stored in the app itself so the only other thing you'll need is the SQLitening DLLs.  If you don't have PB/CC, don't worry as I put the compiled version in the ZIP as well as the source so you can run the program and see how its written.

Not elegant by any means, but it does all the basic functions in a straight forward way.  If you have any questions, please don't hesitate to ask!

*** Just noticed that I'm mistakenly adding an '&' to the last name in the sample data.  The erroneous line reads:

SQL$ = "INSERT INTO names VALUES(NULL," & "' & " & fName$ & "','" & lName$ & "');"


It should be:

SQL$ = "INSERT INTO names VALUES(NULL," & "'" & fName$ & "','" & lName$ & "');"


Doesn't effect the demo, but it doesn't look as nice :)  This line is in the FOR/NEXT loop in the last SUB at the end.

JoeByrne

December 03, 2008, 02:47:34 am #5 Last Edit: December 03, 2008, 02:58:21 am by JoeByrne
Here is the source code If you don't want to download the zip file:


' JPRO_COMPILER = PB/CC
' JPRO_COMPILETEMP = false


#COMPILE EXE

%Debug    = 0
%IsRemote = 0

' --------------------
#INCLUDE "WIN32API.INC"
#INCLUDE "SQLitening.Inc"
' --------------------  
GLOBAL    gPath     AS STRING
GLOBAL    gDBName   AS STRING

'---SQLitening Record Set IDs---
%rSet1         = 1
%rSet2         = 2
%rSet3         = 3  
%rSet4         = 4
                            

MACRO Wait
      a$ = WAITKEY$
END MACRO     

                            
FUNCTION PBMAIN() AS LONG
'===============================================================
     IF DIR$("SQLitening_Example.db3") = "" THEN
        CLS
        INPUT "Database does not exist. Do you want to create it? (No/Enter) ",a$
        IF LCASE$(a$) = "n" THEN EXIT FUNCTION
        '
        '--- Create the database in the current directory ---
        '
        Result& = slOpen("SQLitening_Example.db3","C")    '--> use "C" to create if doesn't exist
        IF Result& THEN
           ?slGetError:?"Failed to Open/Create database"
           Wait
           EXIT FUNCTION
        END IF
        '
        '--- Create the table ---
        '
        SQL$ = "CREATE TABLE Names (RecKey INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " & _
               "FirstName TEXT, LastName TEXT COLLATE NOCASE);"
        slEXE(SQL$)
        ?"Database and table created"
        ?:INPUT "Do you want to create sample data? (Y/Enter) ",Ans$
        IF UCASE$(Ans$) = "Y" THEN
           CreateSampleData
        END IF
        wait
     ELSE
        slOpen("SQLitening_Example.db3")
        CLS:?"Database Open: Press any key "
        wait
     END IF
    
    
     DO
        CLS
        ?"(1) Browse All"
        ?"(2) Find a Name"
        ?"(3) Add Name"
        ?"(4) Update Name"
        ?"(5) Delete Name"
        ?"-----------------"
        ?"(6) Reset w/Sample Data"
        ?"(7) EXIT PROGRAM"
        ?""
        INPUT "Select: ",a$
        Result& = VAL(a$)
        '
        slCloseSet(%rSet1)
        '
        SELECT CASE Result&
          CASE 1: BrowseDB
          CASE 2: FindName
          CASE 3: AddName
          CASE 4: UpdateName
          CASE 5: DeleteName
          CASE 6: CreateSampleData
          CASE 7: EXIT DO
        END SELECT
     LOOP

END FUNCTION    


SUB BrowseDB()
'===============================================================
'Browse through the database
'---------------------------------------------------------------
     LOCAL DisplayLines AS LONG
    
     cls:?"Browsing Database"
     ?:?"Sort by (R)ecord or (L)ast Name? ";
   GetSort: 
     OrderBy$ = UCASE$(WAITKEY$)
     SELECT CASE OrderBy$
        CASE "R": CLS:? "Browsing Database Sorted by Record Insert Order":?
        CASE "L": CLS:? "Browsing Database Sorted by Last Name":?
        CASE ELSE: OrderBy$ = ""
     END SELECT
     '
     IF OrderBy$ = "" THEN GOTO GetSort
    
     '--- Select all records ---
     SQL$ = "SELECT * FROM Names"
     IF OrderBy$ = "L" THEN    
        SQL$ = "SELECT * FROM Names ORDER BY LastName"
     END IF
     '
     Res& = slSEL(SQL$,%rSet1,"E")
     IF Res& THEN
        ?slGetError:?"Failed Read Names Table"
        WAIT
        EXIT SUB
     END IF
     '
     '--- Loop through returned records one at a time --- 
     '--- Each Record is contained in the %rSet1 Buffer ---
     '
     DO WHILE slGetRow(%rSet1)
           RecKey$ = slFN("RecKey",%rSet1)        'Record 'key'
           fName$  = slFN("FirstName",%rSet1)     'First Name
           lName$  = slFN("LastName",%rSet1)      'Last Name
           '
           INCR DisplayLines
           IF DisplayLines > 15 THEN
              ?:?"Continue? (No/Enter) "
              a$ = WAITKEY$
              IF LCASE$(LEFT$(a$,1)) = "n" THEN
                 EXIT DO
              END IF      
              '
              CLS
              DisplayLines = 1
           END IF
           '
           '--- show the data ---
           '
           ?"(" & RecKey$ & ")  "; lName$; ", "; fName$
     LOOP
    
     slCloseSet(%rSet1)  'Not needed if looped all the way through
     ?:?:?"Done: ";
     wait
END SUB


SUB FindName()
'===============================================================
'Find a name in the database
'---------------------------------------------------------------
     CLS
    
     DO
        INPUT "First Part of Last Name : ",lname$
        IF lname$ = "" THEN
           EXIT DO
        ELSE
           lName$ = UCASE$(lname$)
        END IF
       
        SQL$ = "SELECT * FROM names WHERE LastName LIKE '" + lname$ + "%'"
        Res& = slSEL(SQL$,%rSet1,"E")
        IF Res& THEN
           ?slGetError:?"Failed Read Names Table"
           WAIT
           EXIT SUB
        END IF

        DO WHILE slGetRow(%rSet1)
              RecKey$ = slFN("RecKey",%rSet1)        'Record 'key'
              fName$  = slFN("FirstName",%rSet1)     'First Name
              lName$  = slFN("LastName",%rSet1)      'Last Name
              '
              '--- show the data ---
              '
              ?"(" & RecKey$ & ")  "; lName$;", "; FName$
        LOOP
        ?:?
        wait
     LOOP  
     slCloseSet(%rSet1)  'Not needed if looped all the way through
END SUB

                        
SUB AddName()
'===============================================================
'Add a new name into the database
'---------------------------------------------------------------
     CLS: ?"Add a Name to the Database":?:?
    
     DO
        INPUT "Last Name : ", lname$
        IF lName$ = "" THEN EXIT DO
        INPUT "First Name: ", fName$
        '
        SQL$ = "INSERT INTO names VALUES (NULL," & _
               "'" & FName$ & "'," & _
               "'" & LName$ & "');"
        Res& = slEXE(SQL$,"E")
        IF Res& THEN
           ?slGetError:?"Failed To Add Name " & Fname$ & " " & lName$ & " to Table"
        ELSE
           ? "Name inserted successfully"
        END IF                     
        WAIT
     LOOP
END SUB



SUB UpdateName()
'===============================================================
'Browse through names, pick one, and change it
'---------------------------------------------------------------
     LOCAL DisplayLines AS LONG
    
     CLS
     '--- Select all records ---
     SQL$ = "SELECT * FROM Names"
     Res& = slSEL(SQL$,%rSet1,"E")
     IF Res& THEN
        ?slGetError:?"Failed Read Names Table"
        WAIT
        EXIT SUB
     END IF
     '
     '--- Loop through returned records one at a time --- 
     '--- Each Record is contained in the %rSet1 Buffer ---
     '
     DO WHILE slGetRow(%rSet1)
           RecKey$ = slFN("RecKey",%rSet1)        'Record 'key'
           fName$  = slFN("FirstName",%rSet1)     'First Name
           lName$  = slFN("LastName",%rSet1)      'Last Name
           '
           INCR DisplayLines
           IF DisplayLines > 15 THEN
              ?:?:INPUT "Select Record# to Change: ",Rec$
              IF VAL(Rec$) > 0 THEN EXIT LOOP
              CLS
              DisplayLines = 1
           END IF
           '
           '--- show the data ---
           '
           ?"(" & RecKey$ & ")  "; lName$; ", "; fName$
     LOOP
    
     ?
     slCloseSet(%rSet1)
     IF Rec$ = "" THEN
        INPUT "Select Record# to Change: ",Rec$
        IF VAL(Rec$) < 1 THEN EXIT SUB
     END IF
     '
     '--- Select a single record by record key # ---
     '
     SQL$ = "SELECT * FROM names WHERE RecKey = " & Rec$
     Res& = slSEL(SQL$,%rSet1,"E")
     '--- If no rows returned then something isn't right---
     IF Res& THEN
        ?slGetError:?"Failed Read Names Table for record: " & Rec$
        WAIT
        EXIT SUB
     END IF
     '
     slGetRow(%rSet1)
     fName$ = slFN("FirstName",%rSet1)
     lName$ = slFN("LastName",%rSet1)
     '
     ?:?"Current Name: " & fName$ & " " & LName$
     INPUT "New Last Name: (Enter = No Change) ",NewLName$
     INPUT "New First Name: (Enter = No Change) ",NewFName$
     '
     IF (NewLName$) = "" AND (NewFName$ = "") THEN EXIT SUB
     IF NewLName$ = "" THEN NewLName$ = LName$
     IF NewFName$ = "" THEN NewFName$ = FName$
     '
     '--- Update the record ---
     '
     SQL$ = "UPDATE names SET FirstName = " & _
            "' " & NewFName$ & "', LastName = '" & NewLName$ & "' " & _
            "WHERE RecKey = " & Rec$ & ";"
     Result& = slEXE(SQL$,"E")
     '
     IF Result& <> 0 THEN
        ?slGetError:?"Failed to Update Names in Table" & Rec$
     ELSE
        ?:?"Changes Successful"
     END IF
     '
     slCloseSet(%rSet1)
     WAIT
END SUB    



SUB DeleteName()
'===============================================================
'Browse through names, pick one and Delete it
'---------------------------------------------------------------
     LOCAL DisplayLines AS LONG
    
     CLS
     '--- Select all records ---
     SQL$ = "SELECT * FROM Names"
     Res& = slSEL(SQL$,%rSet1,"E")
     IF Res& THEN
        ?slGetError:?"Failed Read Names Table"
        WAIT
        EXIT SUB
     END IF
     '
     '--- Loop through returned records one at a time --- 
     '--- Each Record is contained in the %rSet1 Buffer ---
     '
     DO WHILE slGetRow(%rSet1)
           RecKey$ = slFN("RecKey",%rSet1)        'Record 'key'
           fName$  = slFN("FirstName",%rSet1)     'First Name
           lName$  = slFN("LastName",%rSet1)      'Last Name
           '
           INCR DisplayLines
           IF DisplayLines > 15 THEN
              ?:?:INPUT "Select Record# to Delete: ",Rec$
              IF VAL(Rec$) > 0 THEN EXIT LOOP
              CLS
              DisplayLines = 1
           END IF
           '
           '--- show the data ---
           '
           ?"(" & RecKey$ & ")  "; lName$; ", "; fName$
     LOOP
    
     ?
     slCloseSet(%rSet1)
     IF Rec$ = "" THEN
        INPUT "Select Record# to Delete: ",Rec$
        IF VAL(Rec$) < 1 THEN EXIT SUB
     END IF
     '
     '--- Select a single record by record key # ---
     '
     SQL$ = "SELECT * FROM names WHERE RecKey = " & Rec$
     Res& = slSEL(SQL$,%rSet1,"E")
     '--- If no rows returned then something isn't right---
     IF Res& THEN
        ?slGetError:?"Failed Read Names Table for record: " & Rec$
        WAIT
        EXIT SUB
     END IF
     '
     slGetRow(%rSet1)
     fName$ = slFN("FirstName",%rSet1)
     lName$ = slFN("LastName",%rSet1)
     '
     ?:?"Selected Name: " & fName$ & " " & LName$
     ?:INPUT "Are you SURE you want to Delete this name: (YES/Enter) ",Ans$
     '
     IF Ans$ <> "YES" THEN EXIT SUB
     '
     '--- Delete a Single record ---
     '
     SQL$ = "DELETE FROM names WHERE RecKey = " & Rec$ & ";"
     Result& = slEXE(SQL$,"E")
     '
     IF Result& <> 0 THEN
        ?slGetError:?"Failed to Update Names in Table" & Rec$
     ELSE
        ?:?"Changes Successful"
     END IF
     '
     slCloseSet(%rSet1)
     WAIT
END SUB    



SUB CreateSampleData()
'===============================================================
'Clear the table then add sample names
'---------------------------------------------------------------
     LOCAL x AS LONG
    
     CLS: ?"Creating Sample Data. Existing data will be deleted"
     ?:INPUT "Continue? (Y/Enter) ",Ans$
     IF UCASE$(Ans$) <> "Y" THEN EXIT SUB
     '
     '--- Delete all existing records in the table ---
     '
     SQL$ = "DROP TABLE names"
     e& = slEXE(SQL$,"E")
     IF e& THEN
        ?slGetError
        wait
        EXIT SUB
     END IF
     '
     '
     '--- Create the table ---
     '
     SQL$ = "CREATE TABLE Names (RecKey INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " & _
            "FirstName TEXT, LastName TEXT COLLATE NOCASE);"
     slEXE(SQL$)
     '
     '--- The reason to 'DROP' the table is so the AUTO-INCREMENT record number always starts at 1
     '    If you simply delete the records (DELETE FROM names) then the sample data will start with a
     '    RecKey# following the highest deleted value.  Problem? Probably not but be aware of this.
     
     
     DATA "Stang","Jacob","Stockinger","Dan","Tegland","Jeff","Terwey","Greg","Wick","Dean","Wuollet","Ben","Zierman","Helen"
     DATA "Kent","Don","Klebs","Duane","Knutson","Steve","Krauss","Jay","Langerud","Tom","Lant","Roland","Mattson","Jill"
     DATA "Arvan","Rebecca","Benson","John","Berg","Betty Lou","Bernier","Sandy","Boettcher","John","Brown","Pete"
     DATA "Cornwall","Mike","Cross","Helen","Galligan","Eric","Gargus","Mark","Gordon","Dan","Hilgers","Jennifer","Janis","John"
     DATA "Olsen","Randy","Peterson","Ronnie","Petrie","Dan","Primus","Andrea","Quiggle","Barb","Raulerson","Mike","Reed"
     DATA "John","Robinson","Iola","Rodriguez","Jayson","Schmiesing","Owen","Schreder","Brian","Smith","Todd"
    
    
     FOR x = 1 TO DATACOUNT STEP 2
         lName$ = READ$(x)
         fName$ = READ$(x+1)
         SQL$ = "INSERT INTO names VALUES(NULL," & "'" & fName$ & "','" & lName$ & "');"
         Res& = slEXE(SQL$,"E")
         IF Res& THEN
            ?slGetError
            EXIT FOR
         ELSE
            ?lName$ & ", " & fName$
         END IF
     NEXT
     '
     ?:?:INPUT "Done ",Ans$
END SUB