newlispfanclub.alh.net

Posted: **Mon Oct 15, 2012 6:56 am**

Hello Lutz,

I tried to add a new connectdb fixes and connect: PgSQL of module postgres.lsp.

The reason is that the parameters are omitted and for postgres PQconnectdb is possible, depending on the version parameter is increased.

PgSQLconnect is (C library "libpq"), has been changed in order to allow connections without a password, even if the host name and the setting.

You can be written as follows,
ex1.(PgSQL:connect "" "" "" "mydb");; host (socket file), user (login-name), passwd (empty)
ex2.(PgSQL:connectdb "host=srv port=25432 user=name dbname=mydb") ;; connect other port.

I would appreciate it if you can change it.

Thanks,

postgres.zip: postgres.lsp, postgres.lsp.udiff; (5.5 KiB) Downloaded 581 times

Posted: **Mon Oct 15, 2012 8:42 am**

Hi,

add more useful function PgSQL:fnumber added, with extend PgSQL:fetch-value.

PgSQL:fnumber - returns column number.
PgSQL:fetch-value - column number or column name.

"create table test (key integer, value text)"
key | value
-----+-------
1 | xx
2 | yy

(PgSQL:query "SELECT * FROM test")

Code: Select all

;; all cases returns "xx".

(PgSQL:fetch-value 0 1)
(PgSQL:fetch-value 0 (PgSQL:fnumber "value"))
(PgSQL:fetch-value 0 "value")

thanks.

postgres.zip: postgres.lsp(full), udiff; (6.35 KiB) Downloaded 576 times

Posted: **Mon Oct 15, 2012 3:50 pm**

Thanks for the additions to postgres.sql Unya.

But I do not completely understand the changes to PgSQL:connect.

Does test-pgsql - at the end of the module - still work/connect ?

Posted: **Mon Oct 15, 2012 9:56 pm**

Never mind, I think I understand the changes now. The new module is online here:

http://www.newlisp.org/code/modules/postgres.lsp.html

and also part of:

http://www.newlisp.org/downloads/develo ... nprogress/

Posted: **Tue Oct 16, 2012 1:34 am**

Thanks Lutz,

Thank you for adding, I took a look at the changes.

I think about the query and I wish I could propose Parameterized query function.

Posted: **Tue Oct 16, 2012 11:07 am**

Hello Lutz,

new PgSQL:query function - extended with parameter, and compatible.

Code: Select all

;; Example execute
(PgSQL:query "select $1||$2" "abc" "def")
(PgSQL:fetch-all) ; -> (("abcdef"))

(PgSQL:query "select $1 + $2" 10 20)
(PgSQL:fetch-all) ; -> (("30"))

(PgSQL:query "select $1::timestamp + $2::interval" "2012-10-01 00:00:00" "123456 seconds")
(PgSQL:fetch-all) ; -> (("2012-10-02 10:17:36"))

(PgSQL:query "create table tbl (a integer, b integer)")
(dotimes (i 10) (PgSQL:query "insert into tbl values ($1, $2)" i (* i 2)))
;    a | b
;   ---+----
;    0 |  0
;    1 |  2
;    2 |  4
;    ...
;    9 | 18

(PgSQL:query "select * from tbl where a=$1 or a=$2" 2 9)
(PgSQL:fetch-all) ; -> (("2" "4") ("9" "18"))

Thanks,

postgres-newquery.zip: postgres.lsp + postgres.lsp.udiff(ver 2.10); (6.59 KiB) Downloaded 576 times

Posted: **Tue Oct 16, 2012 1:15 pm**

Very nice! I am traveling the next two days but will integrate it before the weekend.

You can replace:

Code: Select all

(dotimes (i nParams) (push ptr-fmt ""))

with shorter, faster:

Code: Select all

(dup ptr-fmt nParams)

Posted: **Tue Oct 16, 2012 2:59 pm**

Thank you Lutz,

I appreciate your advice, grateful for taking the time.

Have a nice weekend.

newlispfanclub.alh.net

PgSQL:connect/connectdb (postgres.lsp)

PgSQL:connect/connectdb (postgres.lsp)

More ADDED Re: PgSQL:connect/connectdb (postgres.lsp)

Re: PgSQL:connect/connectdb (postgres.lsp)

Re: PgSQL:connect/connectdb (postgres.lsp)

Re: PgSQL:connect/connectdb (postgres.lsp)

Re: PgSQL:connect/connectdb (postgres.lsp)

Re: PgSQL:connect/connectdb (postgres.lsp)

Re: PgSQL:connect/connectdb (postgres.lsp)