I want to write an application in PureBasic that will connect remotely to MySQL (download data from MySQL and send data to MySQL)
What's the best way to do this?
Will an encrypted SSL connection to Mysql servers ensure security?
Maybe it's better to use PostgreSQL?
Remote to MySQL
Re: Remote to MySQL
You don't know if a secured connection ensure security?
It ensure it more than a no secured connection
But I think you will soon notice, that it is not possible with native PB database access to secure a connection.
(at least for postgresql it is not implemented, I don't work with mysql for customer applications)
For postgres I had to write a proxy to use a secured connection.
It ensure it more than a no secured connection
But I think you will soon notice, that it is not possible with native PB database access to secure a connection.
(at least for postgresql it is not implemented, I don't work with mysql for customer applications)
For postgres I had to write a proxy to use a secured connection.
-
- User
- Posts: 20
- Joined: Sun Feb 14, 2021 2:55 am
- Location: Max Meadows, Virginia, USA
- Contact:
Re: Remote to MySQL
I am brand new to PureBasic. But, connecting to a remote MySQL should be no problem. MySQL uses TLS protocol. Assuming you are hosting a MySQL database somewhere, they have to inherently implement some security since all connections are remote (I use Hostgator for website and databases). You can ask your provider or simply execute a SQL statement - I cannot remember the exact command but it something like SHOW SESSION STATUS. You also have to usually white list your IP address from which you are accessing the data on your remote server. Please note that this does not secure your data, just the communication. If you need to secure the data you want to encrypt the data when you write it and this will require a decryption key every time you access it.
Re: Remote to MySQL
MySQL has been natively supported by PureBasic since version 5.70 LTS. It interfaces with remote databases in pretty much the same manner as it would a local database. This quick tutorial illustrates PureBasic's fundamental database CRUD operations:Johanson wrote:...an application in PureBasic that will connect remotely to MySQL (download data from MySQL and send data to MySQL)...
> PureBasic SQLite Database: A Quick Tutorial
And here's a simple example to demonstrate connecting to and querying a remote MySQL database server using PureBasic's built-in commands:
Code: Select all
UseMySQLDatabase()
Enumeration
#window
#list
#mySql
EndEnumeration
#appName = "Remote MySQL Example"
MessageRequester(#appName, "The connection used in this example is " +
"to a public read-only MySQL database from " +
"Rfam, which is a scientific organisation " +
"providing the latest data on RNA families. " +
"(https://docs.rfam.org/en/latest/database.html)")
wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
OpenWindow(#window, 0, 0, 800, 400, #appName, wFlags)
ListViewGadget(#list, 10, 10, 780, 380)
; connecting to the remote MySQL database using given parameters & credentials
If OpenDatabase(#mySql, "host=mysql-rfam-public.ebi.ac.uk port=4497 dbname=Rfam", "rfamro", "")
; querying the first ten records of table > family
If DatabaseQuery(#mySql, "SELECT * from family LIMIT 10")
; displaying selected columns from the query results
While NextDatabaseRow(#mySql)
AddGadgetItem(#list, -1, "A/c No: " + GetDatabaseString(#mySql, 0))
AddGadgetItem(#list, -1, "Description: " + GetDatabaseString(#mySql, 3))
AddGadgetItem(#list, -1, "Source: " + GetDatabaseString(#mySql, 5))
AddGadgetItem(#list, -1, "Updated: " + GetDatabaseString(#mySql, 33))
AddGadgetItem(#list, -1, "")
Wend
FinishDatabaseQuery(#mySql)
success = #True
EndIf
CloseDatabase(#mySql)
EndIf
If Not success
AddGadgetItem(#list, -1, "MySQL Error: " + DatabaseError())
EndIf
While WaitWindowEvent() ! #PB_Event_CloseWindow : Wend
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel
-
- User
- Posts: 20
- Joined: Sun Feb 14, 2021 2:55 am
- Location: Max Meadows, Virginia, USA
- Contact:
Re: Remote to MySQL
One thing I forgot to mention in my earlier message is that if you are connecting to MySQL on a remote (such as a webserver) is that often you must white list the connecting IP to access the data. If you are using a standard IP, it is dynamic and changes every once in awhile and will need to be updated in the white list.
Re: Remote to MySQL
To avoid these hassles, it would be better to simply use the HTTPRequest() function with GET/POST queries, and just read the returned responses. No need to fiddle with remote connections, security settings, or tiresome web scripts. The server relays all the necessary queries locally, and returns the requested data.JaxMusic wrote:...connecting to MySQL on a remote (such as a webserver) is that often you must white list the connecting IP to access the data. If you are using a standard IP, it is dynamic and changes every once in awhile and will need to be updated in the white list.
Here's an example which polls weather data from the U.S. National Weather Service, by providing latitude and longitude values as GET queries:
Code: Select all
InitNetwork()
http_request = HTTPRequest(#PB_HTTP_Get, "https://api.weather.gov/points/37.372,-122.038") ;Sunnyvale, CA
If http_request
Debug "StatusCode: " + HTTPInfo(http_request, #PB_HTTP_StatusCode)
Debug "Response: " + HTTPInfo(http_request, #PB_HTTP_Response)
FinishHTTP(http_request)
Else
Debug "HttpRequest creation failed"
EndIf
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel
-
- User
- Posts: 20
- Joined: Sun Feb 14, 2021 2:55 am
- Location: Max Meadows, Virginia, USA
- Contact:
Re: Remote to MySQL
[quote="TI-994A"][/quote]
Correct me if I’m wrong, I don’t think this would work for your own database without having a script to process the request. This is how it works for public web services. I believe the OP was referring to using a database on a webserver (or any server) instead of using a local database. I have several databases I use in VB programs that are hosted remotely. I use them with VB, PHP, Python and tested with LiveCode (another multi platform development tool) and yesterday I easily tested with PureBasic.
Correct me if I’m wrong, I don’t think this would work for your own database without having a script to process the request. This is how it works for public web services. I believe the OP was referring to using a database on a webserver (or any server) instead of using a local database. I have several databases I use in VB programs that are hosted remotely. I use them with VB, PHP, Python and tested with LiveCode (another multi platform development tool) and yesterday I easily tested with PureBasic.
-
- User
- Posts: 14
- Joined: Wed Nov 23, 2022 12:51 am
Re: Remote to MySQL
I used to write BV n tier apps and I used remote SQL server all the time and just put the IP address in the connection string if memory serves me along with my vreds and it worked everytime from what I remember. Then passed an SQL query and got the results back as name value pairs, etc.
Re: Remote to MySQL
viewtopic.php?t=72861Johanson wrote: ↑Sat Feb 13, 2021 11:30 pm I want to write an application in PureBasic that will connect remotely to MySQL (download data from MySQL and send data to MySQL)
What's the best way to do this?
Will an encrypted SSL connection to Mysql servers ensure security?
Maybe it's better to use PostgreSQL?