Retrieve one record, contents into text fields

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

neatgadgets
Posts: 17
Joined: Sat Dec 21, 2013 12:14 pm

Retrieve one record, contents into text fields

Post by neatgadgets » Wed Oct 08, 2014 1:06 am

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?

Saman Sjr.
Posts: 49
Joined: Sat Nov 30, 2013 6:40 am

Re: Retrieve one record, contents into text fields

Post by Saman Sjr. » Wed Oct 08, 2014 12:49 pm

Bangkok and Klaus have the answer for you in here http://forums.livecode.com/viewtopic.php?f=12&t=21609

neatgadgets
Posts: 17
Joined: Sat Dec 21, 2013 12:14 pm

Re: Retrieve one record, contents into text fields

Post by neatgadgets » Thu Oct 09, 2014 9:52 am

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?

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Retrieve one record, contents into text fields

Post by Klaus » Thu Oct 09, 2014 12:06 pm

Hi neatgadgets,

guessing is ineffective 8)
So could you please post your script(s) and maybe some example data?


Best

Klaus

neatgadgets
Posts: 17
Joined: Sat Dec 21, 2013 12:14 pm

Re: Retrieve one record, contents into text fields

Post by neatgadgets » Thu Oct 09, 2014 12:39 pm

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"

Saman Sjr.
Posts: 49
Joined: Sat Nov 30, 2013 6:40 am

Re: Retrieve one record, contents into text fields

Post by Saman Sjr. » Thu Oct 09, 2014 1:10 pm

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.

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Retrieve one record, contents into text fields

Post by Klaus » Thu Oct 09, 2014 1:16 pm

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! 8)
...
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 :D

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

neatgadgets
Posts: 17
Joined: Sat Dec 21, 2013 12:14 pm

Re: Retrieve one record, contents into text fields

Post by neatgadgets » Thu Oct 09, 2014 1:17 pm

That worked for the streetname field but I still got two bits of data in the first field for streetnumber.

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Retrieve one record, contents into text fields

Post by Klaus » Thu Oct 09, 2014 1:31 pm

Please post the content of tData (the data you retrieve from the DB)!

neatgadgets
Posts: 17
Joined: Sat Dec 21, 2013 12:14 pm

Re: Retrieve one record, contents into text fields

Post by neatgadgets » Thu Oct 09, 2014 10:20 pm

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?

sturgis
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 1685
Joined: Sat Feb 28, 2009 11:49 pm

Re: Retrieve one record, contents into text fields

Post by sturgis » Fri Oct 10, 2014 1:51 am

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/

neatgadgets
Posts: 17
Joined: Sat Dec 21, 2013 12:14 pm

Re: Retrieve one record, contents into text fields

Post by neatgadgets » Fri Oct 10, 2014 2:10 am

Code: Select all

1	11	White	Road	Jensen	4818	QLD	
This is what goes into tData

sturgis
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 1685
Joined: Sat Feb 28, 2009 11:49 pm

Re: Retrieve one record, contents into text fields

Post by sturgis » Fri Oct 10, 2014 3:32 am

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

neatgadgets
Posts: 17
Joined: Sat Dec 21, 2013 12:14 pm

Re: Retrieve one record, contents into text fields

Post by neatgadgets » Fri Oct 10, 2014 8:14 am

Only difference is that you had the following and used sqlite vs mysql

Code: Select all

 put  revdatafromquery(,,sDbId,tSql) into tData
I checked the first field I have, and it actually has the entire row of data, not just the first two fields of data.

sturgis
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 1685
Joined: Sat Feb 28, 2009 11:49 pm

Re: Retrieve one record, contents into text fields

Post by sturgis » Fri Oct 10, 2014 2:29 pm

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

Post Reply

Return to “Databases”