[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bug-apl] Update on SQL support

From: Elias Mårtenson
Subject: Re: [Bug-apl] Update on SQL support
Date: Mon, 21 Apr 2014 12:42:03 +0800

Another change: Positional parameters are now always specified as ? (question mark), regardless of underlying database. This should make portable code easier.


On 20 April 2014 23:53, Elias Mårtenson <address@hidden> wrote:
Another update. There is now a new command: SQL[8] which returns a list of all tables in the database.

Eventually SQL[9] will return a list of columns for a given table, but that's less important for now.


On 17 April 2014 23:52, Elias Mårtenson <address@hidden> wrote:
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. :-)


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:


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


No problems.



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?


reply via email to

[Prev in Thread] Current Thread [Next in Thread]