Shell novice - help needed (OSX)

Hi all,

First of all I should make the disclaimer that I’m a UNIX novice so I may need a little hand-holding with this.

I’m trying to connect to a MySQL database through SSH tunnel with a pem key. Now if I do this in Terminal (my key is in the Downloads folder):

ssh -v -N -L 7777:127.0.0.1:3306 -i ~/Downloads/mydomain.pem ubuntu@mydomain.com 

…then my app can connect just fine using the normal

  DB = New MySQLCommunityServer
  DB.Host = "127.0.0.1"
  DB.UserName = "root"
  DB.Password = ""
  DB.DatabaseName = "mydatabase"
  DB.Port = 7777

What I’m trying to do is do the terminal bit in Xojo too. What I’ve tried is the following but the debug app just hangs and I have to force quit.

  Dim s as New Shell
  Dim cmd as string="ssh -v -N -L 7777:127.0.0.1:3306 -i ~/Downloads/mydomain.pem ubuntu@mydomain.com"
  s.Execute(cmd)

What am I doing wrong?

Thanks in advance

You need to use an Asynchronous shell mode. Set s.mode = 2 before calling s.Execute.

But, why don’t you use Xojo’s built in DB connectivity options?

Awesome. That’s exactly what I needed. Thanks for the very quick reply.

How would you suggest I do it, bearing in mind I need an SSH tunnel and the key to connect?

Agh! This was working fine for me last time I tried a few weeks ago. Just tried again and I’m getting this error.

If I do the port forwarding in Terminal like I previously was it works fine. And the key is OK because I can connect with Sequel Pro.

Any ideas? I’m going nuts here!

Does the tunnel still work if you create it in Terminal?

Hi Greg

Yes - that’s what has me confused.

I don’t really know why, but changing the string name from ‘cmd’ to ‘xxx’ finally fixed it!

‘cmd’ isn’t a reserved word is it?

No, and it’s the variable name I use all the time. Something else is going on here…

Kem - you’re right. After a restart it no longer connects again.

Is this something I could get some direct support to try and resolve if I pay for the Desktop licence?

I’m hoping to get the purchase authorised by my boss this afternoon (it’s an app I’m building for the company I work for). They’re really impressed with what I’ve done so far but I can’t be having every employee having to connect with Terminal!

Playing around with this a little more and picking up what I can from other threads I’ve now added this Do…Loop after the s.Execute.

(The last thing that comes back when doing it in Terminal is “entering interactive session.” so I’m waiting for that)

So with the code below I get a successful connection and the first page of data pulled from the DB in my app loads fine. Any subsequent code that queries the DB however gives a “MySQL server has gone away” error. Is this because the process is being killed? And if so, how could I stop that?

So my code now is (having replaced my domain name with generic ones for the purposes of posting here) :

  Dim s as New Shell
  Dim cmd as string = "ssh -v -N -L 2222:127.0.0.1:3306 -i ~/Documents/mydomain.pem ubuntu@mydomain.com"
  s.Mode=2
  s.Execute (cmd)
  
  Do
    App.DoEvents(10)
  Loop Until Instr(s.Result, "interactive session.")>0
  
  DB = New MySQLCommunityServer
  DB.Host = "127.0.0.1"
  DB.UserName = "root"
  DB.Password = ""
  DB.DatabaseName = "mydatabase"
  DB.Port = 2222
  
  If DB.Connect Then
    mIsConnected = True
    MsgBox("Connection to Database successful.")
 
  Else
    mIsConnected = False
    MsgBox("Error connecting to Database : "  + DB.ErrorMessage)
  End If

I’m also aware that App.DoEvents is frowned upon but I’m trying anything I can to get this working.

Just like you have a DB Property, you’ll also need one for the Shell. Once that code goes out of scope, your “s” (Shell variable) is destructed and consequently your ssh connection is too.

William - Thank you so much! ! !

That’s exactly what I was missing. Working great now.

Woohoo!