SQLITE database Form view rather than Table or Datagrid

[WalterCo]

Hello,

I have been working with LiveCode SQLITE database connections and information received from my last post. (Thanks) I have learned how to connect to a database, add, update, delete, search (General searches) and close a database.

I have used the datagrid to show the data. But I’m still having problems understanding how to do the following: (By the way, I’m specifically interested in MOBILE APPLICATIONS IOS,ANDROID and using SQLITE as opposed to the “Behave like Background type card database”)

1.As stated above, I’m starting to understand how to work with databases in LiveCode.
BUT, everything that I’ve found talks about using a database with a table file or datagrid. What about using a database form view. I.E. Contacts DB with a form that has: “Name, Address, City, State, Zipcode. I know that the DataGrid objects has a setting called Style and the options are “Table” or “Form” but I’m not sure how to configure the form setting. I guess I’m describing something that looks like the “Behave as a background” way of creating a database with the “New, Previous, Next, Delete, Search” buttons at the bottom but that type of hypercard type DB can only hold a few thousand records. That’s why I want to use the device local SQLITE database but not in a table but a form view. (Or even a combination of the two.)

My real question is … Can you place fields of a connected DB down on a card where you want them? In code or otherwise. And them be able to MovetoNext Record one at a time without creating a new card for each record. (Kind of like a Filemaker Pro database) Someone said to me, well why don’t you just use Filemaker and my answer was that I want to create MOBILE cross-platform applications and I don’t like the feel of Filemaker Go for mobile IOS applications.

I think I’ve learned that the Database Query Builder will not help me when it comes to Mobile. Is this true? I got it to work on a Mac standalone but not as an IOS standalone. Maybe this is why it’s not supported by LiveCode any longer. Although, it seems like a very powerful tool. (I could do almost everything that I’ve talked about above minus more detailed search or find requests, with the Database Query Builder)



2. How do you get the input from an end user and put it into a search field? I have no problem putting the SQL statement to return all persons named “Robert” within the code BUT how do you accomplish this by taking the user input at runtime? Example: I have a field named “FirstName” and I want the end user to be able to type a name they are looking for say “John” and then hit “OK” and then all records with the name “John” as the first name and are put into a found set. Now, the user is able to view each record by hitting the next button which takes the user to the next record with “John” as the first name. This continues until the last record is reached in the found set and code turns the “Next” button disabled to mark then end of the found set. (Not sure if I’m explaining this properly but is the kind of search I want to do.

Thanks for any further direction.

Walter

[Adrian]

If I understand what you are wanting, you need to program the interaction between your database and fields on a card.

For example, you could create a card with fields for name, address, etc. and buttons for "next", "search", or whatever. The code in the buttons would perform the database query (for "next", for example), or database update (such as for a "save" button). For queries, the code would put relevant data into the fields on the card; for updates it would get the entered vales from the card.

Searching would follow the same approach. You could have a search field(s), or use the main input fields - whichever works best. The code would likely be in a "search" button, or similar, and would perform the SQL search. There are various ways of preserving the found record set and showing the relevant record on "next"/"previous".

Adrian.

[Thierry]

Hi Walter,

Have you looked at the 2 SQLite Sampler stacks you can find on Revonline?

Guess you'll have your answers and more here...

Regards,

Thierry

[WalterCo]

Hi Thierry?

Thanks for you response and yes I have looked at both SQLite Sampler Stacks several times in fact that's how I learned to how to connect to a database, add, delete, update, and search records but I still have not found a way to have ONE record on the screen at a time and then be able to move back and forth using a "Previous" or " Next" buttons. Add new records by clicking on a " New" button and then all the fields of the record clear out and the user can add the info for the fields and then clck "Save" I know that this can be done in using the " Behave like background " and grouping fields together in LiveCode but I specifically want to use SQLite on the local iphone device.

Thanks, Walter.

[Thierry]

Something like that?

repeat until revQueryIsAtEnd(tCursor)
...
revMoveToNextRecord tCursor
end repeat

In the dictionary, look for revQuery....
You'll find a lot of functions to manage a recordset

[Simon]

Hi Walter,
I think you can take a step back and take a breath, those lessons are... well a learning experience. Don't mean to sound condescending (really don't) but it's not difficult in liveCode

Go ahead and just set up a card with all the needed fields. (no need for behave like background or anything)
This assumes you have a unique id for each record (you should have).
Next button

Code: Select all

add 1 to theNewID 
put "SELECT * from table_name WHERE id =" && "'"& theNewID &"';" into tSQL
put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords

tRecords will contain all the information held with that unique id, you just have to parse it out into your fields.
Previous is subtract 1 from theNewID

New is just empty all the fields
Save is

Code: Select all

put "INSERT into table_name (first_name, last_name, address, etc.) VALUES " && "(" & fld 1 & comma & fld2 &comma& etc. ");"  into tSQL 
revExecuteSQL gConnectionID, tSQL

http://www.w3schools.com/sql/sql_insert.asp

Of course you have to set up that theNewID with a value to start with.
Oh and you have your db connected

All can be done on a single card.

Simon