Pls help with add, edit, delete using LCSvrScrips/vServer

[BarrySumpter]

I'm trying to build scripts that will allow my Android to update a vDBServer using LC Server Side Scripting.

I've successfully tested GET and POST HTTP submits from a LC Android project to my LC Scripting Server.
It's just a quick n dirty Username and Password confimation script:
http://forums.runrev.com/viewtopic.php? ... 377#p53537

And I think i've successfully tested v4Rev from LC Server Side Scripting.

Now I'd like to POST a Username, Lat, Lng to update the vDBServer.
something like:
username=Barry&Lat=1234.123&lng=789.1


Has anyone got a working script or scripts to get me started?
i.e. to Add a record, to update a record, to delete a record, and to search for and return records.

I don't know where to start on the simplest most direct way of how to structure/group the scripts.
i.e. either in a single script or multiple scripts, etc?

Or perhaps a single stack to use in the LC Server Side Scripting?
hmmmmm

tia

[bn]

Hi Barry,

(disclaimer: I don't know the slightest thing about server scripting)

I have seen Andre Garcia doing a demo on his RevSpark and it looked like it could really be usefull to what you want to do

http://www.andregarzia.com/page/revspark

unfortunately his demo scripts currently don't seem to be live

Kind regards

Bernd

[BarrySumpter]

Thanks heaps.

Happy to research anything that might get me started.

I'll have a look thru.

[BarrySumpter]

Nope but certainly appreciate the post.

Just had a flash there was a CRUD example here somewhere.

http://forums.runrev.com/viewtopic.php? ... d&start=30

[sturgis]

While its not related to the db stuff, since you're heading down the lc server road in a big way you might want to check this thread: http://forums.runrev.com/viewtopic.php? ... ion#p50034 to see how session control works. Of course if you've already got a handle on sessions feel free to ignore!

[BarrySumpter]

No $_Session in the LiceCode Dictionary.
I wonder if its a CGI property?
Or is there a different LC Dict for the Server?
Or am I out of date with 4.6.4?
Yep, I'm out of date. 5.0.2 has $_Session.



Now I have to chase down what sessions are and what they mean to my simple app.
And then cookies, and then phsycologist, ...
40625-how-do-i-use-cookies-on-livecode-server
http://lessons.runrev.com/s/lessons/m/4 ... ode-server


I don't know why but I have this overwhelming urge to put everything I want to do as server side scripting in a single LC stack.

[BarrySumpter]

If anyone is shy about posting here,
you can contact me on barrrysum@bigpond.net.au
I won't post your name or your input unless given permission.

tia

[bangkok]

I've successfully tested GET and POST HTTP submits from a LC Android project to my LC Scripting Server.
It's just a quick n dirty Username and Password confimation script:
http://forums.runrev.com/viewtopic.php? ... 377#p53537

And I think i've successfully tested v4Rev from LC Server Side Scripting.

Now I'd like to POST a Username, Lat, Lng to update the vDBServer.
something like:
username=Barry&Lat=1234.123&lng=789.1

What is your problem ? You're almost there.

Client side :

Code: Select all

put "username"&tab&"lat"&tab&"lng" into myParameters
put "myParameters="&urlencode(myParameters) into toBePosted
post toBePosted to url "http://www.myserver.com/backoffice_abcde.irev"
put it into myResult

-- can't connect to DB, code "*" sent by server
if myResult="*" then
answer "DB error"
exit to top
end if

answer myResult

On the server side :

Code: Select all

<?rev
put $_POST["myParameters"] into myParameters

### to prevent SQL injection attacks
replace quote with "" in myParameters
replace " " with "" in myParameters
replace "=" with "" in myParameters
replace "," with "" in myParameters

set itemdelimiter to tab
put item 1 of myParameters into username
put item 2 of myParameters into lat
put item 3 of myParameters into lng

put revOpenDatabase("mysql","127.0.0.1","mydatabase","mylogin","mypassword") into dbID

### can't connect to DB
if dbID is not a number then 
put "*"
quit
end if

put "SELECT * from mydatabase where username='" & username&"'" into dbsql
put revDataFromQuery(, , dbID, dbSQL) into myResult

put myResult

revclosedatabase dbID
?>

There is only one thing missing : more security.

-you could add like a key/password (a fixed or variable one) to myParameters that you are sending to the server
-or encrypt myParameters with a simple algorithm

[BarrySumpter]

What is your problem ?

Dude! How much time do you have for me to tell you?
I sure it was just a developers block.

You're almost there.

Client side :

Code: Select all

put "username"&tab&"lat"&tab&"lng" into myParameters
put "myParameters="&urlencode(myParameters) into toBePosted
post toBePosted to url "http://www.myserver.com/backoffice_abcde.irev"
put it into myResult

-- can't connect to DB, code "*" sent by server
if myResult="*" then
answer "DB error"
exit to top
end if

answer myResult

On the server side :

Code: Select all

<?rev
put $_POST["myParameters"] into myParameters

### to prevent SQL injection attacks
replace quote with "" in myParameters
replace " " with "" in myParameters
replace "=" with "" in myParameters
replace "," with "" in myParameters

set itemdelimiter to tab
put item 1 of myParameters into username
put item 2 of myParameters into lat
put item 3 of myParameters into lng

put revOpenDatabase("mysql","127.0.0.1","mydatabase","mylogin","mypassword") into dbID

### can't connect to DB
if dbID is not a number then 
put "*"
quit
end if

put "SELECT * from mydatabase where username='" & username&"'" into dbsql
put revDataFromQuery(, , dbID, dbSQL) into myResult

put myResult

revclosedatabase dbID
?>

There is only one thing missing : more security.

-you could add like a key/password (a fixed or variable one) to myParameters that you are sending to the server
-or encrypt myParameters with a simple algorithm

Thanks for that script.

Even though I'm looking for Valentina scripting,
I can see how simple I should keep it.
Get in, do your work, get out.
Open, process, close.

I was wondering if I should have a single .irev with a "type=xxx" param.
Where xxx would be add, update, delete, etc
Or have seperate add.irev, update.irev, delete.irev, etc.
Or maybe use a stack instead.

[bangkok]

Even though I'm looking for Valentina scripting,
I can see how simple I should keep it.
Get in, do your work, get out.
Open, process, close.

I was wondering if I should have a single .irev with a "type=xxx" param.
Where xxx would be add, update, delete, etc
Or have seperate add.irev, update.irev, delete.irev, etc.
Or maybe use a stack instead.

Hello Barry,

Valentina is "just", if i may say, another SQL database, right ?

Therefore, it shouldn't affect the ways you work :
-the client POST some data/and or commandes to an LiveCode Server script
-the livecode server, opens connexiont to DB, sends a SQL query, closes DB, and "displays" the result
-the client gets back the "result"

Now... a problem could arrise with the way you connect to the Valentina DB.
-first, I dont know if valentina has a "native" DB driver for LiveCode (currently, LiveCode has natives driver for mysql, postgresql, sqllite and ODBC)
If not, you could use ODBC

-but watch out, i dont know if LiveCode server can handle a ODBC DSN on the server

-that leads to another problem : where is installed the Valentina DB ?
Is it on a hosted server (on which you get the classic PHP + MySQL, and on which you can install a LiveCode Server for scripting) ? In this case, can you also install a Valentina DB Server on this server ? Not sure....
Or do you have everything "in house" ? you need a LiveCode Server solution on a local network, where you already have a Valentina DB server ? In this case, the ODBC solution should work.

Regarding the second part of your comment : the process I showed to you can work in every case.

Wheter you want to do a SELECT, or an UPDATE, or an INSERT is irrelevant.

Just add another parameter to myParameters you send to the irev script. A very simple value : 1, 2, 3

In the irev script, add IF THEN bits to analyse this value.

If typeOfMySQuery = 1 then
make an UPDATE....
end if

If typeOfMySQuery = 2 then
make an INSERT
end if

Or, what you suggest is good too : have one irev script per type of query. Although, I personnaly prefer to put everything (all my select, insert, delete, update) into one irev script. It's easier to maintain.

In any case, you should give us more details on your infrastucture, the way it is organized (where is the LiveCode Server, where is your database, same servers, which kind, different server, local, remote etc.).

Every thing is possible. Even complicated solution like this one :

-clients POST queries to remote LiveCode Server (hosted on dreamhost for instance)
-liveCode script analyses the data, creates a query
-sends this query... to a private network...
-on the private local network, you open a port, create a port forward rule... to a local web server (with Apache or the very good Abyss, both can run on Windows) where you have installed another LiveCode Server !
-this liveCode server takes the query, processes it and sends it to... the local Valentina DB server
-then "displays" the results
-therefore the results go back to the first irev script
-and then "displayed" for the clients !

Crazy ? I do it (not with a Valentina DB, but with a MS SQL server on my local network)

You could say : easier for the clients to send directly the queries to the local network, with the second web+livecode server, no need to have the first one.
True. But you gain in security : all the clients talk to only one web site... and only this web site talks to your local network (you allow only this IP address, on your firewall, to penetrate into your private local network).

[BarrySumpter]

Wow!

Thanks for the verbose post!

Much appreciated.

I've just gotten past the proof of concept stage
and got kinda burnt out with the complexity
of all the planning, researching, testing, proofing, etc.

I keep thinking my app is so simple.
And keep expecting it's going to be some huge implementation.
But it always looks that way from the beginning.

I also keep thinking that I'll only have 5 to 10 users on weekends only.
So my IIS5.1 personal web server will suffice.

I really like your security ideas.
I just have to be careful where I put what
to make sure I'm not paying $10 a month
for DB and .exe files for a dedicated server, etc.

Once I get it working on my inTRAnet I'll have a look at going public access with my IIS 5.1 web server.

Are you using a static IP?
I'll have to check my service provider
but the last time I researched static IP addresses
they wanted a rediculous $10 per month.
I know there is another solution.
Just can't remember the acronym for it.

Anyway, yes I can successfully access the valentine db server from LC Server Scripting.
Can't recall now if I documented the procedure or not.
If I did it would be on barrysumpter.com
If I didn't it was copying the v4REV dlls to the LiveCode-server.exe folder on my web server.

I won't be persuing PHP unless I'm getting paid to learn yet another programming language.
No reason to use mySql as I've got vServer with unlimited user license.

I never have liked the mess of HTML with embedded scripting, styles etc.
You should have seen the spaghetti scripting from 10-20 years ago.
Freakin nightmare.
I'm just convinced I want to use a LC stack instead of .lc text.

The LiveCode-server.exe is on my development PC under IIS 5.1 personal web server.
The database .vdb is on my development PC as well.

Another LC developer just sent me some scripts with Valentina commands.
So I've got something I can really sink my teeth into now without all the
one step forward and two steps back research eating up all my time and energy.

OK, got a decent sleep, heaps o energy, so can't wait to start again.

Please, everyone, feel free to post any further constructive suggestions and I'd be happy to research them.

[bangkok]

You're right : LiveCode is perfect to "experiment".

But eventually, you'll have to chose, regarding the infrastructure.

Do you plan.. to use your developpement PC, to host your DB ? I doubt it.

I will go even further : do you really need Valentina (sorry for Valentina's guys)

You're talking about 5 to 10 clients, week end only... That's small indeed. Do you really have such a huge DB to justify the use of Valentina, and its legendary speed ?

If not, then a regular webhosting package (like dreamhost, with web + MySQL, and LiveCode Server too) could allow you to do everything.

Regarding your question about fixed IP address, there is indeed a "turn around" :

http://www.no-ip.com

you create a redirection (free), for instance :

mysite.no-ip.info

then on your PC, with the variable IP address (because of your ISP), you install their small piece of software. It will update the IP address, every X minutes, on your mysite.no-ip.info

Therefore, your local PC is always "reachable", with a unique URL mysite.no-ip.info, even though it might change IP address.

by playing with port number, and port forward rules on your firewall, you could use this system to access remotely a local DB server, on your local network (or even your private PC).

Good luck.

[BarrySumpter]

Man there's a log of good info on this thread!

[FireWorx]

That is a great Livecode to iRev example BK. Wish there were more iRev server scripts examples out there like it.

Do you have a good sample script that uses information obtained by the user in LiveCode to do an UPDATE via iRev into the on-rev server mysql database.

In my case the user already has the unique record number in column1 but needs to update the information in say column3 with "Black" and Column 5 with "Blue". Both the column names will have to be passed to iRev as well as the data to be updated in those columns because it is situational.

The update procedure should be able to accommodate a flexible amount of columns updates up to say 32 possible.

Was thinking of placing the column names in one array and the data in another and then pass the two parameter arrays to iRev, cleanse the data and assemble an UPDATE SQL statement that had the flexibility built into it to handle as many columns as the user needed to UPDATE.

I will work on this while I wait for your reply but if you already have something similar that works please let us know.
Thanks ahead of time Bankok.
Dave

[FireWorx]

Got it. Hope it helps somebody else. If you need the SELECT rev/irev combo let me know. This is the UPDATE rev/irev combo to update a column in your rev server web hosted mySQL database. This script was thrown together from my working stack so could have some glitches.

Here is a script that based on a unique ID record number (tRecordNum) that has already populated fields from a query of your web hosted mySql database using the SELECT command can now UPDATE any one of the columns in that database table with a new value. The column name is not hard coded into the irev script so flexible for any column in the table.

In LiveCode from my IOS devise I gather the database column name (the field in livecode can be named the same as the column in the database to make it easy so the name can be returned with the script below.

global tColumn, tRecordNum
## the script of the locked text field
on mouseup ## clicking on the field to edit places the column into the global var and the text into the ios text field
put the short name of me into tColumn ## the column name in the MySql database to update
put the text of me into tText ## get the current content
mobilecontrolset tIOSTextEntryfieldIdNum, tText ## load current content into the ios text field pop up the keyboard
end mouse up

## then in the enter button. once you have edited the content to be updated

on mouseUp
mobileControlGet tIOSTextEntryfieldIdNum, "text"
put the result into tValue
if tvalue is empty or " " then
answer "No text selected. Confirm you would like to empty the field?"
with "Yes" or "Cancel"
if it is "Cancel" then exit mouseup
end if
mobileControlSet sEditField_id, "text", empty ## empty the ios text field for the next edit
 put tRecord&tab&tColumn&tab&tValue into myQuery
put "TheQuery="&urlencode(myQuery) into TheQuery ## pass the record number, the column name and the value to iRev
 post TheQuery to url "http://www.yourserver.on-rev.com/UpdateTest2.irev" ## launch the iRev on your server
end mouse up

The script of the iRev file url "http://www.yourserver.on-rev.com/UpdateTest2.irev

<?rev set the errorMode to "inline" ?>
<?rev put revOpenDatabase("mysql", "localhost", "yourDatabaseName", "yourUserName", "YourPassword") into tConId

set itemdelimiter to tab
put $_POST["TheQuery"] into TheQuery

put item 1 of TheQuery into tRecordNum
put item 2 of TheQuery into tColumn
put item 3 of TheQuery into tValue

## cleans the data as a security measure
replace quote with "" in tValue
replace "'" with "" in tValue
replace "," with "" in tValue
replace ";" with "" in tValue
replace "=" with "" in tValue


put "UPDATE `YourTableName` SET" && tColumn &"='" & tValue & "' WHERE column1 ='" & tRecordNum &"'" into tSQL
put tSQL
if tConId is a number then
revExecuteSQL tConID, tSQL
end if

?>

Hope this Helps,
Dave