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?
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$ & ")"
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!!
UPDATEOk, 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.
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.
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?
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 :)
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. ;)
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$)
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.
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 :)
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.
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.
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.
Thanks Fred. Things are starting to fit into place now!
Slowly but surely .....
I'm having a problem updating an existing table now. I'm getting the SQL error "19 - Primary Key Must Be Unique" on this statement:
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:
- Creates and opens a file
- Creates a table (or 2)
- Adds some data to the table
- Retrieves some specific items (rows) from the table using a known value
- Updates a row in the table
- Deletes a row in the table
- closes the table
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]
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.
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.