Retrieve one record, contents into text fields
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 17
- Joined: Sat Dec 21, 2013 12:14 pm
Retrieve one record, contents into text fields
I'm trying to find an example of running a database query (which I can do) and putting the data from the single record retrieved into text fields. Can someone point me to one?
-
- Posts: 49
- Joined: Sat Nov 30, 2013 6:40 am
Re: Retrieve one record, contents into text fields
Bangkok and Klaus have the answer for you in here http://forums.livecode.com/viewtopic.php?f=12&t=21609
-
- Posts: 17
- Joined: Sat Dec 21, 2013 12:14 pm
Re: Retrieve one record, contents into text fields
Hi thanks for that.
The lines i got from that were
put item 2 of tData into fld "fldStreetNumber"
put item 3 of tData into fld "fldStreetName"
Problem is that for fldStreetNumber, it had the values for the first two items, and nothing appeared in the fldStreetName
Is there something I am missing?
The lines i got from that were
put item 2 of tData into fld "fldStreetNumber"
put item 3 of tData into fld "fldStreetName"
Problem is that for fldStreetNumber, it had the values for the first two items, and nothing appeared in the fldStreetName
Is there something I am missing?
Re: Retrieve one record, contents into text fields
Hi neatgadgets,
guessing is ineffective
So could you please post your script(s) and maybe some example data?
Best
Klaus
guessing is ineffective
So could you please post your script(s) and maybe some example data?
Best
Klaus
-
- Posts: 17
- Joined: Sat Dec 21, 2013 12:14 pm
Re: Retrieve one record, contents into text fields
Code: Select all
-- construct the SQL (this selects all the data from the specified table)
put "properties" 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
put item 2 of tData into fld "fldStreetNumber"
put item 3 of tData into fld "fldStreetName"
-
- Posts: 49
- Joined: Sat Nov 30, 2013 6:40 am
Re: Retrieve one record, contents into text fields
make sure the item delimiter is tab as you query with tab option in revDataFromQuery.
use this code to set the item delimiter before you access items in tData :
set itemdelimiter to tab
regard's
Saman Sjr.
use this code to set the item delimiter before you access items in tData :
set itemdelimiter to tab
regard's
Saman Sjr.
Re: Retrieve one record, contents into text fields
Hi neatgadgets,
AHA!
Pblem is that you told "revquery..." to delimit your data with a TAB,
but you do not set the ITEMDELIMITER to tab when neccessary!
...
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
## Here you need to set the itemdelimiter:
set itemdel to TAB
put item 2 of tData into fld "fldStreetNumber"
...
Please check this link: http://www.w3schools.com/sql/default.asp
That's what got me started with SQL
Currently you are retrieving ALL database fields of ALL records but I am sure you only need a specific street and number, right
Check the tutorials above to be able to only select street and number for a specific customer (or whatever you have else in your db).
Best
Klaus
AHA!
Pblem is that you told "revquery..." to delimit your data with a TAB,
but you do not set the ITEMDELIMITER to tab when neccessary!
...
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
## Here you need to set the itemdelimiter:
set itemdel to TAB
put item 2 of tData into fld "fldStreetNumber"
...
Please check this link: http://www.w3schools.com/sql/default.asp
That's what got me started with SQL
Currently you are retrieving ALL database fields of ALL records but I am sure you only need a specific street and number, right
Check the tutorials above to be able to only select street and number for a specific customer (or whatever you have else in your db).
Best
Klaus
-
- Posts: 17
- Joined: Sat Dec 21, 2013 12:14 pm
Re: Retrieve one record, contents into text fields
That worked for the streetname field but I still got two bits of data in the first field for streetnumber.
Re: Retrieve one record, contents into text fields
Please post the content of tData (the data you retrieve from the DB)!
-
- Posts: 17
- Joined: Sat Dec 21, 2013 12:14 pm
Re: Retrieve one record, contents into text fields
It is being retrieved from a standard MySQL database table.
Property ID and Street Number are being put in the first field.
Property ID = 1
Street Number = 1
Street Name = White
Not sure exactly what format you want to see the data. If it comes from a MySQL database, should it not be pulled out the same all the time?
Property ID and Street Number are being put in the first field.
Property ID = 1
Street Number = 1
Street Name = White
Not sure exactly what format you want to see the data. If it comes from a MySQL database, should it not be pulled out the same all the time?
Re: Retrieve one record, contents into text fields
Need to see the content of tData as it appears in the variable. Since it seems you're pulling multiple lines, can you post 1 line of data? revdatafromquery, as you've set it up, should come back as
column 1 [tab] column 2 [tab] column 3 return
column 1 [tab] column 2 [tab] column 3 return.. etc (assuming you only have 3 fields in your table.
Of course, if you only have 1 entry it'll be a single line of data but, posting an example of the data as it comes back from revdatafromquery is helpful for locating issues. In fact, it would be helpful to you if you added a simple line of code before you try to put the data into your fields. SOmething like: answer information (line 1 of tData)
This should pop up a box showing you exactly what is in the first line of tData.
In fact, while thinking about this, I have a theory. Is it possible there is a return embedded in the data of one of your fields? Or an extra tab, or something of that nature?
As others have mentioned, its hard to debug such a thing without the data that goes along with the scripting. (sometimes it even helps to pull out a single field at a time and see what you're getting back. If the data in the database has something strange going on, this can be a great way to find it)
Also, there is this nifty little library called dblib by andre garzia (which is dual license) that does interesting things for you, and can simplify your life greatly. Some things still must be done the old fashioned way, but boy it can be a great tool.
EDITED: to add link to dblib
http://andregarzia.com/pages/en/dblib/
column 1 [tab] column 2 [tab] column 3 return
column 1 [tab] column 2 [tab] column 3 return.. etc (assuming you only have 3 fields in your table.
Of course, if you only have 1 entry it'll be a single line of data but, posting an example of the data as it comes back from revdatafromquery is helpful for locating issues. In fact, it would be helpful to you if you added a simple line of code before you try to put the data into your fields. SOmething like: answer information (line 1 of tData)
This should pop up a box showing you exactly what is in the first line of tData.
In fact, while thinking about this, I have a theory. Is it possible there is a return embedded in the data of one of your fields? Or an extra tab, or something of that nature?
As others have mentioned, its hard to debug such a thing without the data that goes along with the scripting. (sometimes it even helps to pull out a single field at a time and see what you're getting back. If the data in the database has something strange going on, this can be a great way to find it)
Also, there is this nifty little library called dblib by andre garzia (which is dual license) that does interesting things for you, and can simplify your life greatly. Some things still must be done the old fashioned way, but boy it can be a great tool.
EDITED: to add link to dblib
http://andregarzia.com/pages/en/dblib/
-
- Posts: 17
- Joined: Sat Dec 21, 2013 12:14 pm
Re: Retrieve one record, contents into text fields
Code: Select all
1 11 White Road Jensen 4818 QLD
Re: Retrieve one record, contents into text fields
Here is a link to a sample stack that fills fields from a database. hopefully something in there will help. There is a short description in a text field in the stack that explains whats going on. (kinda) Code isn't really commented, I'm lacking time, but hopefully this will help. (and as i was putting it together, I did silly things like not putting the data into the right fields etc.. Amazing how small things can slip in when you're in a hurry)
Heres the link. https://dl.dropboxusercontent.com/u/119 ... t.livecode
Heres the link. https://dl.dropboxusercontent.com/u/119 ... t.livecode
-
- Posts: 17
- Joined: Sat Dec 21, 2013 12:14 pm
Re: Retrieve one record, contents into text fields
Only difference is that you had the following and used sqlite vs mysql
I checked the first field I have, and it actually has the entire row of data, not just the first two fields of data.
Code: Select all
put revdatafromquery(,,sDbId,tSql) into tData
Re: Retrieve one record, contents into text fields
As mentioned above, did you set the itemdelimiter to tab before trying to split up the row? Also, the default for revdatafromquery is to have tab as column delimiter and return as row delimiter, so I didn't bother to set them explicitly.
Since the data is being returned with the items in the row being tab delimited, the only thing I can think of is that the itemdelimiter isn't set correctly.
set the itemdelimiter to tab. If you prefer, and if you're positive there are no commas in your data, you can use comma instead of tab and then not have to worry about setting the delimiter. I did notice the sample data you posted used spaces rather than tab, but my assumption is that either the forum did it, or you had to do it that way to overcome forum/form limitations, but going to ask the question just to confirm.. Its not possible that your entire row of data is in a single field of the database right? Other than the possible itemdelimiter issue, this is the only other thing I can think of that would put the whole row into the first field. (with the itemdelimiter NOT set to tab, the same thing would result. The whole row would be seen as 1 item because there are no commas (commas being the default itemdelimiter))
Since the data is being returned with the items in the row being tab delimited, the only thing I can think of is that the itemdelimiter isn't set correctly.
set the itemdelimiter to tab. If you prefer, and if you're positive there are no commas in your data, you can use comma instead of tab and then not have to worry about setting the delimiter. I did notice the sample data you posted used spaces rather than tab, but my assumption is that either the forum did it, or you had to do it that way to overcome forum/form limitations, but going to ask the question just to confirm.. Its not possible that your entire row of data is in a single field of the database right? Other than the possible itemdelimiter issue, this is the only other thing I can think of that would put the whole row into the first field. (with the itemdelimiter NOT set to tab, the same thing would result. The whole row would be seen as 1 item because there are no commas (commas being the default itemdelimiter))