Remote to MySQL

Just starting out? Need help? Post your questions and find answers here.
Johanson
User
User
Posts: 40
Joined: Sat Aug 01, 2020 9:53 am

Remote to MySQL

Post by Johanson »

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?
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Remote to MySQL

Post by infratec »

You don't know if a secured connection ensure security?

It ensure it more than a no secured connection :wink:

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.
JaxMusic
User
User
Posts: 20
Joined: Sun Feb 14, 2021 2:55 am
Location: Max Meadows, Virginia, USA
Contact:

Re: Remote to MySQL

Post by JaxMusic »

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.
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: Remote to MySQL

Post by TI-994A »

Johanson wrote:...an application in PureBasic that will connect remotely to MySQL (download data from MySQL and send data 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:

> 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 :D
JaxMusic
User
User
Posts: 20
Joined: Sun Feb 14, 2021 2:55 am
Location: Max Meadows, Virginia, USA
Contact:

Re: Remote to MySQL

Post by JaxMusic »

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.
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: Remote to MySQL

Post by TI-994A »

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

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
This service actually polls its own databases with the provided latitude/longitude queries, and returns the corresponding weather results.
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 :D
JaxMusic
User
User
Posts: 20
Joined: Sun Feb 14, 2021 2:55 am
Location: Max Meadows, Virginia, USA
Contact:

Re: Remote to MySQL

Post by JaxMusic »

[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.
HoosierDaddy
User
User
Posts: 14
Joined: Wed Nov 23, 2022 12:51 am

Re: Remote to MySQL

Post by HoosierDaddy »

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.
User avatar
Paul
PureBasic Expert
PureBasic Expert
Posts: 1243
Joined: Fri Apr 25, 2003 4:34 pm
Location: Canada
Contact:

Re: Remote to MySQL

Post by Paul »

Johanson 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?
viewtopic.php?t=72861
Image Image
Post Reply