Enable network access from Community Edition in VirtualBox VM

Moderator
Moderator

Almost all our courses are fine with the standard setting that the public network is connected to a VirtualBox Host-Only adapter, because we don't have to connect to the outside world.

One exception is the Advanced Analytics course where we have some demos that do require that. And there may be other use cases where this may come in handy.

This is the problem with the standard settings:

create schema test;
--/
CREATE OR REPLACE LUA SCALAR SCRIPT
lua_connect(hostname varchar(4096), port varchar(4096))
RETURNS varchar(4096)
AS
socket = require("socket")
function run(ctx)
sock = assert(socket.tcp())
assert(sock:connect(ctx.hostname, ctx.port))
sock:close()
return 'OK'
end
/
SELECT lua_connect('google.com', '80') res FROM dual;

[Code: 0, SQL State: 22001] Lua Error "Host name lookup failure" caught in script "TEST"."LUA_CONNECT" at line 4 (Session: 1658890362988332563)

One way to resolve that: Change the public network to connect to a bridged adapter in VirtualBox:

exa-Uwe_0-1591089097156.png

Then restart the VM. Notice that it comes back up with different IPs for EXAoperation and database connections:

exa-Uwe_1-1591089584119.png

So you need a different URL to connect to EXAoperation and another connection string for DbVisualizer.

Then in EXAoperation, set the DNS servers to Googles free DNS servers:

exa-Uwe_2-1591089786552.png

Now the test works:

SELECT lua_connect('google.com', '80') res FROM dual;

OK

The demo in the Advanced Analytics course works as well now:

open schema test;
--/
CREATE OR REPLACE PYTHON SCALAR SCRIPT get_currencies()
EMITS (CURRENCY_SYMBOL VARCHAR(2000000), CURRENCY_NAME VARCHAR(2000000)) AS
import urllib
import json
def run(ctx):
url = "https://free.currencyconverterapi.com/api/v6/currencies?apiKey=d7ae42bdee761bbda7d2"
s = urllib.urlopen(url).read()
data = json.loads(s)

for r in data["results"]:
ctx.emit(r, data["results"][r]["currencyName"])
/
select get_currencies() ;

DZD Algerian Dinar
NAD Namibian Dollar
QAR Qatari Riyal
EGP Egyptian Pound
BGN Bulgarian Lev
BMD Bermudan Dollar
PAB Panamanian Balboa
BOB Bolivian Boliviano ...

I have modified the API key above so it doesn't work. If you want to try, you need to apply for your own API key at the site 😀

Many thanks to Alexey who brought this to my attention and suggested this solution!

 

0 REPLIES 0