Get data from a field in SQlite
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Get data from a field in SQlite
Hello:
I have created an SQLite dabatase an I have three fields on it, named:
ID , name and telephone
I want to get the data from a field of one record.
When I write:
put fld "telephone"
I get all records and not the phone number
Can you help me please?
I have created an SQLite dabatase an I have three fields on it, named:
ID , name and telephone
I want to get the data from a field of one record.
When I write:
put fld "telephone"
I get all records and not the phone number
Can you help me please?
Re: Get data from a field in SQlite
Hi anjemalo,
welcome to the forum!
...
put fld "telephone"
...
This is a Livecode command and will put the content of a Livecode field named "telephone"
into the message box and has nothing to do with your database nor does this command access
the database at all!
You need to talk "SQL" with the database and since a database can have more than one record
you need to define which telephone number you want to fetch.
I can recommend some learning resources:
1. These are great stacks to learn more about the basics of Livecode:
http://www.hyperactivesw.com/revscriptc ... ences.html
2. This page got me started with SQL:
http://www.w3schools.com/sql/default.asp
Best
Klaus
welcome to the forum!

Sorry, don't get it?anjemalo wrote:...
I have created an SQLite dabatase an I have three fields on it, named:
ID, name and telephone
I want to get the data from a field of one record.
When I write:
put fld "telephone"
I get all records and not the phone number
...
put fld "telephone"
...
This is a Livecode command and will put the content of a Livecode field named "telephone"
into the message box and has nothing to do with your database nor does this command access
the database at all!
You need to talk "SQL" with the database and since a database can have more than one record
you need to define which telephone number you want to fetch.
I can recommend some learning resources:
1. These are great stacks to learn more about the basics of Livecode:
http://www.hyperactivesw.com/revscriptc ... ences.html
2. This page got me started with SQL:
http://www.w3schools.com/sql/default.asp
Best
Klaus
Re: Get data from a field in SQlite
Hi.
What Klaus said.
Also, and very important, NEVER use reserved words as names of controls. So "ID" and "name" are right out. You will never regret that decision. If you like descriptive references, use something like "name1" or "fName", anything but "name".
That you used those sorts of things tells me that you must, absolutely must, read the material Klaus recommended. Write back anytime and often, but do practice the very basics. Make an address book. It will take longer than you think, and even if ultimately useless as an app, will really help you get started.
Craig Newman
What Klaus said.
Also, and very important, NEVER use reserved words as names of controls. So "ID" and "name" are right out. You will never regret that decision. If you like descriptive references, use something like "name1" or "fName", anything but "name".
That you used those sorts of things tells me that you must, absolutely must, read the material Klaus recommended. Write back anytime and often, but do practice the very basics. Make an address book. It will take longer than you think, and even if ultimately useless as an app, will really help you get started.
Craig Newman
Re: Get data from a field in SQlite
thank you for your response.
I worked with SQL in other programming languages. I am new with Livecode. I need to get the data from a field name for example "telephone"
This is my code:
on mouseUp
-- check the global connection ID to make sure we have a database connection
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if
-- construct the SQL (this selects all the data from the specified table)
put "tpersons" into tTableName -- set this to the name of a table in your database
put "SELECT * FROM " & tTableName into tSQL
-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
-- check the result and display the data or an error message
if item 1 of tData = "revdberr" then
answer error "There was a problem querying the database:" & cr & tData
else
put tData into field "Data"
--put the number of lines of tData
put item 1 of line 1 of tData --give me the first record
--
end if
end mouseUp
I worked with SQL in other programming languages. I am new with Livecode. I need to get the data from a field name for example "telephone"
This is my code:
on mouseUp
-- check the global connection ID to make sure we have a database connection
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if
-- construct the SQL (this selects all the data from the specified table)
put "tpersons" into tTableName -- set this to the name of a table in your database
put "SELECT * FROM " & tTableName into tSQL
-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
-- check the result and display the data or an error message
if item 1 of tData = "revdberr" then
answer error "There was a problem querying the database:" & cr & tData
else
put tData into field "Data"
--put the number of lines of tData
put item 1 of line 1 of tData --give me the first record
--
end if
end mouseUp
Re: Get data from a field in SQlite
Hi anjemalo,
## Looks like here you are missing one step!
## Since you did not set the itemdelimiter, which defaults to COMMA, you are getting the complete record
## Add this line:
set itemdelimiter to TAB
That should do the trick!
Best
Klaus
ah, OK, that was not clear in your posting.anjemalo wrote:I worked with SQL in other programming languages.
Code: Select all
...
put "SELECT * FROM " & tTableName into tSQL
-- query the database
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
-- check the result and display the data or an error message
if item 1 of tData = "revdberr" then
answer error "There was a problem querying the database:" & cr & tData
else
put tData into field "Data"
--put the number of lines of tData
## Since you did not set the itemdelimiter, which defaults to COMMA, you are getting the complete record
## Add this line:
set itemdelimiter to TAB
Code: Select all
put item 1 of line 1 of tData --give me the first record
end if
end mouseUp
Best
Klaus
Re: Get data from a field in SQlite
That't working. thank you.
How then, I can disable itemdelimiter after get the data fields?
I need to get data from fields of individual records. For example record number four.
How then, I can disable itemdelimiter after get the data fields?
I need to get data from fields of individual records. For example record number four.
Re: Get data from a field in SQlite
Is there a way to get only the data from a field named "telephone" of a specific record?
Using the above code I get data not desirable. for example:
958123456
2
these are two records, because the first does not have TAB.
Using the above code I get data not desirable. for example:
958123456
2
these are two records, because the first does not have TAB.
Re: Get data from a field in SQlite
Hi.
Remember that learning the basics thing? Make a button. Put this into its script:
Step through this short handler. Watch the delimiters. Watch the output. The record delimiter, "return", does not change.
Craig
Remember that learning the basics thing? Make a button. Put this into its script:
Code: Select all
on mouseUp
put "field1/record1" & "," & "field2/record1" & "," & "field3/record1" & return & "field1/record2" & "," & "field2/record2" & "," & "field3/record2" into temp
answer item 2 of line 1 of temp
answer item 2 of line 2 of temp
replace comma with tab in temp
set the itemDel to tab
answer item 3 of line 1 of temp
end mouseUp
Craig
Last edited by dunbarx on Thu Oct 13, 2016 10:03 pm, edited 1 time in total.
Re: Get data from a field in SQlite
Hi anjemalo,
If the ID is also the number of record you could:
Best
Klaus
the itemdelimiter is reset automatically at the end of a handler.anjemalo wrote:How then, I can disable itemdelimiter after get the data fields?
Use a "where" clause in your SQL, see my link to the SQL turotial!anjemalo wrote:I need to get data from fields of individual records. For example record number four.
If the ID is also the number of record you could:
Code: Select all
...
put "SELECT * FROM " & tTableName && "where ID =4" into tSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
...
Klaus
Re: Get data from a field in SQlite
Hi anjemalo,
You define this delimiter in your revdatafromquery() handler:
...
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
...
So LC uses a CR to delimit records and TAB to delimit the single fields of one record.
Best
Klaus
it is Livecode that uses a TAB to differ between the fields of a database record!anjemalo wrote:Is there a way to get only the data from a field named "telephone" of a specific record?
Using the above code I get data not desirable. for example:
958123456
2
these are two records, because the first does not have TAB.
You define this delimiter in your revdatafromquery() handler:
...
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
...
So LC uses a CR to delimit records and TAB to delimit the single fields of one record.
Best
Klaus
Re: Get data from a field in SQlite
What Klaus said again.
The itemDelimiter can be any character, and as of v7 can be any string. You can set and reset it as many times as you need to, anywhere in the flow of a handler. The default delimiter is comma.
Excel and most databases use tabs as field delimiters, and return as record delimiters by default. But in LC this is entirely within your control. But as Klaus mentioned, it is up to you to manage your data.
Again, all this points to the fact that you are moving just a little too fast. LC allows this, by presenting such a convenient and seemingly simple programming environment. This is a good thing, but also is a bit misleading; LC is large and rich in functionality. You must put in effort to be able to drive with comfort.
Craig
The itemDelimiter can be any character, and as of v7 can be any string. You can set and reset it as many times as you need to, anywhere in the flow of a handler. The default delimiter is comma.
Excel and most databases use tabs as field delimiters, and return as record delimiters by default. But in LC this is entirely within your control. But as Klaus mentioned, it is up to you to manage your data.
Again, all this points to the fact that you are moving just a little too fast. LC allows this, by presenting such a convenient and seemingly simple programming environment. This is a good thing, but also is a bit misleading; LC is large and rich in functionality. You must put in effort to be able to drive with comfort.
Craig
Last edited by dunbarx on Fri Oct 14, 2016 2:44 pm, edited 1 time in total.
Re: Get data from a field in SQlite
Many thanks to all of you.
Best regards
Best regards