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.