Connecting to Google Cloud SQL

Hello Everyone,
I am working on an IoT project, which requires to host database online.

Earlier, I have made a desktop app with Processing 3 and have regularly used to it save data to Google Cloud SQL.
Now I have created a similar app using Xojo, but I am unable to get the data onto Google Cloud MySql.

Therefore I even tried opening an account with Xojo Cloud, hoping that if not Google Cloud, I shall use Xojo Cloud. However,
I am unable to connect to it as well. Forget the app, I am unable to connect to Xojo Cloud Mysql even from the SQL Workbench.

I believe its something to do with the Firewall Ports, which I believe need to be open or something. Can anyone please help me on this.

In the MyWindow open event handler, I have put the below code.

Dim db As New MySQLCommunityServer
db.UserName = “XXXXX”
db.Password = “XXXXX”
db.Host = “XXXX”
db. Port = 3306
db.DatabaseName = “XXXXXX”
Try
db.Connect
MessageBox("Connected to ")
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

For connecting to Google Cloud SQL too, I have used the same above code, replacing the IP address with the one provided by Google Cloud SQL.
The error I receive everytime is “SSL connection error: unknown error number”.

I have not enabled SSL encryption anywhere, neither in Google Cloud nor in Xojo.

Can anyone please help me on this. I shall be grateful to use any of the options either Xojo or Google Cloud SQL.

I already have some data on Google Cloud SQL from my existing Processing app.

Could you post your HTTPS socket code please?

Hey Guys,

I am very pleased to inform that I was finally able to connect to both Xojo Cloud MySql instance as well as to Google Cloud MySql instance. For those of you who too might be looking for a similar query or for those who might have a similar question in the near future, I would like to explain what I did.

Xojo Cloud

For connecting to Xojo Cloud, I recollected reading somewhere that the firewall port needs to be opened on Port 3306. Being a novice as stated earlier, I didn’t know as to how to achieve that. I have never every created a web app before.

I referred to a tutorial video on creating a webapp and followed the below instruction

Step 1 - Open Firewall Ports

a. Create a new webapp by clicking onto New Project - WebApp. Name the app and save it.
b. Use any Control and in the Control Action Event use the below code. ( I used a Button Control,
titled it “Open Port” and in the Action Event entered the below Code)
"
Var fwpo As New XojoCloud.FirewallPort(3306, XojoCloud.FirewallPort.Direction.Outgoing)
fwpo.Open() // This call is synchronous

Var fwpi As New XojoCloud.FirewallPort(3306, XojoCloud.FirewallPort.Direction.Incoming)
fwpi.Open() // This call is synchronous
"
I then run this app to check for errors and then deployed it into Xojo Cloud. Run the app once in the cloud.
With this I was able to open the firewall ports and connect to Xojo Cloud from external MySql WorkBench.

Step 2 - Connecting to the Xojo Cloud MySql instance from desktop application
a. Please note that using Xojo default Database Plugin I was able to connect to Xojo Cloud. However, I was not able to
connect with Google Cloud SQL using default Xojo MySql plugin. ( I am yet to find a reason, could be my error somewhere ).

b. Therefore I purchased a MBS MySQL plugin for Xojo. As shown in the video tutorial, copied the MBS MySql plugin to Xojo Plugin folder ( in my case C:\Program Files\Xojo\Plugin folder).
Also remember to download the MySql libraries from MBS site. The libmysql.dll file is required for using MBS plugin.

c. I used the below code in a Open Window Event to connect to the Xojo Cloud or to Google Cloud MySQL.

"
dim con as new SQLConnectionMBS

try

// where is the library?
con.SetFileOption con.kOptionLibraryMySQL, Volume(0).Child(“XXXX”).Child(“XXXX”).Child(“libmysql.dll”) // point this to the location of libmysql.dll file on your PC.

// connect to database
// in this example it is Oracle,
// but can also be Sybase, Informix, DB2
// SQLServer, InterBase, SQLBase and ODBC

dim server as string = “XXX.XXX.XXX.XXX,3306@database name”

con.Connect(server,“username”,“password”,SQLConnectionMBS.kMySQLClient)

MsgBox “We are connected!”

// Disconnect is optional
// autodisconnect will ocur in destructor if needed
//con.Disconnect

//msgbox “We are disconnected!”

catch r as RuntimeException
MsgBox r.message

// SAConnection::Rollback()
// can also throw an exception
// (if a network error for example),
// we will be ready
try

// on error rollback changes
con.Rollback

catch rr as runtimeexception
MsgBox rr.message
end try
end try
"
Remember the above format correctly.

I was getting wrong with the above format ( I was either forgetting to mention the Port or forgetting to mention the database name). For PostegreSQL, the default TCP port number shall change from 3306 to 5432.

With that I am able to connect to any database ( Xojo / Google Cloud SQL ) or I believe others.

Kindly correct me If you find any error in my method.