Get data from a field in SQlite

[anjemalo]

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?

[Klaus]

Hi anjemalo,

welcome to the forum!

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

Sorry, don't get it?
...
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

[dunbarx]

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

[anjemalo]

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

[Klaus]

Hi anjemalo,

I worked with SQL in other programming languages.

ah, OK, that was not clear in your posting.

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

## 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

Code: Select all

    put item 1 of line 1 of tData  --give me the first record
  end if
end mouseUp

That should do the trick!


Best

Klaus

[anjemalo]

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.

[anjemalo]

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.

[dunbarx]

Hi.

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

Step through this short handler. Watch the delimiters. Watch the output. The record delimiter, "return", does not change.

Craig

[Klaus]

Hi anjemalo,

How then, I can disable itemdelimiter after get the data fields?

the itemdelimiter is reset automatically at the end of a handler.

I need to get data from fields of individual records. For example record number four.

Use a "where" clause in your SQL, see my link to the SQL turotial!
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
...

Best

Klaus

[Klaus]

Hi anjemalo,

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.

it is Livecode that uses a TAB to differ between the fields of a database record!
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

[dunbarx]

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

[anjemalo]

Many thanks to all of you.
Best regards