Announcement

Collapse
No announcement yet.

SQLite3 Client/Server

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite3 Client/Server

    Hi Everyone,

    I just posted a new set of sources for the SQLite3 Client/Server project. You can read all about it at http://planetsquires.com/support/index.php?topic=2411.0

    The source code is public domain... just like SQLite itself. If you haven't heard about SQLite then you are missing out on one of the smallest, fastest, coolest SQL enabled database systems ever written. http://www.sqlite.org/

    (Updated Jan 27, 2008)
    Last edited by Paul Squires; 27 Jan 2008, 09:23 AM.
    Paul Squires
    FireFly Visual Designer (for PowerBASIC Windows 10+)
    Version 3 now available.
    http://www.planetsquires.com

  • #2
    Here is a quick example of starting session with the database server, creating a database, a table, an index, and finally performing a SELECT and iterating the results. It is all pretty simple compared to many other database systems.

    Code:
    '//
    '//  PUBLIC DOMAIN SOFTWARE - November 23, 2007
    '//  The author or authors of this code dedicate any and all 
    '//  copyright interest in this code to the public domain. 
    '//  Anyone is free to copy, modify, publish, use, compile, 
    '//  sell, or distribute the original code, either in source 
    '//  code form or as a compiled binary, for any purpose, 
    '//  commercial or non-commercial, and by any means.
    '//
    
    #Compile Exe
    
    'JPRO_COMPILER=PB/WIN
    
    
    #Include "sql3client.inc"
    
    
    Function PBMain() As Long
    
       Local hSession  As Dword
       Local sData     As String
       Local sSQL      As String
       Local sDatabase As String
       Local sColumns  As String
       Local rs        As Long     ' holds the recordset
       Local nRows     As Long
       Local nCols     As Long
       Local errcode   As Long
       Local i         As Long
       Local j         As Long
       
    
       sDatabase = "create.db3"
    
       
       '//
       '//  Create the session. We must have a session variable
       '//  in order to interact with all other sql functions.
       '//
       sql3_StartSession hSession, 0   ' do not use Unicode support
    
     
       
       '//
       '//  Connect to the server (make sure that server is running
       '//  or we will get an error 57). 
       '//
       '//  Comment out the sql_connect call if you want your database to 
       '//  be opened/created on the local drive rather than by the server.
       '//  This allows you to use the program without any server running
       '//  at all (i.e. like a traditional, local, database system). You
       '//  just need to make sure to use %SQL_LOCAL in the sql3_Use statement.
       '//
       sSQL = "SERVER = localhost; PORT = default; UID = pss234; PASSWORD = sqlrocks"
       
       errcode = sql3_connect( hSession, sSQL )
       If errcode Then 
          ? "Error:" & Str$(errcode) & " during sql3_connect.":  GoTo ExitOut
       End If
       
     
       
       '//
       '//  Select the database to use (or create if none already exists). In this
       '//  case we use %SQL_REMOTE to create the database on the server.
       '//  To create a local database we would specify %SQL3_LOCAL.     
       '//
       errcode = sql3_Use( hSession, sDatabase, %SQL3_REMOTE )
       If errcode Then 
          ? "Error:" & Str$(errcode) & " during sql3_use.":  GoTo ExitOut
       End If
             
    
       '//
       '//  Create a simple table with an index in the database.
       '//
       sSQL = "CREATE TABLE [parts] ( "   & _
               "[MANUF] Text Not Null, "  & _
               "[REDREF] Text, "          & _
               "[PRODUCT] Text, "         & _
               "[LANGUAGE] Text, "        & _
               "[CPU_OS] Text, "          & _
               "[MEDIA] Text, "           & _
               "[TYPE] Text, "            & _
               "[PGROUP] Text, "          & _
               "[PRICE] NUMERIC); "       & _
               "CREATE INDEX [MANUF] ON [parts] ([MANUF]);"
       errcode = sql3_exec( hSession, sSQL ) 
       If errcode Then 
          ? "Error:" & Str$(errcode) & " during sql3_exe (Adding table/index).":  GoTo ExitOut
       End If 
    
    
       '//
       '//  Add a new record to the database.
       '//  In order to minimize database locking problems, you should wrap your SQL
       '//  request in an "IMMEDIATE" or "EXCLUSIVE" transaction rather than a
       '//  "DEFERRED" transaction.                      
       '//  
       sSQL = "BEGIN IMMEDIATE TRANSACTION; " & _
              "INSERT INTO parts (manuf) VALUES ('PlanetSquires" & Time$ & "');" & _
              "COMMIT TRANSACTION;"
       errcode = sql3_exec( hSession, sSQL ) 
       If errcode Then 
          ? "Error:" & Str$(errcode) & " during sql3_exe (Adding record).":  GoTo ExitOut
       End If 
       
       
       
       '//
       '//  Create a recordset. Once the recordset is created then we can move through it.
       '//  Ensure to set the row cache size to a realistic level. It is faster to bring
       '//  a number of rows to the client from the server rather than one row at a time. 
       '//  The server will compress the rows prior to sending them to the client but
       '//  there is always overhead associated with the TCP call and network transmission.
       '//  In this case, we set a row cache of 1000 rows. The client will request these
       '//  many rows from the server when needed.
       '//
       sSQL = "SELECT * FROM parts;"
       errcode = sql3_select( hSession, sSQL, rs, 1000 ) 
       If errcode Then 
          ? "Error:" & Str$(errcode) & " during sql3_select (Select).":  GoTo ExitOut
       End If
     
     
       
       '//
       '//  Get the column names
       '//
       nCols = sql3_rsColCount( hSession, rs )
       sColumns = ""
       For i = 0 To nCols - 1    ' column numbers are zero based
           sColumns = sColumns & sql3_rsColName( hSession, rs, i ) & ", "
       Next
       sColumns = RTrim$(sColumns, Any ", ")
       
     
       
       '
       ' Additional functions
       '
       ' Convert a Column name into its Column number (needed for the sql3_rsColText function).
       '      nColNumber = sql3_rsColNumber( hSession, rs, "MANUF" )
       '  
       
       
       
       ' Save our results to a test file
       f& = FreeFile
       Open "_debug.txt" For Output As #f&
       
       ' Save our column names
       Print #f&, sColumns
      
    
       t1# = Timer
       
       '//
       '//  Iterate the recordset by fetching each row from the recordset. If necessary,
       '//  additional rows will be automatically retrieved from the server as needed.
       '//
       Do Until sql3_rsFetch( hSession, rs ) = %SQL3_EOF
           
           sData = ""
           
           ' Get the data for each column in the row.
           For j = 0 To nCols - 1
              ' All row/field data is retrieved in text form. If you need
              ' it in numeric form then it is your responsibility to convert
              ' it using functions like BASIC's Val function.
              sData = sData & sql3_rsColText( hSession, rs, j ) & ", "  
           Next             
                               
           Incr nRows
           Print #f&, "Row:" & Str$(nRows) & "  " & RTrim$(sData, Any ", ") 
           
       Loop
        
       t2# = Timer
    
       Close #f&
    
        
       ? "Time to iterate" & Str$(nRows) & " rows: " & Format$( t2#-t1#, "###.##0") & " seconds."
    
    
    ExitOut:
    
       '//
       '//  End the session and destroy session/connection/recordset
       '//
       If hSession Then 
          If rs Then sql3_rsDestroy hSession, rs
          sql3_Disconnect hSession 
          sql3_EndSession hSession 
       End If
       
    End Function
    Paul Squires
    FireFly Visual Designer (for PowerBASIC Windows 10+)
    Version 3 now available.
    http://www.planetsquires.com

    Comment


    • #3
      First post updated with link to SQLite3 C/S 0006 (Jan 26, 2008). Fixed a GPF problem. %SQL3_LOCAL mode is lightning fast - the same as accessing the SQLite3 DLL directly.

      (All source code is included).

      Version 0006

      - Added sql_rsColInt and sql3_rsColInt64 to retrieve 32 bit and 64 bit integers from a recordset.

      - Added sql3_Fix. Used to escape embedded quotes in strings.

      - Fixed problem with %SQL3_ENDSESSION that was not removing the database handle from the internal linked list which could caues a GPF.
      Paul Squires
      FireFly Visual Designer (for PowerBASIC Windows 10+)
      Version 3 now available.
      http://www.planetsquires.com

      Comment


      • #4
        First post updated once again to version 0007.

        Version 0007 (2008-01-27)
        - %SQL3_ENDSESSION error in v0006 was only fixed for %SQL3_LOCAL and not also for %SQL3_REMOTE.
        - Added the SQLite3 error codes to the sql3client.inc file.
        Paul Squires
        FireFly Visual Designer (for PowerBASIC Windows 10+)
        Version 3 now available.
        http://www.planetsquires.com

        Comment


        • #5
          For those interested, there have been some major changes to
          the SQLite code. Here's a link to the new release:

          The SQLite site has a new update (version 3.5.5) available that incorporates the new register based machine.
          http://www.sqlite.org/releaselog/3_5_5.html

          Comment


          • #6
            You know what would be really useful? If someone created a visual table designer for sqlLite. MS Access has its "design view" and in MySQL you can create and view tables in a browser. From my limited used of sqlLite, everything has to be done programmatically, unless I'm missing something.

            Mostly I would think this would be useful if one inherited code from someone else and had to examine the table structures. I mostly work with MySql and often will print out the table structure page for reference as I'm programming. Also it would be useful to have a grid where one could browse through the data.

            Just an idea for someone smarter than me.

            Comment


            • #7
              Check this: http://sqlitebrowser.sourceforge.net/

              Bye!
              -- The universe tends toward maximum irony. Don't push it.

              File Extension Seeker - Metasearch engine for file extensions / file types
              Online TrID file identifier | TrIDLib - Identify thousands of file formats

              Comment


              • #8
                Originally posted by Shawn Anderson View Post
                ...a visual table designer for sqlLite.
                As sqlite doesn't require all the b**cks like Oracle, you can just create a table like:
                Code:
                create table shawn ( a,b,c);
                Is this worthy of a visual designer, or (as is often the case) am I missing something?

                Comment


                • #9
                  Marco's post what just what I was thinking.
                  It certainly isn't required, but I think it would be useful.

                  Comment


                  • #10
                    Hey Paul,
                    I can't get your download link to work on your forum.
                    is it still correct?
                    thanks!

                    Comment


                    • #11
                      firefox users

                      sqlite manager addon

                      https://addons.mozilla.org/en-US/firefox/addon/5817

                      Comment


                      • #12
                        Nice! looks like the mySql manager

                        Comment


                        • #13
                          I use the free, personal, edition of SQLite Expert. It works very well. Extremely easy to use. http://www.sqliteexpert.com/
                          Paul Squires
                          FireFly Visual Designer (for PowerBASIC Windows 10+)
                          Version 3 now available.
                          http://www.planetsquires.com

                          Comment


                          • #14
                            Originally posted by Shawn Anderson View Post
                            Hey Paul,
                            I can't get your download link to work on your forum.
                            is it still correct?
                            thanks!
                            Hi Shawn, the latest download links and changes are outlined in this post: http://planetsquires.com/support/index.php?topic=2415.0

                            I am at Version 0009. If you use the code (which is free/public domain) and have some ideas for improvement then by all means please let me know.
                            Paul Squires
                            FireFly Visual Designer (for PowerBASIC Windows 10+)
                            Version 3 now available.
                            http://www.planetsquires.com

                            Comment


                            • #15
                              I had no idea so many tools existed. thanks

                              Comment


                              • #16
                                Completely different

                                Fred Meier's version is the one being used:

                                http://planetsquires.com/sqlite_client_server.htm
                                How long is an idea? Write it down.

                                Comment


                                • #17
                                  Yes, we switched over to a brand new code base and it rocks! The new code is very robust, wicked fast, and supports encryption and compression. It also has server logging and ability to restrict user access to certain files. and, as we proved last week, you can connect to SQLitening Servers over the internet. Installation is a matter of copying a couple of DLL's. Simple.

                                  Still work to be done on the project but Fred and I are working on it (Fred, more so than me). Documentation is the big project at this point.

                                  SQLitening is a client/server (and local if you need it) implementation of the SQLite3 database system. SQLite is used by millions of applications and users around the world. It is SQL based and supports many advanced features such as transactions.
                                  Paul Squires
                                  FireFly Visual Designer (for PowerBASIC Windows 10+)
                                  Version 3 now available.
                                  http://www.planetsquires.com

                                  Comment


                                  • #18
                                    Originally posted by Paul Squires View Post
                                    It is SQL based and supports many advanced features such as transactions.
                                    Paul, for those of us who already have some investment in "vanilla" SQLite, does your product also mimic the standard SQLite API?

                                    Comment


                                    • #19
                                      Originally posted by Chris Holbrook View Post
                                      Paul, for those of us who already have some investment in "vanilla" SQLite, does your product also mimic the standard SQLite API?
                                      Yes it does - very much so. There is a very light wrapper over the top of the SQLite function calls but probably no heavier than what you're using now. Download the package and browse the source code - you can then make up your own mind whether it suits your needs or not. Fred designed the code to be very light.

                                      To be honest, I would love for you to become involved in helping with the project because you have a very good background in using PB and SQLite together. The more people that can help, the better.
                                      Paul Squires
                                      FireFly Visual Designer (for PowerBASIC Windows 10+)
                                      Version 3 now available.
                                      http://www.planetsquires.com

                                      Comment


                                      • #20
                                        Paul I noticed in firefly, it only allows you to choose between pbwin8.0 and pbwin7.0, not pbwin9.0.
                                        KS

                                        Comment

                                        Working...
                                        X