I am newly returning after 5 years to NSB. I would like to post about a dozen variables to an external MySQL Database managed by AWS. I can easily do this in Visual Studio, but I do not know how to do it in NSB.
Does anyone have an example I can use,mor would someone be willing to accept a contract to do this for me?
Bob Briscoe
617 851 9104
Does AWS provide any JavaScript sample code?
If your server has PHP you can use Ajax to send these data to a PHP script you would need to write. This script would then populate the SQL data base accordingly. If your server is running Microsoft ASP.NET you can use that instead of PHP.
John
There are lots of examples in several languages. Which exact aws sql service are you using? There are several.
Hi Gary,
I have hosted dozens of MySQL databases on various servers, so I don’t see how using AWS as a server makes any difference. FYI , the database is a plain vanilla MySQL database. The application is NSB. If you have an example of using the AJAX/php scenario in NSB to post to an external MyS@QL database I would very much like to see it.
Thanks for responding.
Bob Briscoe
Hi John,
I don’t understand about the role of the server. I have a Linux server hosting the NSB application. The server offers php. What I need is the code for NSB to post about a dozen variables to a MySQL database. Do you have an example of this written in NSB?
Thanks,
Bob Briscoe
There are two MySQL server services. One is RDS with MySQL and the other is Aurora with MySQL. The Aurora can be spun up and down. Note, it takes up to 90 seconds to spin up. Aurora is expensive. RDS is not much cheaper, depending on your needs. But for always up, you can not spin down the server and save money.
Aurora can not be exposed as a SQL port on an IP. You have to use RDS. AWS will tell you that’s a real bad idea. You’ll at least need to use their WAF service to protect the DB from attacks.
NOTE: AWS does have per country the ability to exclude access from them, like China and Russia in CloudFront.
My suggestion is to not use the MySQL at AWS for the front end database. I started using Aurora 2 years ago and estimated my costs per user to be around $20.00 per month and higher for super users. I now use S3 storage for the front end data base and it costs less than 1 cent per month per user. I do spin up an Aurora MySQL database when I want to run analytics and reports and update the db from the S3 Storage. This is very cost effective.
When I was looking at port access to RDS from AppStudio as a webapp, I also found that there is no standard library to do such, as it’s considered such a security risk. You’d need to do port access, and that would mean a plug in. I don’t see how you could use the AJAX for it, as AJAX is assuming the server is HTTP/HTTPS.
Now, how do I do it. Again, there is the domain name at Route 53 that points to CloudFront that points to API Gateway that invokes a Lambda Node.JS function that calls the S3 API to read and write data. Yes, a lot of setup. But it works really nicely. Very fast server responses.
Hi Gary,
Why are you explaining this to me? BTW I am using RDS, for some reasons you did not mention. I am only interested in getting some code for my NSB application that would allow me to insert about a dozen variables in a MySQL database. I am hosting the NSB code on a cheap Linux server and am hosting MySQL on an AWS server. In the past I typically hosted MySQL on the customer’s server and hosted the NSB on my server. So, please take whatever server I am using out of the equation as it is irrelevant.
If you can help me with the code to put into the NSB application I would be very grateful.
Thanks,
Bob Briscoe
Because what you want to do won’t work. You’ll need a plug in to do port access to RDS. That’s not available in a webapp,. Electron or Cordova apps can use plugins…
If your server is Unix/Linux and you have php and myPhpAdmin on your server and you want to send some data to your server that has mySQL (or similar) installed then in NSB:
Create a string something like this: postData = “data1=” & data1 & “&data2=” & data2 & “&data3=” & etc.
Each data string will be returned in your php script and can then be inserted into SQL where desired.
Function PostToServer()
AjaxWaitTime = Now
TS = SetInterval(TestAjaxDone,1000)
req=Ajax("https://yourserverurl/yourphpscript.php","POST",postData,AjaxDone)
End Function
Function TestAjaxDone()
If DateDiff("s", AjaxWaitTime, Now) > 4 Then
ClearTimeout(TS)
req.abort()
M = "Unable to connect to the server at this time, please try again later."
Call ShowMsg(M,0, "No Connect")
End If
End Function
Function AjaxDone()
If req.readyState<> 4 Then
If DateDiff("s", AjaxWaitTime, Now) > 10 Then
req.abort()
M = "Unable to connect to the recipeRasa server at this time, please try again later."
Call ShowMsg(M,0, MsgTitle)
ClearInterval(TS)
End If
Exit Function
End If
ClearTimeout(TS)
If req.status=200 Then 'success
s = req.responseText
Else 'failure
M = "Problem (error " & req.err & "). Verify that you have an Internet connection and try again."
Call ShowMsg(M,0, "Error Sending Data")
End If
There are 2 tests following the POSTing of the data. The Function AjaxDone is what you want to happen. But it doesn’t always work that way. So the first Function TestAjaxdone gives the app 4 seconds to get a response from the server. If AjaxDone is called before the 4 seconds then all is good and the TS interval is ended. Not sure if the 10 second wait is really required in AjaxDone but it seems to work for me so I have left it in.
Note: ShowMsg is one of my functions, substitute the NSB Message box. The code for the php script is another matter. If you want an example I can post that here.
It should be noted that sending data to your server and using standard SQL commands can leave you open to being hacked. See this page for preventing sql injections: PHP MySQLi Prepared Statements Tutorial to Prevent SQL Injection
John
Hi Gary,
All you do in non-NSB applications is create a connection string and post your data. You do not need anything special. I do it everyday with web apps and with desktop apps in every language you can imagine.
All I wanted to know was how to do this simple task with NSB.
I have done it in the past using AJAX and php so I guess I just have to dust off the archives and take a look at how I did it before.
Thank you very much for your suggestions on this project. You didn’t have to do it, and I am very grateful for your comments.
Thanks again,
Bob Briscoe
The AWS WAF service provides sql injection protection and many other hacks, but as John and AWS note, it’s dangerous.
Also Have you considered how the app will get the db password, as you won’t want to embed it in the app.
Also, look at the lambda service, it can provide a php environment as well. And the other services I listed.
Hi John,
My Server is a cheap plain vanilla Linux server capable of running php. Is there a simple example of what control(s) I need to add to my NSB app and maybe a simple example of how to post a MySQL Database?
Thanks,
Bob Briscoe
Bob:
You can use any button control to call the Function PostToServer in the sample code provided earlier.
The Ajax command in this function calls a PHP script, which you have to write, that will update the MySql database.
For info on writing PHP scripts see W3Schools: PHP Tutorial
John
Hi John,
That is EXACTLY what I want. I apologize but I lost the code “PostToServer” which you mention. Could you be so kind as to send this code sample again?
Thank you very much.
Bob Briscoe
Bob:
Its on this thread but here it is again:
Function PostToServer()
AjaxWaitTime = Now
TS = SetInterval(TestAjaxDone,1000)
req=Ajax("https://yourserverurl/yourphpscript.php","POST",postData,AjaxDone)
End Function
Function TestAjaxDone()
If DateDiff("s", AjaxWaitTime, Now) > 4 Then
ClearTimeout(TS)
req.abort()
M = "Unable to connect to the server at this time, please try again later."
Call ShowMsg(M,0, "No Connect")
End If
End Function
Function AjaxDone()
If req.readyState<> 4 Then
If DateDiff("s", AjaxWaitTime, Now) > 10 Then
req.abort()
M = "Unable to connect to the server at this time, please try again later."
Call ShowMsg(M,0, MsgTitle)
ClearInterval(TS)
End If
Exit Function
End If
ClearTimeout(TS)
If req.status=200 Then 'success
s = req.responseText
Else 'failure
M = "Problem (error " & req.err & "). Verify that you have an Internet connection and try again."
Call ShowMsg(M,0, "Error Sending Data")
End If
John
Bob, you asked why I was telling you all of that information. That’s a good question which I should have addressed.
AWS provides services that are virtualized. Relational Database Service, RDS, is a virtualized service that provides an interface to their backend data storage that looks like MySQL and other standard dbs systems. It is not virtualized on Linux or Windows servers, it’s on whatever proprietary thing AWS is using internally. That virtualization is not accessible to the developer. AWS provides interconnects between their services, again all virtualized. Lambda is a service that can execute PHP code, but again, it is virtualized.
John, and I believe Bob, are referring to renting a server. AWS does rent them, but that server would not have a native connection to RDS. And AWS is not the cheapest for rack space rental (check Hostgator).
Using AWS has advantages, but one is NOT moving to another service. Nothing in AWS services is fully standard. Moving to a Linux box from AWS is not just a copy and paste.
Maybe this will help you avoid some rat hoes before you commit to a design and hosting system.
Gary:
Thanks for the info on AWS, quite informative. Some of my users have AWS but I’ve never looked into it because of cost and I haven’t needed the added power, and I would assume security, they provide. A low cost server is all I need for development with PHP as a requirement for my testing. When it’s time to hand over an app to a user I send them the fully deployed folder and they take it from there.
Voltbuilder might be an alternative for some users. I am not familiar enough with it to comment. Maybe others can lend a hand here.
Thanks, John