Text only | Text with Images

newLISP Fan Club

Forum => newLISP and the O.S. => Topic started by: unya on October 14, 2012, 11:56:01 PM

Title: PgSQL:connect/connectdb (postgres.lsp)
Post by: unya on October 14, 2012, 11:56:01 PM
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,


[attachment=0]postgres.zip[/attachment]
Title: More ADDED Re: PgSQL:connect/connectdb (postgres.lsp)
Post by: unya on October 15, 2012, 01:42:09 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 cases returns "xx".

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


thanks.


[attachment=0]postgres.zip[/attachment]
Title: Re: PgSQL:connect/connectdb (postgres.lsp)
Post by: Lutz on October 15, 2012, 08:50:08 AM
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 ?
Title: Re: PgSQL:connect/connectdb (postgres.lsp)
Post by: Lutz on October 15, 2012, 02:56:05 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/development/inprogress/
Title: Re: PgSQL:connect/connectdb (postgres.lsp)
Post by: unya on October 15, 2012, 06:34:28 PM
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.
Title: Re: PgSQL:connect/connectdb (postgres.lsp)
Post by: unya on October 16, 2012, 04:07:06 AM
Hello Lutz,



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


Code Select

;; 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,


[attachment=0]postgres-newquery.zip[/attachment]
Title: Re: PgSQL:connect/connectdb (postgres.lsp)
Post by: Lutz on October 16, 2012, 06:15:17 AM
Very nice! I am traveling the next two days but will integrate it before the weekend.



You can replace:
Code Select
(dotimes (i nParams) (push ptr-fmt ""))

with shorter, faster:
Code Select
(dup ptr-fmt nParams)
Title: Re: PgSQL:connect/connectdb (postgres.lsp)
Post by: unya on October 16, 2012, 07:59:05 AM
Thank you Lutz,



I appreciate your advice, grateful for taking the time.



Have a nice weekend.
Text only | Text with Images