r3wp [groups: 83 posts: 189283]
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

World: r3wp

[PgSQL] PostgreSQL and REBOL

Oldes
16-Jun-2005
[1]
safra
Pekr
17-Jun-2005
[2]
? :-)
Oldes
21-Jun-2005
[3]
Just wanted to say, that I needed async postge SQL protocol, so I 
used the Nenad's non-async version located here: http://rebol.softinnov.org/pgsql/
and made async my own version, which is available here: http://box.lebeda.ws/~hmm/rebol/a-pgsql.r
Graham
21-Jun-2005
[4]
How well does it work?
Oldes
22-Jun-2005
[5]
At this moment I'm testing it for logging messages into DB form this 
experimental chat I'm working on: http://box.lebeda.ws/~hmm/and 
it was running without problems, the chat is working and data stared. 
I will make more tests after I finish some app. design things.
Ingo
19-Jul-2005
[6]
A question about nenads original pgsql (0.90),


I have a database using UNICODE as the encoding, and try to connect 
via pgsql.
Characters get encoded as, e.g.  \010 ( #"^/" ) etc. 

I tried to change the client encoding within postgresql to winxxxx, 
or latinxxx, but it made no difference.

any ideas?
Oldes
24-Jul-2005
[7x6]
yes, there is a bug in nenads code
in function to-octal replace "\\" with "\"
and you can also change line 276:
from: pl/matched-rows: either pos [to integer! trim pos][0]
to: pl/matched-rows: either not error? try [pos: to integer! trim 
pos][pos][0]
Im' using the non async version with these changes with no problems 
and use UNICODE encoding as well
Ingo
25-Jul-2005
[13x2]
Thanks Oldes!
Something else that works: 

insert  db "set client_encoding to 'win'"


now pgsql understands what rebol sends it, doesn't work for "", 
though.
Graham
30-Aug-2005
[15]
Hmm.  To unpack Nenad's archive, you have to rename it to pgsql-r090.rip 
from pgsql-r090-1.rip
Graham
1-Sep-2005
[16x2]
just installed postgesql 8 on windows 2003, but can't seem to create 
tables.  Get syntax errors reported.
Can connect okay on localhost, but not from another pc on the lan 
.. guess it's due to some configuration setting.
Thr
1-Apr-2006
[18]
.
Graham
19-Jan-2007
[19x2]
Are there any gotchas with the postgresql driver ?
Oldes ... do you have a download somewhere of your patched async 
version?
Janeks
2-Mar-2007
[21]
I have PostSQL 8.2

It seems that I can connect and send SQL commands and they are executed, 
but whenever in the SQL script/string appears word TABLE I am getting 
error:
>> insert db {CREATE TABLE my_table (
{    prod_no integer, name text, price numeric);}
** Script Error: Invalid argument: TABLE
** Where: forever
** Near: to integer! trim pos

Any workaround?
Thanks!
Oldes
2-Mar-2007
[22x3]
I don't know, I don't have/use psql for quite long time
but as you see. try the change I mentioned before: 

 pl/matched-rows: either not error? try [pos: to integer! trim pos][pos][0]
pl/matched-rows: either error? try [pos: to integer! trim pos][0][pos]
Janeks
2-Mar-2007
[25]
Thanks Oldes - it worked!

The minor thing now is that I can not use UTF8 encoding for databases 
then I am getting in case of language specific characters:


>> insert db {INSERT INTO my_first_table VALUES (1, 'kani', 12.34);}

** User Error: ERROR:  invalid byte sequence for encoding "UTF8": 
0xede76e

** Near: insert db {INSERT INTO my_first_table VALUES (1, 'kani', 
12.34);}
>>


But well - it seems that I can still use Win1257 encoding or try 
to set client encoding - that I did not yet tried/learn.
Oldes
2-Mar-2007
[26x8]
you can use my script to encode unicode chars, search for links in 
unicode channel
do http://box.lebeda.ws/~hmm/rebol/ucs2_latest.r
do http://box.lebeda.ws/~hmm/rebol/utf-8_latest.r
utf-8/encode-2 ucs2/encode "abc"
but first load correct charset, for example:
ucs2/load-rules "ISO-8859-1"
or let the sql server know, what encoding you are using (that it's 
not UTF8)
this should the best way (I just don't remember the correct command)
in MySQL I for example use: "SET character_set_client = cp1250"
http://www.postgresql.org/docs/7.4/static/multibyte.html
if you set the encoding on the server side, you don't need to convert 
your strings in Rebol but let the server to do that job
Janeks
2-Mar-2007
[34]
It seems for me too the best aproach too.

One another case where to use encoding funcions could be PDF maker 
- I had trouble to get pdf output into special characters of my language.
Oldes
2-Mar-2007
[35]
here are charset ids you can load into my scripts: print read http://box.lebeda.ws/~hmm/rebol/projects/ucs2/latest/charmaps.rb
Gabriele
3-Mar-2007
[36]
janeks: unfortunately the pdf format does not support unicode, so 
that's not trivial to do... i will do it, eventually.
DaveC
29-May-2007
[37]
I've been using  pgsql-protocol (r090)  to store HTML reports into 
the DB. 


It can write a large compressed string into a text column ok. Trying 
to read the data back causes the program to hang. The length of the 
stored string returns 182514 chars. (In psql: SELECT length(html) 
FROM reports....)

Test Code;

sql: {SELECT html FROM reports.html_reports WHERE ticket = '2007-756-fcw'}
insert db-port sql
html: first db-port
.... rest of test program never gets executed.

Does anyone know if this a known behaviour? Thanks

For information, if I select any other column, the program runs as 
expected. The data is these columns is a lot smaller in length.
Dockimbel
29-May-2007
[38x2]
They might be several reasons why this don't work. Try using LIMIT 
as a cheap workaround : http://pgsqld.active-venture.com/queries-limit.html
If this don't work, tell me what Postgres server version are you 
using, I'll try to reproduce the error.
DaveC
29-May-2007
[40]
Ok thanks. I'll double check the exact number tommorrow, but it's 
the 8.1 series. LIMIT would be a good idea, but the query returns 
only one row. It's the amount of data in the one column that is the 
problem. (Whole HTML reports in one chunk). I was thinking that I 
should split up the report into a set of smaller chunks as it gets 
generated  anyway.


I'll give you some more info tommorrow. I appreciate you are busy 
with Cheyenne, so thanks for your time.
Dockimbel
29-May-2007
[41]
No problem, I'm usually much more productive under pressure ;-)
Graham
20-Nov-2007
[42]
What's the current status of the postgresql driver   ?   Should one 
use mysql instead?
Ashley
20-Nov-2007
[43]
Or SQLite?
Graham
20-Nov-2007
[44x2]
Not SQLite ... as I need something that is not memory based.
Got databases over 2Gb in size
Pekr
21-Nov-2007
[46x3]
SQLite is not memory based imo. It was RebDB. SQLite is the coolest 
thing on earth for the purpose it is supposed to serve :-) I mean 
- small, embeddable SQL engine. MySQLLib is not good solution imo, 
as it does not use SQL syntax and is not even free for embedded stuff.
There is one defficiency with SQLite - it is one user solution. Well, 
you can lock iirc, on file level (yes, even records), but SQL requests 
are not serialised, so you have to think twice, when more than one 
client would connect to database.
As for PostGress, IIRC the driver was in better state than the one 
of mySQL? It even used continual parsing. Doc said later on even 
mySQL driver will be rewritten that way, and maybe it already is, 
dunno.
Graham
21-Nov-2007
[49x2]
Oh ... my bad.
I will have multiple clients accessing the database .. webserver, 
 update utiliities and main engine