Erlang and PostgreSQL

Question

I try to execute simple PostgreSQL query with erlang and epgsql.

I do:

{ok, C} = pgsql:connect("localhost", "shk", "qwerty", [{database, "mydb"}]).
>> {ok,<0.34.0>}

Then:

{ok, Cols, Rows} = pgsql:squery(C, "select * from users").

But i got error:

=ERROR REPORT==== 27-Apr-2012::17:58:23 ===
** State machine <0.34.0> terminating 
** Last message in was {'EXIT',<0.32.0>,
                        {{badmatch,
                          {error,
                           {error,'ð\236ð¨ð\230ð\221ð\232ð\220',<<"42P01">>,
                            <<208,190,209,130,208,189,208,190,209,136,208,181,
                              208,189,208,184,208,181,32,34,109,121,100,98,34,
                              32,208,189,208,181,32,209,129,209,131,209,137,
                              208,181,209,129,209,130,208,178,209,131,208,181,
                              209,130>>,
                            [{position,<<"15">>}]}}},
                         [{erl_eval,expr,3}]}}
** When State == ready
**      Data  == {state,undefined,<0.35.0>,5000,
                        [{<<"application_name">>,<<>>},
                         {<<"client_encoding">>,<<"UTF8">>},
                         {<<"DateStyle">>,<<"ISO, DMY">>},
                         {<<"integer_datetimes">>,<<"on">>},
                         {<<"IntervalStyle">>,<<"postgres">>},
                         {<<"is_superuser">>,<<"off">>},
                         {<<"server_encoding">>,<<"UTF8">>},
                         {<<"server_version">>,<<"9.0.7">>},
                         {<<"session_authorization">>,<<"shk">>},
                         {<<"standard_conforming_strings">>,<<"off">>},
                         {<<"TimeZone">>,<<"posixrules">>}],
                        undefined,undefined,undefined,
                        {30932,488494147},
                        {statement,undefined,undefined,undefined},
                        73}
** Reason for termination = 
** {{badmatch,{error,{error,'ð\236ð¨ð\230ð\221ð\232ð\220',<<"42P01">>,
                            <<208,190,209,130,208,189,208,190,209,136,208,181,
                              208,189,208,184,208,181,32,34,109,121,100,98,34,
                              32,208,189,208,181,32,209,129,209,131,209,137,
                              208,181,209,129,209,130,208,178,209,131,208,181,
                              209,130>>,
                            [{position,<<"15">>}]}}},
    [{erl_eval,expr,3}]}

What's wrong i do? How can i fix it?

Thank you.

Answer 1

From epgsql docs:

Errors

Errors originating from the PostgreSQL backend are returned as {error, #error{}}, see pgsql.hrl for the record definition. epgsql functions may also return {error, What} where What is one of the following:

{unsupported_auth_method, Method} - required auth method is unsupported timeout - request timed out closed - connection was closed sync_required - error occured and pgsql:sync must be called

Try to include pgsql.hrl, capture the error and print the error message, that should point you to the right direction.

Answer 2

The error string seems to be in russian if I recognize the characters.

To view the response you can use the following command:

io:format("~ts",[<<208,190,209,130,208,189,208,190,209,136,208,181,
                   208,189,208,184,208,181,32,34,109,121,100,98,34,
                   32,208,189,208,181,32,209,129,209,131,209,137,
                   208,181,209,129,209,130,208,178,209,131,208,181,
                   209,130>>]).
отношение "mydb" не существует

A quick google translate makes me think the database mydb does not exist or you do not have permissions to use it.

Answer 3

try simply doing

Response = pgsql:squery(C, "select * from mydb"), 
io:format("~p~n",[Response]).

And see what he is giving back from the server, maybe you have typo or table don't exists etc.

also check this out http://www.erlangatwork.com/2009/01/erlang-and-postgresql.html