Saving as a RevLet

[cusingerBUSCw5N]

I know I am jumping the gun because I'm new - but I need to know how I can retrieve data from my website to feed LiveCode. So I went to "how to pass data from a webpage to a plugin" and was following along in the instructions when it came to:

Save your stack as a revlet and see the result

What's a revlet? How do I save a stack as a revlet?

Thanks

[mwieder]

Well, you guessed right - you're in entirely the wrong place. If you don't know what a revlet is then this is the wrong lesson to follow.

My guess is that you want your desktop application to be able to retrieve data from a website. In that case, check out the url function, as in

Code: Select all

put url("http://www.runrev.com/company/about-us") into field "xyzzy"

[cusingerBUSCw5N]

thanks for your prompt reply. Unfortunately, your solution just gives me the source code. I just want the the results of a query that I've run.

Let's start again. I have an existing database (SQL - not msSQL) and I want to get information from it to LiveCode - (the results are a loop - so I need all the results to be passed). How do I do it?

[sturgis]

The idea is the same. Set up a web script that just returns the data with no formatting other than the delimited data you need to get back.

Set up a page that accepts post (or get) then post your query to the page from your livecode stack, the script on your website does the query, makes sure the data is formatted as a flat list, and returns the results. You can then do whatever you need to with the returned data.

[cusingerBUSCw5N]

OK....maybe... do you have any examples of that being done - with a loop?

[sturgis]

Ok, just to test I set up an lc server script with a small database on the web. Its using sqlite but you should be able to use odbc to connect to your database hopefully.

(I don't remember much php which is why i'm doing it with lc server.)

Now, this doesn't loop, it just grabs all the results of a query and sends em back. You'll need to explain where your loop is and what it does if you need more help.

K here is my lc script on the server. It only has 1 table, 1 field (fname)

Code: Select all

<?lc
if $_POST["fname"] is not empty then --see if a fname lookup has been requested
put $_POST["fname"] into fname -- if so put the value into a variable
get revopendatabase("sqlite","testdb.sqlite",,,) -- open the database
if it is an integer then -- if it was an integer the db opened successfully
put it into sDb -- sDb now contains the database id for the connection. 
put "select * from myTable where fname='[[fname]]'" into tSql --set up the sql string to do the select
get revdatafromquery(,,sDb,merge(tsql)) -- merge the name in to the string and do the query
if the number of lines in it > 0 then  -- see if there were results
put it -- if so return them
else -- if not
put "no results found" --say so
end if
else -- if the database did NOt open correctly, return an error message
put "There was a problem: " & the result
end if
else  -- data for fname was not posted
put "No Post data was received, try again!"
end if
?>

Once this is set up I can do the following in the message box:

Code: Select all

post "fname=george" to url "http://guidezone.info/testing.lc"; put it

And the message box will display a list of all georges. Of course since I only have the 1 field 1 table it will just look like
george
george
george
george
george...

If you don't have lc server you can do the same with php, or asp or perl or whatever. Just somthing to handle the queries for you and return the data.

I'll leave my quick script up for a bit if you want to try it. Valid names are mike george um.. john, ringo, charles etc. No tony.

[cusingerBUSCw5N]

OK... I have the results of a query coming into LiveCode (pretty amazing...)

I am using Cold Fusion to access the database. Right now, I am looping over states, so it appears on LiveCode as AL<br>AZ<br>AR<br>
(in cold Fusion it appears properly formatted) - so LiveCode isn't processing the html formatting. If I remove the <br> it appears as AL AZ AR etc. - which is also no help.

How do I use my data in LIveCode so that each item appears as a separate line (actually a link)?

Thanks

[sturgis]

OK... I have the results of a query coming into LiveCode (pretty amazing...)

I am using Cold Fusion to access the database. Right now, I am looping over states, so it appears on LiveCode as AL<br>AZ<br>AR<br>
(in cold Fusion it appears properly formatted) - so LiveCode isn't processing the html formatting. If I remove the <br> it appears as AL AZ AR etc. - which is also no help.

How do I use my data in LIveCode so that each item appears as a separate line (actually a link)?

Thanks

Ah k. Try this
set the htmltext of field "myfield" to thequeryresults

It should recognize the <br> and such. This is a limited subset of html but from your description it will probably be fine. If there are links they'll show up as links but if you want to actually do something with them when clicked you'll need some more code in your field.

Grats on getting data back to lc!

[cusingerBUSCw5N]

I'm afraid I've flunked.

I tried using your idea - but I don't have anything labeled queryresults. It's just picking up words on bob.cfm
So, I thought I might set up a second field (sample2) and have it get the htmltext from the first one (sample)

set the htmltext of field "sample2" to field "sample"

But that didn't work - an empty field. I tried putting htmltext in the original instructions to get the information, but it wants "to field" and so it didn't work either. (the current instructions on the button are:
on mouseUp
put url("____") into field "sample"
end mouseUp

Other people must have wanted to do this... what am I missing here?

[sturgis]

You said you were successfully getting results back from cold fusion in the form of az<br>nm<br>tx<br> and that you could see these results. Are you placing them directly into a field, or using an intermediary variable? Can you post your code?

If you have a variable named myVariable with "tx<br>nm<br><a href=http://www.google.com>Google</a><br>ak<br>" in it
and then set the htmltext of field "whateverfield" to myVariable you should see.

tx
nm
google <-- would be linktext
ak

In the field.

Unfortunately you can't append htmltext directly to a field (as far as I know) so if you need to keep a continuous stream of unending data going into the field you would need to keep building up your variable and setting the field to the complete contents. (use lock screen and unlock screen to speed things up)

Another option would be to parse the incoming data and replace <br> with cr to get the line breaks, and parse for any links in the data so that you put the text after the field to append it, locate the link begin and end locations, the link itself and set the linktext at the same time as removing the actual <a href.. tags themselves.

It would also help greatly if you could post your code so that we can take a look at it and see whats up. (including sample data since we don't have access to your cold fusion setup i'd assume)

I'm afraid I've flunked.

I tried using your idea - but I don't have anything labeled queryresults. It's just picking up words on bob.cfm
So, I thought I might set up a second field (sample2) and have it get the htmltext from the first one (sample)

set the htmltext of field "sample2" to field "sample"

But that didn't work - an empty field. I tried putting htmltext in the original instructions to get the information, but it wants "to field" and so it didn't work either. (the current instructions on the button are:
on mouseUp
put url("____") into field "sample"
end mouseUp

Other people must have wanted to do this... what am I missing here?

[cusingerBUSCw5N]

thanks for the ideas. I have to take a break to pay taxes to the IRS...and stuff like that... I'll be back later to work on this

[cusingerBUSCw5N]

Hi. I'm back. The IRS has been paid. Now...back to getting data from my SQL database. I couldn't figure out how to implement your ideas - so I searched around and found the code below - that seems to get database info without needing a RevLet ---or anything special---. I get to the point where the answer is "connected" - but it doesn't give me a tDatabaseID - so the next query fails (button "connect": execution error at line n/a (External handler: exception) near "revdberr,invalid connection id"). It says I have a tDatabaseID - but when I ask it to give it to me, if fails... Here is the code

on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global tDatabaseID

-- connect to the database
put revOpenDatabase("odbc", "IP for my database", "my DSN", "login","password") into tDatabaseID

if tDatabaseID is a number then
answer "not connected"
else
answer "connected"
put "select * from mytablename" into tQuery

put revDataFromQuery(tab,cr,tDatabaseID,tQuery) into tData
put tData into field1
revCloseDatabase tDatabaseID

end if
end mouseUp

So....if it is connected, why isn't it giving me a tDatabaseID?

[mwieder]

To start with, the logic is backwards:

Code: Select all

if tDatabaseID is a number then
answer "not connected"
else
answer "connected"

should read

Code: Select all

if tDatabaseID is NOT a number then

secondly, you want to exit at that point and not continue trying to access a database you're not connected to.
UPDATE: I read your code more closely and see that you're doing this, given the constraints of the first point.

Can you access the database using the DSN via other means than LiveCode (have you verified that the DSN works)? Is this a system DSN or a file DSN? Been a while since I've had to use odbc for anything, but I seem to remember that system DSNs don't work in LiveCode.

[cusingerBUSCw5N]

I'm back to trying to connect to an external database. Let's start at the basics. I want to get data from my website database through an ODBC connection. Does LiveCode need to be installed on my server? My webhost seems to think that LiveCode has to be installed. If it does, is there a server version - or just install what's on my computer on the remote server?

If it doesn't need to be installed - Here is the code that I am using: (because this post doesn't allow ip addresses, I am giving it to you in 4 chunks - when this is resolved, I will change my database name and password)
74
112
192
54

on mouseUp

local tDatabaseID

--This will open up connection for the MS Access Database using a ODBC connection
put revOpenDatabase("ODBC", "put in IP above", "duedates_new", "admin", "" ) into tDatabaseID

if tDatabaseID is null then
answer "Not connected"
else
answer "connected" + tDatabaseID

--This query will select all the data from the table Test
put "SELECT * FROM done" into tQuery

--This will query the Database Table
put revDataFromQuery(tab, cr, tDatabaseID, tQuery) into tData

--This will display all the data from the Database
put tData into field 1

--This closes the Database connection
revCloseDatabase tDatabaseID
end if

end mouseUp

When I try it in Cold Fusion ( here is the address backwards livecodetest.cfm toolsforbusiness.info www. http://- I can get into the database - 83 records
When I do it in LiveCode, it fails - no tDatabaseID

Thanks for your patience with me.

[mwieder]

Thanks for your patience with me.

NP, but...

Does LiveCode need to be installed on my server?

No. You *can* install it there and then create a client/server connection to the server from LiveCode to LiveCode, but your DSN should work with no extra fiddling.

if tDatabaseID is null then

tDatabaseID will never be null. It will either be an integer which is the actual database ID to be used in further calls or it will contain an error string.

--This will open up connection for the MS Access Database using a ODBC connection
put revOpenDatabase("ODBC", "put in IP above", "duedates_new", "admin", "" ) into tDatabaseID

No. It won't. Check the documentation for the revOpenDatabase command at http://docs.runrev.com/Function/revOpenDatabase, especially the part that says

To use a DSN to identify an ODBC database, use the DSN as the host, and leave the databaseName parameter empty.

There's an example in the... er... Examples section:

Code: Select all

get revOpenDatabase("odbc", "BizFile", , "jenkins" ,the dbPassword of me, "emulated static")