Re: [Bug-apl] Update on SQL support

[Elias Mårtenson]

Latest update:

The 2D matrix bind parameters is now implemented. Transaction support is there (but not really tested yet). Bost SQLite and Postgres also works.

There is also a .apl file with function definitions that smakes the API look nicer.

My next step is to get the available database libraries detected using autoconf and otherwise integrate the build into GNU APL proper before I had over the code to Jürgen if he wants to integrate.

Ideally, I'd like there to be a very simple way to load this library (and support APL code) straight out of a newly installed GNU APL installation. That's why I've been talking about default directories for )COPY and whatnot. :-)

Regards,

Elias

On 15 April 2014 12:08, Elias Mårtenson <address@hidden> wrote:

On 15 April 2014 01:44, Blake McBride <address@hidden> wrote:

In terms of connecting to PostgreSQL (and probably most other 'real' databases), I think the connection string should have more (optional) arguments separated by a comma.  The ones I use everyday with PostgreSQL are:

driver=org.postgresql.Driver

url="">

user=userxyz

password=thepassword

I'm using Kerberos for authentication everywhere, which is why I don't have to include usernames or passwords in my connect strings, and all I had to specify was the host name. Of course you can add whatever connect options you want.

The way I've built the SQL support is simular to JDBC in a sense. You specify the underlying database type (the argument to the left of the connect command) and everything on the right-hand side is passed to the backend. The SQLite backend only uses a single string (the file name) while the Postgres one passes it as a conninfo string.

The conninfo strng is simply multiple parameters, separated by spaces where each parameter is of the form key=value. In other words, something like this:

    'postgresql' SQL∆Connect "user=foo password=bar host=somehost"

 

Of course this is an example from Java.  C must have corresponding parameters.  (I hope you are not _requiring_ an ODBC setup.)

No, I don't require ODBC. Although it wouldn't be hard to implement an ODBC provider as well.

 

The point being that you cannot connect to a database by just knowing where it is.  You usually need a username and password so the database can determine your access permissions.

Only if you're not using Kerberos. :-)

But, like I said, using cleartext password in the connect sthing should work perfectly fine. It's just that I don't normally use it myself.

 

The driver option probably makes more sense for Java or if you are linking (via static library or shared library) to a native PostgreSQL library.  But it can be useful if you are using runtime loading.

I'm not using any runtime loading for the driver. They are all linked into the lib_sql.so library. The availability of the different database libraries will probably be detected when running ./configure.

 

I'm sorry, I actually came from the APL world and not the APL2 world (although I know what nested arrays are).  I do not understand your SQL∆Select and SQL∆Exec lines do.  Could you provide a brief explanation?

It's very simple. It's just a wrapper around the native variably entry point. So, instead of typing:

    'select * from foo where a=?' SQL[3,db] 10

You'd simply type:

    'select * from foo where a=?' SQL∆Select[db] 10

It's imply implemented by a wrapper function like this:

    ∇Z←query SQL∆Select[dbId] args

    Z←query SQL[3,dbId] args

    ∇

Thanks!

No problems.

Regards,

Elias

Blake

On Mon, Apr 14, 2014 at 11:25 AM, Elias Mårtenson <address@hidden> wrote:

Just an update: Postgres support almost works now. I have some issues determining the correct data type of the returned values, but once that is resolved it's time to work on the 2D array arguments.

I've changed SQL[1] (connect) to accept a left argument, being the database type. I will also make it register mnemonic functions so that you don't have to see the function numbers. Thus, once the 2D stuff is ready you'll be able to copy the content of "foo" in an SQLite database into table "bar" in a Postgres database like this:

'libsql' ⎕FX 'SQL'

dbA ← 'sqlite' SQL∆Connect '/path/to/database'

dbB ← 'postgresql' SQL∆Connect 'host=hostname.of.database.com'

result ← 'select * from foo' SQL∆Select[dbA] ⍬

'insert into bar (a,b) values (?,?)' SQL∆Exec[dbB] result

SQL∆Close dbA

SQL∆Close dbB

Are you all OK with this syntax?

Regards,

Elias