Entering Data into SQLite

[Curt]

Back in the days of SuperCard version 2.5 I had written a medical records database program in HyperCard and then in SuperCard. I actually constructed the database within SuperCard using fields, so of course it was limited, yet I was getting quite good at SuperCard programming. HyperCard/SuperCard was my first programming language (I want to return to it). The database limitations of SuperCard however, along with a chorus of voices that requested a PC-based application brought me to rewrite, with a programmers help, the application in FoxPro 2.6. It has been a wonderfully functioning program ever since on the PC platform.

VistaOS does not run 16-bit applications, which gave me a wonderful opportunity to return to the Macintosh. At present I'm running my FoxPro program using VMware on my new MacBook.

I was very excited to purchase the latest version of SuperCard and now I'm trying to connect to databases such as SQL light or MySQL. I am now planning to write a nutrition database using FoxPro as the front end and brain of the application.

However, I have found this to be very frustrating. I have no previous experience with these databases, and I'm finding it difficult to code the connections properly using SuperCard and the external written by Thomas Franzén.

My first question is whether his SQLite external really works for entering data into a new database. The program that I downloaded from the Internet seems to work fine on queries but it gives scant advice on how to code/script the SuperCard interface to enter data into a database. He seems to presuppose a lot of knowledge and experience. Mostly I need reassurance that it does work and that there is some place for me to see examples and learn how to work it.

I would appreciate any help you can give on this issue.

Curt

[vinnie-bob]

Hi Curt,

I haven't played with Tomas' external in quite a long while, but I recall that it did work for both queries and adding data. However, you don't need an external to read from or write to a SQLite database. This can be managed via shell scripting with sqlite3. You may want to have a look at the man page for sqlite3 (launch terminal, type: man sqlite3. then hit return. To scroll the man page down, press spacebar, to go back a page, press b, to exit, press q).

I have uploaded a very limited project which gives you some insight into how this might work in the "Sample & Demo proj" area of this board since file attachments don't seem to work here. First create the data in the first,last,book fields, then click "Add to database". It will automatically create a database if one does not exist, and will add to it, if it does exist. Add one or two more records to the database. Then to retrieve all the data you entered, click the "Show all" button in the search part of the project. You can search on individual fields, but to get something back, you need to enter the exact name that is in the database. (eg, if first name contains "Vincent", searching for "Vince" in the first name field returns nothing, but the full name will produce a result) You can search for parts of names in fields, but for this example, I didnt add that.

I think Tomas' external example contains a link to a good tutorial on SQLite which can be used to make custom searches, and will show you how to add data to the database in more detail. Tomas' external allows you access to most of the sqlite commands, so you might also look on the SQLite documentation page as well.

HTH

[bmartin]

Tomas's External has been replaced by Alec's External, of the same name I believe. It is made for the newer versions of SC and does not require Bundle Bridge to work. I use Alec's external in a production application I wrote for work, and so far it has not let me down.

[JoshRu]

This sqlite3 is intriguing. It only works with OSX right?
IOW, it wouldn't work on a G3 running OS9.2. but will work
on a G4 running OS 10.4.11 and SC4.5.2 I assume.
-- Josh

[Dave_Higgins]

Tomas's External has been replaced by Alec's External, of the same name I believe. It is made for the newer versions of SC and does not require Bundle Bridge to work. I use Alec's external in a production application I wrote for work, and so far it has not let me down.

Yeah. I found it to work fabulous. Very fast. I made a few more wrappers that lead to the included wrappers, so now all I have to do is (for example):

put "select * from users" into tQuery
put dbQuery(tQuery) into tUserData

However, I have found this to be very frustrating. I have no previous experience with these databases, and I'm finding it difficult to code the connections properly using SuperCard and the external written by Thomas Franzén.

My first question is whether his SQLite external really works for entering data into a new database. The program that I downloaded from the Internet seems to work fine on queries but it gives scant advice on how to code/script the SuperCard interface to enter data into a database. He seems to presuppose a lot of knowledge and experience. Mostly I need reassurance that it does work and that there is some place for me to see examples and learn how to work it.

As for learning the SQL part...W3Schools, man. Start here <http://www.w3schools.com/sql/sql_syntax.asp> and keep going with the "Next" button. There are a very few "minor" things that differ in SQLite, but for the most part you shouldn't bump into them.

The best place to start is to copy the wrappers from the project script of the sample project to your own, from "-- START OF WRAPPERS" to "-- END OF WRAPPERS". You can delete the function runBundleExternal, since it's no longer required (I think you can pull that from the sample, Alec).

My dbQuery wrapper goes like this...

Code: Select all

function dbQuery query, tItemDel, tLineDel
    put dbOpen(dbFile()) into dbID
    if tLineDel is "" then put "cr" into tLineDel
    if tItemDel is "" then put "|" into tItemDel
    get SQLite_Query (dbID, tLineDel, tItemDel, query)
    dbClose dbID
    return it
end dbQuery

function dbFile
    -- hard wired database name
    return "wkc.db"
end dbFile

function dbOpen dbPath
    get SQLite_Open(dbPath)
    return it
end dbOpen

function SQLite_Open dbPath
    -- This is a modified version of the SQLite_Open function from the sample project
    global gLP_defaultSQLiteFolder
    -- gLP_defaultSQLiteFolder is a global that contains a default folder for my db files
    -- so I can make calls without dealing with the path... Just the db name
    if not (dbPath contains ":") then
        -- It's only a name, not a whole path provided in dbPath
        put gLP_defaultSQLiteFolder & ":" & dbPath into dbPath
    end if
    get SQLite("open",dbPath)
    return it
end SQLite_Open

on dbClose dbID
    get SQLite_Close (dbID)
end dbClose


So, now using their examples at w3schools, you would start with

Code: Select all

put "CREATE TABLE Persons (P_Id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));" into dbQuery
get dbQuery(tQuery)
-- no reply necessary


Then, to populate it you can use:

Code: Select all

put "INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger');" into dbQuery
get dbQuery(tQuery)
-- no reply necessary


And so on...

After a few inserts, you can retrieve with:

Code: Select all

put "SELECT * FROM persons WHERE LastName like 'Nilsen';" into dbQuery
get dbQuery(tQuery)


-- result is
4|Nilsen|Johan|Bakken 2|Stavanger

All SQL queries can be sent that way. You can also build multi-line queries. Just make sure that there is a semi-colon at the end of each line. This is good for multiple insert commands.

You can also shorten up some other wrappers by using something like

Code: Select all

function dbTables
    put dbOpen(dbFile()) into dbID
    put SQLite_Tables (dbID) into tTables
    dbClose dbID
    return tTables
end dbTables

function dbColumns tableName
    put dbOpen(dbFile()) into dbID
    put SQLite_Columns (dbID, tableName) into tColumns
    dbClose dbID
    return tColumns
end dbTables

function dbColumns dbID, tableName
    return SQLite_Columns (dbID, tableName)
end dbColumns


At this point, in our examples:

get dbTables()
would return:
Persons

get dbColumns("Persons")
would return:
P_Id
LastName
FirstName
Address
City

Hope that can help get you started.

[Dave_Higgins]

This sqlite3 is intriguing. It only works with OSX right?
IOW, it wouldn't work on a G3 running OS9.2. but will work
on a G4 running OS 10.4.11 and SC4.5.2 I assume.
-- Josh

Yeah. I doubt you could easily get it to run in OS9.x

[HairyHighlandCow]

Hi,

The latest version of the SQLite external can be found at the address below. It requires SuperCard 4.6 or higher to function. I recommend using it rather than the shell, but if you have SC 4.5 then you'll have to use that. You can download the external here:

http://www.hairyhighlandcow.net/SuperCa ... ternal.zip

I use the external regularly in my applications built with SuperCard (see http://www.hairyhighlandcow.net/softwar ... rMain.html) and it functions well for both retreiving and inserting data into databases. Mike Yenco also uses it in his applications (see http://www.yenco.com). If you have any questions about how to insert data into a database using the external, please ask and I will try to help.

thanks

Alec

[Lisa]

Thanks, guys, for the sample projects and all the useful info. I have an old (SC3) project that needs rebuilding from the ground up and was wondering if a sql db would work for storing the data. Does anyone know if there's a limit to how much text you can have in an entry? Some of my entries might be 500 characters or more. Would sqlite be feasible? Thanks.

[Dave_Higgins]

Thanks, guys, for the sample projects and all the useful info. I have an old (SC3) project that needs rebuilding from the ground up and was wondering if a sql db would work for storing the data. Does anyone know if there's a limit to how much text you can have in an entry? Some of my entries might be 500 characters or more. Would sqlite be feasible? Thanks.

Oh yeah. I've put a couple K of data into individual "fields" with no problem. I think your db size will only be limited to how much HD room you have.

[Curt]

Frankly, I am overwhelmed and gratified by the response to my question. I was a little uncertain when I bought the latest version of SuperCard after being away for so many years, because it is not well represented on the Internet. However, I ought to have expected that most SuperCard users would not need that much support. The language and interface are pretty easy to learn, when compared to most other computer programming languages.

I did learn how to connect to the SQLite database by consulting one of my computer guru patients. I will be studying the code that you folks offered when I have time this weekend, to see if there's a better way to connect and even if there is a good way to connect to MySQL databases. It's nice to know that the DATABASE GAP is finally being closed in SuperCard. I my opinion that GAP is the one obstacle to its becoming THE dominant number one programming language.

Thank you all for being out there and so quick to respond. It's great to be back in the creative world of SuperCard again and to know that there is help when I need it.

Curt

[HairyHighlandCow]

Hi Lisa,

The databases can store a lot of data. I have one which I store images in,

thanks

Alec

[JoshRu]

Taken from Alec's updated project...

http://www.hairyhighlandcow.net/SuperCard/SQLite%20External.zip

...I've installed SQLite 3.4.2. into an existing project just to start testing simple calls since I'm new to this. I altered some of his wrappers and function calls to my needs and just started playing with open and close, but already there seems a problem. After I simply open/create a database and put the connection ID into the message box, the whole project bogs down to a crawl and is almost unresponsive. The cursor seems ok, but every click, even my up and down arrowkey handlers take forever to respond. The moment I quit and re-open the project (without making any calls to SQLite), everything is snappy again. Running OS 10.4.11 on G4, SC 4.6.2.

Here is an example of my altered 'open' and 'close' wrappers and function calls:

Code: Select all

------------ My altered wrappers in a window script: -----------

function SQLite_OpenFKIJobberDatabase
  global gSQLconnectID
 
  if gSQLconnectID = empty then   -- No database previously opened this session,
    --                            -- Proceed to open/create it at project-folder level
    put projpath(this proj) & "FKIJobberDatabase" into databasePath
    get SQLite("open",databasePath)
    if isNumber(it) then
      put it into gSQLconnectID
      return it
    else
      beep
      answer "Unknown database problem." with "Exit"
      exit to supercard
    end if
  else
    beep
    answer "Database already open  =  # " & gSQLconnectID
    return gSQLconnectID
  end if
end SQLite_OpenFKIJobberDatabase


function SQLite_CloseFKIJobberDatabase
  global gSQLconnectID
 
  if gSQLconnectID = empty then  -- no database previously opened this session, exit now
    beep
    answer "No FKIJobberDatabase open." with "Exit"
    exit to supercard
  else
    get SQLite("close",gSQLconnectID)
    put empty into gSQLconnectID
    return it
  end if
end SQLite_CloseFKIJobberDatabase


---------------- From my test popup menu  ----------------

on itemSelect  -- for Open
  get SQLite_OpenFKIJobberDatabase() -- from my own wrappers in WD
  put it                             -- debug
end itemSelect


on itemSelect  -- for Close
  get SQLite_CloseFKIJobberDatabase()
  put it                              -- debug
end itemSelect



Wondering if there's a memory leak or something in the external? Thanks all. -- Josh

[HairyHighlandCow]

Hi Josh,

I've been using the latest version of SQLite in my main application, PreMinder, for a long time now, as well as many smaller SC projects. Mike is also using it without any problems in his latest application releases. I have carefully tested the external and not noticed any memory leaks yet, and I haven't had any other reports from Mike or anyone else using it, or end users, with the same problems you seem to be having.
Have a look in the Activity Monitor application and check SuperCard's memory usage.
Also, check there are no other externals in use which might be causing a memory leak.
Finally, from first glance I think your wrappers are OK, I also use a modified set of wrappers which work along the same lines now, using a global variable for the session ID-- the original wrappers were not written by me. However, they may not be working as intended. Make sure sessions are being closed properly before new ones are opened. Make sure to close a session when you are finished with the database and not leave it hanging around. Writing data will always be much faster if you open a transaction, write all your data, and then close the transaction.

cheers

Alec

[JoshRu]

Thanks Alec. I'll look into this further, just starting out. Am hoping this has nothing to do with the G4 box and is just something I'm overlooking. The G4 is at my work and is connected to a couple servers, one using a Terminal session so who knows what might be going on under the hood. -- Josh

[JoshRu]

Alec, speaking of using the transaction, could you post an example of how that might look? It's not altogether clear to me yet.

Thanks. -- Josh

[HairyHighlandCow]

Hi Josh,

The order will work something like this:

SQLiteOpen

(Do some stuff, maybe read a few things from the database if needed before we start writing to the database)

SQLiteBeginTransaction

Write some data to the database. Maybe a few 'UPDATE' queries or a few 'INSERT' queries.

SQLiteEndTransaction

SQLiteClose

The wrappers I use are below. I have altered them a bit before posting so they haven't been tested in exactly the format they appear here, but the basic principle is if you try to open a session when one is already open then the wrapper will use the existing session number. If you try to open a transaction when one is already open, it won't attempt to open another one. If you close the session when a transaction is open it will close the transaction and then the session.
Any other calls to the external need to use the session number stored in the global variable sqlCurrentSession.
I have some other wrappers, I can post more things later if they are useful.

thanks

Alec

Code: Select all

on SQLiteOpen
global sqlCurrentSession,databasePath

if sqlCurrentSession=empty then
put SQLite("open",databasePath) into sqlCurrentSession
sqlMsg "Open" && sqlCurrentSession
else
sqlMsg "SQL connection already open"
end if
end SQLiteOpen

on SQLiteClose
global sqlCurrentSession,sqlTransactionActive

if sqlTransactionActive = true then
SQLiteEndTransaction
end if

if sqlCurrentSession<>empty then
sqlMsg "close" && sqlCurrentSession
get SQLite("close",sqlCurrentSession)
put empty into sqlCurrentSession
end if
end SQLiteClose

on SQLiteBeginTransaction
global sqlCurrentSession,sqlTransactionActive

if sqlCurrentSession<>empty and sqlTransactionActive=false then
sqlMsg "begin transaction" && sqlCurrentSession
get SQLite("begin",sqlCurrentSession)
put true into sqlTransactionActive
end if
end SQLiteBeginTransaction

on SQLiteEndTransaction
global sqlCurrentSession,sqlTransactionActive

if sqlCurrentSession<>empty and sqlTransactionActive=true then
sqlMsg "end transaction" && sqlCurrentSession
get SQLite("end",sqlCurrentSession)
put false into sqlTransactionActive
end if
end SQLiteEndTransaction

on sqlMsg tMsg
if tMsg is not empty then
put tMsg into message box
end if
end sqlMsg

[JoshRu]

Thanks so much for that. So if I understand correctly, Transactions are faster mainly because they are done back-to-back in memory, not written to disk. Does that mean they are written to disk at the end of the transaction ? -- Josh

[JoshRu]

Also one more thing... after replacing Null characters with empties, you're supposed to escape quote characters also (with double quotes I think). Are there any pitfalls to this approach, IOW, after massaging data in the database, putting the double quotes back to singles using simple replace function? Thanks again -- Josh

[HairyHighlandCow]

Hi Josh,

Yes, when you end a transaction you are doing the equivalent of committing all your changes to the database. There's some detailed information about it here:
http://www.sqlite.org/lang_transaction.html

When it comes to putting text into a database using the external, I replace quote characters with two quotes following each other, which is the required escape. Nulls should be removed or replaced with a safe character:

Code: Select all

put replace(tText,quote,quote & quote) into tText
put replace(tText,null,"") into tText

When you read the text back from the database, all the quotes will appear as they did before you did the replace, so you won't need to do anything to the text once it comes out of the database. However, if you had to store null characters and you replaced all of these with a safe character, you need to do replace all instances of the safe character with null after reading the text out of the database.

cheers

Alec

[swoollam]

Hi Curt,

I am actually looking for help. I have an application that was developed in supercard 2.5 - it is a children's story with animated water molecules. I am now wanting to go in and access the pictures and text but my friend who did the programming can't remember how to get from the application to the programming environment. As a 2.5 user, could you remind me how to do it? I am also curious about your nutritional data base - did you complete that project?