Newbie on Livecode

[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

[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

[eyonmac]

Thanks a lot Klaus !
It works perfectly !!
Have a goo day !

[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 !

[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

[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

[Klaus]

Could you please also post the content of your variable "saisie"?

Do you speak french ?

Un petit peu

[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

[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

[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

[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

[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

[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.

[Klaus]

Yep, put the handler(s) into the stackscript and execute it/them "on openstack"!

[eyonmac]

That's it ! Well, I love this software !