Page 1 of 2
Newbie on Livecode
Posted: Sat May 10, 2014 6:43 pm
by eyonmac
Hello,
Sorry for my bad english language ...
I try to build a little database in order to keep specific informations and I have used the step-by-step guide How to create and use an SQLite database. It works perfectly, but I would want to modify something. In the code, we insert the data by the sql command :
Code: Select all
put "INSERT into informations_table VALUES ('test');" into tSQL
But I would want to fill in the informations_table with the text that I have written in a scrolling field called "saisie". So, I don't know how to do ... I've created a local variable "champsaisie" and put what is is in the field :
Code: Select all
put field "saisie" into champsaisie
. Is there a possibility to insert the value of this variable into informations_table ?
Thanks a lot for your help !
Michel
Re: Newbie on Livecode
Posted: Sat May 10, 2014 6:52 pm
by Klaus
Bonjour Michel,
1. welcome to the forum!
2. You need to create the SQL command string with the name of the variable like this:
...
put field "saisie" into champsaisie
put "INSERT into informations_table VALUES ('" & champsaisie & "');" into tSQL
...
C'est ça!
This is hardly visible -> '"
It is a SINGLE Quote ' and a QUOTE "
Best
Klaus
Re: Newbie on Livecode
Posted: Sat May 10, 2014 6:56 pm
by eyonmac
Thanks a lot Klaus !
It works perfectly !!
Have a goo day !
Re: Newbie on Livecode
Posted: Sat May 10, 2014 7:02 pm
by eyonmac
Excuse me, another question :
in the database, there's only the first line I have written in the field, but not all the line (5 lines separated by return key). How can I do to take it all ?
Thanks a lot again !
Re: Newbie on Livecode
Posted: Sat May 10, 2014 7:26 pm
by Klaus
Bonsoir Michel,
that should work correctly with the above script!?
You mean, if you retrieve that db field it only has ONE line instaed of the original 5 lines?
Hm, no idea, can you post some more of your script(s)?
Best
Klaus
Re: Newbie on Livecode
Posted: Sat May 10, 2014 7:36 pm
by eyonmac
Bonsoir Klaus,
Do you speak french ?
I've also tried this :
Code: Select all
repeat for each item champsaisie in saisie
put "INSERT into informations VALUES ('" & champsaisie & "');" into tSQL
,
but it gives the same thing and not sure the code is correct.
Here is the code in my card :
Code: Select all
local sDatabaseID
## la commande setDatabaseID affecte le numéro d'identifiant de la connexion à la base dans une variable locale
command setDatabaseID pDatabaseID
put pDatabaseID into sDatabaseID
end setDatabaseID
## la fonction getDatabaseID retourne le numéro de la connexion afin qu'elle puisse être utilisée par éléments du programme
function getDatabaseID
return sDatabaseID
end getDatabaseID
command databaseConnect
local tDatabasePath, tDatabaseID, champsaisie
## La base de deonnées doit être à un emplacement accessible en écriture
put specialFolderPath("documents") & "/sofip.sqlite" into tDatabasePath
## On ouvre la connexion vers la base de données
## Si la base de données n'existe pas elle sera créée
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
## Store the database id so other handlers can access it
setDatabaseID tDatabaseID
end databaseConnect
## L'évènement création de table se produit lorsqu'e l'on clique sur le bouton ajout de table
on databaseCreateTable
## On ajoute la table informations dans la base de donnéees
put getDatabaseID() into tDatabaseID
put "CREATE TABLE informations (infos char(50000))" into tSQL
revExecuteSQL tDatabaseID, tSQL
end databaseCreateTable
on databaseInsertInformations
## j'insère les infos du champ saisie dans la base
put getDatabaseID() into tDatabaseID
put field "saisie" into champsaisie
repeat for each item champsaisie in saisie
put "INSERT into informations VALUES ('" & champsaisie & "');" into tSQL
revExecuteSQL tDatabaseID, tSQL
end repeat
end databaseInsertInformations
function databaseGetInformations
## Query the database for contact details to be displayed in the field put getDatabaseID() into tDatabaseID
put "SELECT * from informations" into tSQL
put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
return tRecords
end databaseGetInformations
Don't worry, it's not urgent. I have to go and will see it tomorrow.
Have a good day,
Best
Re: Newbie on Livecode
Posted: Sat May 10, 2014 8:51 pm
by Klaus
Could you please also post the content of your variable "saisie"?
Do you speak french ?
Un petit peu
Re: Newbie on Livecode
Posted: Sat May 10, 2014 8:59 pm
by eyonmac
It was just an exemple. Here is the content of the field :
Exemple de phrase tapée dans le champ intitulé Saisie
Seconde phrase tapée dans le champ
Thanks a lot and have a good night
Re: Newbie on Livecode
Posted: Sun May 11, 2014 9:17 am
by eyonmac
Hello Klaus,
That's good ! It works perfectly !
The problem came from another software with which I opened the sqlite database : it took only the first line on one record !
In Livecode, I put another field for the preview of the data and there I really have all the lines of my text registered in the database !!
Have a good sunday
Michel
Re: Newbie on Livecode
Posted: Sun May 11, 2014 12:07 pm
by Klaus
Bonjour Michel,
OK, glad you got it working.
But here some general hints I observed in your scripts:
1.
...
repeat for each item champsaisie in saisie
...
But the content of your field "saisie" does only have ONE item in it!?
At least I do not see any COMMA in it?
2.
...
put revDataFromQuery(tab,RETURN,tDatabaseID,tSQL) into tRecords
...
This will fail if you have MULTI-LINE (= containing a CR/RETURN) text in the database fields!
In these cases I'd use another LINE delimiter for the returned database values like -> numtochar(2)
Best
Klaus
Re: Newbie on Livecode
Posted: Sun May 11, 2014 2:14 pm
by eyonmac
Bonjour Klaus,
Thanks a lot for your help !
1 - Yes, I 've forgotten the comma in the line (it's the second day only I work on Livecode ...) and I have deleted it as it seems to work.
2 - Super ! I agree with you : my text is effectively multi-line. So, ok, I've deleted the return and put the numtochar(2) expression instead. Yet, I've another problem : the text is not saved in the database if there is an apostrophe : '. How can I do ?
2 other questions :
- I've got a button with which I open and make the connection with the database. Would it be possible to open the database directly at the startup of my program (without the use of the button) ? I've looked after this possibility but did not find.
- I'd like to do a search about a term (apb for instance) in the database and display it in the field. No result for the moment. I did this :
Code: Select all
function databaseGetInformations
## Query the database for contact details to be displayed in the field put getDatabaseID() into tDatabaseID
put getDatabaseID() into tDatabaseID
put "SELECT * from informations where infos = apb" into tSQL
put revDataFromQuery(tab,numtochar(2),tDatabaseID,tSQL) into tRecords
return tRecords
end databaseGetInformations
I don't see where is my mistake ...
Well, I've changed the code like this :
Code: Select all
put "SELECT * from informations WHERE infos = 'apb' " into tSQL
and it works ! But only if the word apb is an alone record, not in a sentence ...
Best
Re: Newbie on Livecode
Posted: Sun May 11, 2014 4:37 pm
by Klaus
Ah, sounds like you are looking on how to use WILDCARDS in SQL, right?
...
put "SELECT * from informations WHERE infos =LIKE '%apb%' " into tSQL
...
Take some deeper looks here:
http://www.w3schools.com/sql/default.asp 
Re: Newbie on Livecode
Posted: Sun May 11, 2014 6:17 pm
by eyonmac
Thanks a lot Klaus, it works perfectly !
Would you have any idea in order to connect to database at the opening of the application without clicking on the button ?
I have found !
Well, perhaps it will be useful for other newbies as me :
- in object Menu, select "Stack script" and type the command as follow :
Code: Select all
on openStack
call "databaseConnect" of card "maquette"
end openStack
databaseConnect was the command launched by the button and "maquette" is the name of my card.
Re: Newbie on Livecode
Posted: Sun May 11, 2014 6:52 pm
by Klaus
Yep, put the handler(s) into the stackscript and execute it/them "on openstack"!
Re: Newbie on Livecode
Posted: Sun May 11, 2014 6:54 pm
by eyonmac
That's it ! Well, I love this software !