Another change: Positional parameters are now always specified as ? (question mark), regardless of underlying database. This should make portable code easier.
Regards, Elias On 20 April 2014 23:53, Elias Mårtenson <loke...@gmail.com> 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. > > Regards, > Elias > > > On 17 April 2014 23:52, Elias Mårtenson <loke...@gmail.com> 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. :-) >> >> Regards, >> Elias >> >> >> On 15 April 2014 12:08, Elias Mårtenson <loke...@gmail.com> wrote: >> >>> On 15 April 2014 01:44, Blake McBride <blake1...@gmail.com> 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=jdbc:postgresql:mydatabase >>>> 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 <loke...@gmail.com>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 >>>>> >>>> >>>> >>> >> >
