SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: JoeByrne on July 26, 2008, 02:45:49 PM

Title: Basic Use Q&A
Post by: JoeByrne on July 26, 2008, 02:45:49 PM
Ok, I'm trying to get my mind around the basics of SQLightning but I'm stumbling on some (I'm sure) very basic processes.

First, I'm not sure if I should use the 'slBuildInsertOrUpdate' function until I have a better handle on how these things work directly, but for now, that's what I'm using.

I have two tables that are identical in structure.  Column 1 is an AUTOINCREMENT INT primary key.  Column 2 is a simple string.  I'm using the following command to create the table which works just fine.

Quote
SQL$ = "CREATE TABLE 'iCompanies' ( 'companyID' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'CompanyName' varchar NOT NULL );"

Now, I'm reading sequentially through a Tsunami database whose records contain various Comapany Names. (These are duplicated throughout the database).  What I'm trying to do populate the SQLighting Table with unique company names.  My assumption is that the Key (INT field) is automatically created for me whenever I insert a record.  Therefore, I am using this code to 'Insert or Update' this table:

Quote
slExe slBuildInsertOrUpdate("iCompanies", "0" & $NUL & InsCo$)

where InsCo$ is the company name, might or might not be unique as far as the SQL table is concerned.

On the very first execution of this statement, I get the error message:
Quote
19 = PRIMARY KEY MUST BE UNIQUE
Statement = Insert into iCompanies values (0,'Sepctra')

I also tried
Quote
slExe slBuildInsertOrUpdate("iCompanies", $NUL & InsCo$)
But that returns a 'parameter mismatch' (Statement = Insert into iCompanies values (",'Sepctra')

So what is the proper way to insert to a table with an autoincrement int key value?
Title: Re: Basic Use Q&A
Post by: Fred Meier on July 26, 2008, 09:53:24 PM
Joe,

   Here is the code I would use and will give you what I think you want.

   slExe "Create Table iCompanies(companyID INTEGER PRIMARY KEY, CompanyName NOT NULL)"
   slExe slBuildInsertOrUpdate("iCompanies", "Null" & $NUL & InsCo$)

   Notice I omitted AUTOINCREMENT and NOT NULL. INTEGER PRIMARY KEY is
   automatically not null.  AUTOINCREMENT is only required if you want the
   key to be one more than any that ever existed during the life of the table.
   You may want this.  I also omitted varchar from CompanyName.  One of the
   greatest features of SQLite is its manifest typing. Any column can store any
   data type.  Also, the single quotes around table and column names and the
   ending semicolon are not required.  Just makes for less typing.

   You may already understand RowID.  If not then here is a link:
      http://www.sqlite.org/lang_createtable.html
   Your companyID is same as RowID and Select companyID, RowID will result
   in same values.   So you design may work just fine with out compayID cause
   ever table has RowID.

   The below link is FAQ.  Check out (1) How do I create an AUTOINCREMENT field.
   http://www.sqlite.org/faq.html
   
   The below link is good for datatypes in SQLite and manifest typing.
   http://www.sqlite.org/datatype3.html

   Using slBuildInsertOrUpdate is optional.  Its real benifit comes when your code is
   Inserting or Updating in the same routine.  If you are, for an example only Inserting,
   then there is no advantage whether you code
         slExe slBuildInsertOrUpdate("iCompanies", "Null" & $NUL & InsCo$)
                              or
         slExe "Insert into iCompanies (Null," & InsCo$ & ")"

Title: Re: Basic Use Q&A
Post by: JoeByrne on July 27, 2008, 01:52:55 AM
Fred,

Ok, that works great.  I guess I've been looking too closely at the SQLite docs :)

If I may ask another question.

In this table (iCompanies), I want to create a single entry for each company name and automatically assign a unique key to each unique company name. So, I changed my CREATE TABLE command to:
Quote
SQL$ = "Create Table iCompanies(companyID INTEGER PRIMARY KEY, CompanyName NOT NULL UNIQUE)"

e& = slExe(SQL$)
Which does in fact enforce uniqueness.  Next, I loop through my Tsunami database record by record.  In any given record I will find a company name.  If this company name is not in the 'iCompanies' table then I want to insert it and find the unique key ID (which I'll store in the main/old Tsunami record).  If however the company name already exists in the 'iCompanies' table then I need to find out what it's key is in order to store that cross-referenced (foreign) key in my main table.... the old 'one-to-many concept...or is that many-to-one...whatever, I'm sure you get the idea :D)

So the problems I'm not seeing are this:

(1) How do I do a lookup in the iCompany table for one specific value?  Do I need to do a 'SELECT * FROM iCompany' and then loop through all the returned values (record set?) looking for a match?  I've tried various forms of "WHERE" but I just get syntax errors.

(2) Do I have to keep track of what 'CompanyNames' have already been inserted into the 'iCompany' table manually, or do I assume its not there and then react to the returned error to skip it, or is there a way to tell SQLighting, "Add this record if its not already there, otherwise ignore it" ?

Greatly appreciate the assistance!!

UPDATE
Ok, after a bit of digging, I thought I had figured out a method.  I used the following code:

     '---Update Unique Insurance Co Names---
     slExe "SELECT CompanyID from iCompanies WHERE CompanyName = '" & InsCo$ & "'"
     LsA$ = SlF (1)
     IF TRIM$(LsA$) = "" THEN
        slExe slBuildInsertOrUpdate("iCompanies", "Null" & $NUL & InsCo$)
     END IF             
     
     '---Update Unique Office Names---
     slExe "SELECT OfficeID FROM Offices WHERE OfficeName = '" & Office$ & "'"
     IF VAL(slf(1)) < 1 THEN
        slExe slBuildInsertOrUpdate("Offices", "Null" & $NUL & Office$)
     END IF
     

     slExe "SELECT CompanyID from iCompanies WHERE CompanyName = '" & InsCo$ & "'"
     InsCo$ = slf(1)                                                           
     slExe "SELECT OfficeID FROM Offices WHERE OfficeName = '" & Office$ & "'"
     Office$ = slf(1)
     
     slEXE slBuildInsertOrUpdate("main","Null" & $NUL & Office$ & $NUL & ExamDate$ & $NUL & _
                                 FullName$ & $NUL & InsCo$ & $NUL & PayDate$ & $NUL & varRec$)


Which is a bit over-kill I know (looking first to see if the name(s) exist, and if not, add them in, THEN reading the same table again to get the Record ID).  But it seemed like logic that should work.  It all compiles ok, but I get the error message:

Quote
-14 Invalid Set Number

Statement = GetField

Where is this coming from?  GetField?  and what Set Number is being used above?  (I really don't get the whole 'set number' concept I guess).

Again, any help would be appreciated.
Title: Re: Basic Use Q&A
Post by: Fred Meier on July 27, 2008, 11:38:29 AM
Joe,

---- Error 14 from Get Field ----
The four Get Field commands are slF, slFN, slFX, and slFNX. Due to their
frequent usage they have very short non-descript names. You must first do
one slSel and one or more slGetRow commands before using one of these get
field commands.  Your code used the slExe command.  slExe will not return
a set of rows while slSel always create a set of zero or more rows.

---- Collate NoCase ----
You may want to add this to your CompanyName when you create the
iCompanies table.  Without it CoABC and Coabc would both be added.  With
it they are considered equal. 

Create Table iCompanies(companyID INTEGER PRIMARY KEY, CompanyName NOT NULL UNIQUE COLLATE NOCASE)"
--- Many to One ----
Assume there are two tables, tabMany and tabOne which I think relate to
your iCompanies and Main. 

   slExe "Create Table if not exists tabMany(ManyName, OneNameRowID)"
   slExe "Create Table  if not exists tabOne(OneName UNIQUE COLLATE NOCASE)"
   slSel "Select RowID from tabOne where OneName='" & ManyName$ & "'"
   if slGetRow then
      ' The name alredy is in tabOne so save it's ID
      RowID&& = val(slF(1))
      slCloseSet
   else
      ' The name is new so insert it and save it's ID
      ' using the slGetInsertID command
      slExe slBuildInsertOrUpdate("tabOne", ManyName$)
      RowID&& = slGetInsertID
   end if
   slExe slBuildInsertOrUpdate("tabMany", ManyName$ & $NUL & format$(RowId&&))

Another way that also will work and is slightly more efficent cause the
tabOne ID is not stored back in tabMany.  It is not really needed since
the name for tabMany can be used to obtain tabOne just as efficent as the
RowID. 

   slExe "Create Table if not exists tabMany(ManyName)"
   slExe "Create Table  if not exists tabOne(OneName UNIQUE COLLATE NOCASE)"
   ManyName$ = "Joe"
   slSel "Select 1 from tabOne where OneName='" & ManyName$ & "'"
   if isfalse slGetRow(0, "C") then slExe slBuildInsertOrUpdate("tabOne", ManyName$)
   slExe slBuildInsertOrUpdate("tabMany", ManyName$)



If more examples are needed just let me know.
Title: Re: Basic Use Q&A
Post by: JoeByrne on July 27, 2008, 05:15:24 PM
Thanks again Fred.

Quote
The four Get Field commands are slF, slFN, slFX, and slFNX. Due to their
frequent usage ...

I came to this SQLighting thing fairly recently. Are things like this documented someplace other than in the 3 ZIPs you have provided?  I really don't want to keep asking simple questions (and struggling along like I am) if some other documentation exists.  I've scoured the SQL3 web sites, but I've never run across these 'get field commands' so I'm assuming they are specific to SQLighting?
Title: More Confusion with 'Sets'
Post by: JoeByrne on July 27, 2008, 06:59:59 PM
Ok, I used the first example you showed me, storing the RowIDs in the main table for the Insurance Company name and Office Name.  Now when I loop through the main table I get the proper data (YEAH!)

However, while looping through the 'main' database, I need to do a look-up on the iCompanies table to get the Insurance Company Name using the RowID stored in the 'main' table.

I'm using the following code:

   slSel "Select * from main"
   DO WHILE slGetRow
      RecID$     = slF(1)
      Office$     = slF(2)
      ExamDate$ = slF(3)
      FullName$ = slF(4)
      InsCo$     = slF(5)
      PayDate$ = slF(6)
      RTA&      = VAL(slF(7))
      Comment$  = slF(8)                                 

      INCR lvRow&
      '
      '---Insurance Co name---
      '
      slSel "SELECT CompanyName FROM iCompanies WHERE RowID ='" & InsCo$ & "'"   
      InsCo$ = slF(2)

but I'm getting an Error -14 Invalid Set number.

I'm assuming that I need to 'define' a new "set" somehow?  Or do I use some other SQL magic to 'combine' the two tables?

I don't mean to be a pest with all the questions, working without documentation is not as easy as I'd thought.  I am trying to keep notes with each step in order to create some 'new user' docs.....  Hopefully that will be partial payback for all the help here :)
Title: Re: Basic Use Q&A
Post by: Paul Squires on July 27, 2008, 08:51:14 PM
Hi Joe,

If you have two tables (Main, iCompanies) in your database then you can use one SQL statement to do the join. I see what you are trying to do... it is code like one would use in Cheetah. You would loop through the records and lookup extra data in other tables as you iterate through each row. In SQL you should not normally have to do that. You could do the join of the fields during the execution of the SQL statement.

Maybe something like this: (I don't know the field names of your Main table)


slSel "SELECT * from main, iCompanies WHERE main.InsCo = iCompanies.CompanyID;"


I would need to know the field names of each table to properly give you an answer. Basically, you are selecting all fields from both the "Main" and "iCompanies" tables where the insurance company ID stored in the "Main" table matches the insurance company ID in the "iCompanies" table.

Once you stop thinking of databases/tables using procedural thinking then SQL becomes pretty easy.  :)

Don't give up - SQLite is an amazing database system. Once we get the docs done for SQLitening then things will be much easier. Too bad that you're learning the hard way.  ;)



Title: Re: Basic Use Q&A
Post by: JoeByrne on July 27, 2008, 10:21:59 PM
Paul,

Ok, I figured there was a way to get the data from the second table (otherwise SQL wouldn't have any real benefit, would it?).  I see what you're saying in the select statement .... sort of.  Here is the actual command using the field names I used.

slSel "SELECT * from main, iCompanies WHERE main.InsCoID = iCompanies.CompanyID;"

This "works", (compiles) but how do I get the Company Name that is stored in the table iCompanies?


My tables are defined this way:

SQL$ = "Create Table Offices(OfficeID INTEGER PRIMARY KEY, " & _
          "OfficeName NOT NULL UNIQUE COLLATE NOCASE)"
e& = slExe(SQL$)
         
SQL$ = "Create Table iCompanies(companyID INTEGER PRIMARY KEY, " & _
          "CompanyName NOT NULL UNIQUE COLLATE NOCASE)"
e& = slExe(SQL$)
   
SQL$ = "CREATE TABLE 'main' ( " & _
          "'RecKey' INTEGER PRIMARY KEY, " & _
          "'OfficeID', 'ExamDate', 'PatientName' NOT NULL, " & _
          "'InsCoID', 'PaidDate', 'RTA', 'Comments', " & _
          "CONSTRAINT 'OfficeID_fk' FOREIGN KEY ('OfficeID') REFERENCES 'Offices' ('OfficeID'), " & _
          "CONSTRAINT 'InsCoID_fk' FOREIGN KEY ('InsCoID') REFERENCES 'iCompanies' ('companyID') );"
   e& = slExe(SQL$)

Title: Re: Basic Use Q&A
Post by: Paul Squires on July 27, 2008, 10:32:30 PM
Hi Joe,

I expect that you can use slFN to get the field data from CompanyName.

For example:


CompanyName$ = slFN( "CompanyName" )

- or -

CompanyName$ = slFN( "iCompanies.CompanyName" )



... at least, I think that will work.  :)

The reason you can reference the "CompanyName" field is because in your SELECT statement you are requesting all fields from both tables.  SELECT * from main, iCompanies   You could also specify specific fields from each table rather than using the asterisk "*" wildcard to get all fields.

Title: Re: Basic Use Q&A
Post by: JoeByrne on July 27, 2008, 10:44:42 PM
YEA!  I figured it out.  This version of the SELECT command appends the data to the record set returned!!!  It seems like there would be a more efficient way to accomplish this, but for now, I'll accept the process.

Thanks for the pointer in the right direction..... back now to tackle the next step :)
Title: Re: Basic Use Q&A
Post by: Paul Squires on July 27, 2008, 10:56:37 PM
In theory it may not sound efficient, but in practice it is very fast and efficient. This is because SQLite does not create the entire recordset of data before allowing you to iterate the results. Basically, SQlite compiles your SQL statement into a kind of byte code that gets executed when you move from row to row. As you move to a new row, SQLite will generate the row data for you (kind of on-the-fly) and allow you to access it. In Local mode this is *extremely* fast.

In client/server mode, SQLitening will buffer a number or rows in order to optimize the TCP connection (you wouldn't want every row sent via a TCP call; it is much more efficient to have a 100 rows sent for example). Whenever slGetRow can no longer get more rows from the cache it will request another cache from the server and so on until all rows in the recordset have been sent from the server to the client.

Title: Re: Basic Use Q&A
Post by: JoeByrne on July 28, 2008, 02:47:52 AM
I'm thrilled to report that more lightbulbs have gone off!

I have successfully modified my app (well, most of it) from Tsunami to SQLightning!  The idea of joining tables with the slSEL makes much more sense now.  I even did it successfully with three tables at once!!

My next big step will be to add/update the tables, but from what I've seen already, I think that will be much easier (I've already written the conversion app so the basic logic I will need is done!).  I have been diligently taking notes so I hope to have some documentation to contribute to help anyone else just starting out.

Looking forward to the wiki .... that should be a great repository.

Thanks again to Fred and Paul for all the help thus far.  It is greatly appreciated.
Title: Re: Basic Use Q&A
Post by: Fred Meier on July 28, 2008, 01:13:33 PM
Joe,

   Hope you don't mind me pointing out some hints I learned the hard way.

   Every active set must have a unique number.  A new active set is started
   by a slSel command.  If a set number is omitted in the slSel command then
   zero is used.   It may be best for you to always code the set number until
   it becomes routine.  The following two slSel commands are identical.

         slSel "Select * from T1"
         slSel "Select * from T1", 0


   In your posted code snippet of (I added set number zero):
   
      slSel "Select * from main", 0   
      DO WHILE slGetRow(0)     
         RecID$     = slF(1, 0)     
         Office$    = slF(2, 0)     
         *** etc etc etc***

         slSel "SELECT CompanyName FROM iCompanies WHERE RowID ='" & InsCo$ & "'" , 0
         InsCo$ = slF(2, 0)[/td]


   The inter slSel got error -14 cause it was trying to use set zero which was already
   active with the first slSel.  If you would have used a set number of one (or two
   or three, or ...), as shown below, it would work fine (with the additions).   

        slSel "SELECT CompanyName FROM iCompanies WHERE RowID ='" & InsCo$ & "'", 1
         if slGetRow(1) then
            InsCo$ = slF(2, 1)
            slCloseSet(1)
         end if


   Many of SQLitening commands have the optional set parm.  The most used ones are
   slSel, slGetRow, and GetField(slF, slFN, etc).  So you must be sure to select the
   proper set number on those commands.  Believe me, it will become routine after a while.

   Using Select * is very powerful and easy to code but may cause maintenance problems.
   What if Select * was used through out an application and then the table is re-structured
   to add a column which will contain a huge text or blob value.  Now all the Select *
   commands will start returning this extra huge field.  May not be so good.

   Also in your posted code you are using slF instead of slFN.  Yes, slF is slightly
   faster than slFN but the later is much easer to maintain and better documentation.
   Example -- Your posted code does Select * and you are assigning the first eight fields.
   What if you re-structured that table and inserted a new column after column three.
   The slF code would break while the slFN would continue to work fine.

   Joins are normally the better way to accomplish a query but sometimes doing an explicit
   inter select, as your posted code was doing, is better.  What if you only wanted the
   company name based on some complex evaluation of the Main row which you know results
   in only a few cases.  Then the inter select would probable be better.

   Yes, the GetFields(slF, slFN, etc) are specific to SQLitening.  You must use one of
   them to retrieve a field's data.

   The only current SQLitening documentation, at this time, is in the SQLitening.Txt file.
   That is documntation of each SQLitening command only. Nothing on when to use the commands.
Title: Re: Basic Use Q&A
Post by: JoeByrne on July 28, 2008, 08:17:03 PM
Thanks Fred.  Things are starting to fit into place now!
Title: Re: Basic Use Q&A
Post by: JoeByrne on August 03, 2008, 03:16:08 AM
Quote
slEXE slBuildInsertOrUpdate("main",RowID$ & $NUL & FORMAT$(OfficeID&&) & $NUL & _
                                     ExamDate$ & $NUL & FullName$ & $NUL & FORMAT$(CompanyID&&) & _
                                     $NUL & PaidDate$ & $NUL & FORMAT$(RTA&) & $NUL & Comments$ & _
                                     " WHERE RecKey = " & RowID$)

Ok, I understand that I am passing the value for the key (RecKey), but if I try to pass it as Null, nothing gets updated.  There are no error messages, but the table values don't change.  It would seem logical to me that if I'm passing Null with my WHERE clause, that SQLite should know to UPDATE the values in that row.  Inserting doesn't seem to be a problem, but updating is driving me nuts..... as is trying to write code blind like this.

Is there any chance that someone could put together a simple example that:

While the existing examples are nice to show off performance, they really lack demonstrating the primary functions commonly used with a database (new, update, find, delete).  If we could see these functions in code, it would make it a lot easier to work without docs.

Thanks!
--Joe

PS: If the above example could be done, it would be most excellent to see the use of recordsets.  Preferably more than one, but I'd like to see the examples with all the optional parameters passed in each function.  Its much harder to figure out why something fails when there are optional parameters and the examples don't pass them :)[/list]
Title: Re: Basic Use Q&A
Post by: Fred Meier on August 03, 2008, 10:34:52 AM
Joe,

You are right, the current examples are not good for learning how to write
a complete application using SQLitening.  I will take the first cut at
doing a better one and post it in a new topic in a day or two. 

Your current error -19 is caused because the slBuildInsertOrUpdate
function is returning an Insert statement while you wanted an Update.
The last part of the error message probable reads...
      Statement = Insert into main .......

The slBuildInsertOrUpdate function takes four parms: Table, Values,
Columns, and Where.  All four are required for an update.  Your example
only passed two.  It is the fourth parm that determines if it's an Insert
or Update.  If it is omitted or empty then will build an Insert statement
else will build an Update statement appending " Where " and the Where
value. 

This example will result in an Update (only using two columns and made up the column names)

   slExe slBuildInsertOrUpdate("main",
                               RowID$ & $NUL & format$(OfficeID&&),
                               "ColNameRowID, ColNameOfficeID",
                               "RecKey = " & RowID$)


I agree that the doc is short.  If in doubt what the slBuildInsertOrUpdate is
returning, pass a copy of the slBuildInsertOrUpdate statment to msgbox so you
can see exactly what it builds.
Title: Re: Basic Use Q&A
Post by: JoeByrne on August 04, 2008, 01:14:37 AM
Thanks Fred, I appreciate the help.

After studying the SQLite docs, I think I've figured out how the UPDATE command works, and for me, I think that's a better route than using the slBuildInsertOrUpdate function.  In this case particular, I will know for sure if the record is new or not, so it should be pretty straight forward to construct the proper SQLite statement.