www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - Need example of usage DerelictPQ

reply "Suliman" <evermind live.ru> writes:
Could anybody provide any simple examples of usage DerelictPQ. I 
do not have experience of C, and I can't understand how to use 
this driver.

I need just basics like connect, select and insert.

http://code.dlang.org/packages/derelict-pq

thanks!
Dec 24 2014
next sibling parent "Suliman" <evermind live.ru> writes:
I have done next:

	string connString = "psql -h localhost -p 5432 -U postgres -d 
testdb";
	package PGconn* conn;
	 property bool nonBlocking(){ return PQisnonblocking(conn) == 1; 
}

	this(parseConfig parseconfig)
	{
		void connect()
		{
			try
			{
				DerelictPQ.load(getcwd ~ buildPath("\\libs\\libpq.dll"));
				
				conn = PQconnectdb(toStringz(connString));
				if( !nonBlocking && PQstatus(conn) != 
ConnStatusType.CONNECTION_OK )
				throw new Exception("Can't connect to DB");


			}
			catch ( DerelictException de )
			{
				writefln("Failed to load libpq.dll: %s", de.msg);
			}



		}
		connect();

But it runtime I am getting next error:

object.Exception source\app.d(203): Can't connect to DB
----------------
0x00402D31 in 
D3app10PostgreSQL6__ctorMFC3app11parseConfigZ7connectMFZv at D:\Pr
oject\2014\seismoDownloader\source\app.d(203)
0x00402C3F in app.PostgreSQL 
app.PostgreSQL.__ctor(app.parseConfig) at D:\Projec
t\2014\seismoDownloader\source\app.d(216)
0x0040208B in _Dmain at 
D:\Project\2014\seismoDownloader\source\app.d(30)

What I am missing? Where I can to look right format of connection 
string for this driver?
Dec 24 2014
prev sibling next sibling parent reply "Adam D. Ruppe" <destructionator gmail.com> writes:
I haven't used the derelict version but I have used the C library 
itself and wrapped it briefly in my postgres.d here:

https://github.com/adamdruppe/arsd/blob/master/postgres.d

(implements an interface from database.d in the same repo)



I used very, very little of the library, but it is enough to get 
basic results.


Your connection string can just be "dbname=NAME_HERE". Given your 
command line, "dbname=testdb" would be the first one I try.

Here's the docs on those strings:
http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS


Your host and port are the default, so no need to list them. You 
might need to set the user, but a better option there might be to 
grant your regular user (whatever one you are logged into the 
operating system as) permission to access the database, then the 
default will work for that too.



The second class in my file shows how to get basic results.

	string[] row;
         for(int i = 0; i < numFields; i++) {
              string a;
              if(PQgetisnull(res, position, i))
                  a = null;
              else {
                  a = to!string(PQgetvalue(res, position, i), 
PQgetlength(res, position, i));
               }
              row ~= a;
          }


gets it as an array of strings. This simple option loses the type 
checking you can get from the database but it works for a lot of 
stuff too.
Dec 24 2014
parent reply "Suliman" <evermind live.ru> writes:
On Wednesday, 24 December 2014 at 14:08:53 UTC, Adam D. Ruppe 
wrote:

 I haven't used the derelict version but I have used the C 
 library itself and wrapped it briefly in my postgres.d here:

 https://github.com/adamdruppe/arsd/blob/master/postgres.d


Thanks! But few questions:
1. how to build it with dub?
2. I tried to: dmd app.d database.d postgres.d but get message 
that "File Not Found pq.lib"
3. Can I build it's simply: dmd app.d ?
Would it able to import other 2 file?
Dec 24 2014
parent reply "Adam D. Ruppe" <destructionator gmail.com> writes:
On Wednesday, 24 December 2014 at 19:26:11 UTC, Suliman wrote:

 1. how to build it with dub?


I don't know, I don't use dub.


 2. I tried to: dmd app.d database.d postgres.d but get message 
 that "File Not Found pq.lib"


Download the lib file from me here:

http://arsdnet.net/dcode/pq.lib


You'll still need the DLLs, the difference between my lib and the 
Derelict one is I bind it statically whereas Derelict loads the 
dll at runtime. Both need the dll, but Derelict skips the lib 
step.

It is the same API though, so you could also just use my source 
as an example to guide writing your derelict based program.


 3. Can I build it's simply: dmd app.d ?
 Would it able to import other 2 file?


Won't work since then the other functions won't be compiled, 
always need to pass them along to dmd too or build a separate 
.lib (but that's not really worth the hassle)
Dec 24 2014
parent reply "Suliman" <evermind live.ru> writes:
Adam, I trying to build simple app:

import std.stdio;
import postgres;
import database;

void main() {
	auto db = new PostgreSql("dbname = test2");

	foreach(line; db.query("SELECT * FROM customer")) {
		writeln(line[0], line["customer_id"]);
	}
}

and getting next error:

D:\123>dmd app.d database.d postgres.d
postgres.d(124): Error: struct database.Row member resultSet is 
not accessible
postgres.d(139): Error: struct database.Row member row is not 
accessible


Also I can't understand why I do not need specify login and pass 
when I connection to DB?
Dec 28 2014
parent "Adam D. Ruppe" <destructionator gmail.com> writes:
On Sunday, 28 December 2014 at 08:41:15 UTC, Suliman wrote:

 import postgres;
 import database;


Those should include the package name:

import arsd.postgres;

Since it publicly imports the base clas, you don't need to import 
database yourself. (You do still need to pass all files to dmd 
though, your command line should remain the same.


 and getting next error:


Those are protection errors because the package name isn't right, 
fix that and it should work (assuming you haven't modified the 
other source files)


 Also I can't understand why I do not need specify login and 
 pass when I connection to DB?


Postgresql authenticates based on what operating system user 
you're logged in as. You can change that in the server settings 
but normally the default works well.
Dec 28 2014
prev sibling next sibling parent reply Mike Parker <aldacron gmail.com> writes:
On 12/24/2014 8:56 PM, Suliman wrote:

 Could anybody provide any simple examples of usage DerelictPQ. I do not
 have experience of C, and I can't understand how to use this driver.

 I need just basics like connect, select and insert.

 http://code.dlang.org/packages/derelict-pq

 thanks!


DerelictPQ is only a binding to libpq. The only difference is the 
DerelictPQ.load method. Just follow the libpq documentation.

http://www.postgresql.org/docs/9.1/static/libpq.html
Dec 24 2014
parent reply Mike Parker <aldacron gmail.com> writes:
On 12/25/2014 11:23 AM, Mike Parker wrote:

 On 12/24/2014 8:56 PM, Suliman wrote:

 Could anybody provide any simple examples of usage DerelictPQ. I do not
 have experience of C, and I can't understand how to use this driver.

 I need just basics like connect, select and insert.

 http://code.dlang.org/packages/derelict-pq

 thanks!


 DerelictPQ is only a binding to libpq. The only difference is the
 DerelictPQ.load method. Just follow the libpq documentation.

 http://www.postgresql.org/docs/9.1/static/libpq.html


Actually, Derelict binds to version 9.3, so the proper link should be

http://www.postgresql.org/docs/9.3/static/libpq.html
Dec 24 2014
parent reply "Suliman" <evermind live.ru> writes:
 DerelictPQ is only a binding to libpq. The only difference is 
 the
 DerelictPQ.load method. Just follow the libpq documentation.

 http://www.postgresql.org/docs/9.1/static/libpq.html


 Actually, Derelict binds to version 9.3, so the proper link 
 should be

 http://www.postgresql.org/docs/9.3/static/libpq.html


But could you look at my example and say what I am doing wrong? 
For my regret I do not know C and I have some problems with 
understanding this doc.
Dec 24 2014
parent Mike Parker <aldacron gmail.com> writes:
On 12/25/2014 3:03 PM, Suliman wrote:

 DerelictPQ is only a binding to libpq. The only difference is the
 DerelictPQ.load method. Just follow the libpq documentation.

 http://www.postgresql.org/docs/9.1/static/libpq.html


 Actually, Derelict binds to version 9.3, so the proper link should be

 http://www.postgresql.org/docs/9.3/static/libpq.html


 But could you look at my example and say what I am doing wrong? For my
 regret I do not know C and I have some problems with understanding this
 doc.


I've never used libpq. Any C samples you find should be mostly directly 
translatable to D, with just a few minor adjustments. Look around for 
such a sample, or a libpq tutorial if such a thing exists, and try to 
port it over to D. Then if you have any trouble, I'm sure you can find 
help here with translating it.
Dec 25 2014
prev sibling parent number <putimalitze gmx.de> writes:
On Wednesday, 24 December 2014 at 11:56:40 UTC, Suliman wrote:

 Could anybody provide any simple examples of usage DerelictPQ. 
 I do not have experience of C, and I can't understand how to 
 use this driver.

 I need just basics like connect, select and insert.

 http://code.dlang.org/packages/derelict-pq

 thanks!



A bit late, but maybe useful for others, and googling 'dlang 
derelict postgres example' brings you here..

The default ubuntu (16.04) postgres version caused runtime errors 
for me (DerelictPQ.load()), what worked was installation from 
here:
https://www.postgresql.org/download/linux/ubuntu/

the example expects a postgres user/role 'sammy' with password 
'sammypw' and a database 'sammy' with a table as shown in 
comments and set up as here:
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

if you don't specify a host at PQconnectdb() then you might get 
an error
'Peer authentication failed for user "sammy"'
when you run the binary as a OS user different from 'sammy', or 
you would have to run the program as the postgres user like
sudo -iu sammy /path/to/prog

---- dub.json
{
	"name": "prog",
	"targetType": "executable",
	"sourceFiles": [ "prog.d" ],
	"dependencies": {
		"derelict-pq": "~>2.2.0"
	}
}

---- prog.d
import core.stdc.stdlib;
import std.stdio;
import std.conv;
import derelict.pq.pq;

/*
	CREATE TABLE playground (
	    equip_id serial PRIMARY KEY,
	    type varchar (50) NOT NULL,
	    color varchar (25) NOT NULL,
	    location varchar(25) check (location in ('north', 'south', 
'west', 'east', 'northeast', 'southeast', 'southwest', 
'northwest')),
	    install_date date
	);
	
	INSERT INTO playground (type, color, location, install_date) 
VALUES ('slide', 'blue', 'south', '2014-04-28');
	INSERT INTO playground (type, color, location, install_date) 
VALUES ('swing', 'yellow', 'northwest', '2010-08-16');

  */

int main(string[] args)
{
	int retval = EXIT_SUCCESS;

	DerelictPQ.load();

	PGconn* conn = PQconnectdb("user = 'sammy' password = 'sammypw' 
dbname = 'sammy' host = '127.0.0.1'");
	if (PQstatus(conn) != CONNECTION_OK)
	{
		writefln("ERROR (connection): %s", 
to!string(PQerrorMessage(conn)));
		retval = EXIT_FAILURE;
	}
	else
	{
		writeln("OK (connection)");

		PGresult* result = PQexec(conn, "select * from playground");
		if (PQresultStatus(result) != PGRES_TUPLES_OK)
		{
			writefln("ERROR (result): %s", 
to!string(PQerrorMessage(conn)));
			retval = EXIT_FAILURE;

		}
		else
		{
			writeln("OK (result)");

			int nbFields = PQnfields(result);
			writeln("nbFields: ", nbFields);

			int nbRows = PQntuples(result);
			writeln("nbRows: ", nbRows);

			for (int f; f < nbFields; f++)
			{
				writef("%16s", to!string(PQfname(result, f)));
			}
			writeln("");

			for (int r; r < nbRows; r++)
			{
				for (int f; f < nbFields; f++)
				{
					ubyte* ub = cast(ubyte*)(PQgetvalue(result, r, f));
					int len = PQgetlength(result, r, f);
					char[] c = cast(char[])ub[0..len];
					string s = to!string(c);
					writef("%16s", s);
				}
				writeln("");
			}
		}
		PQclear(result);
	}
	PQfinish(conn);

	return retval;
}

----

If someone has a better way to convert the PQgetvalue() to 
string, i'm interested :)
The functions are as follows:
     alias da_PQgetvalue = const(ubyte)* 
function(const(PGresult)*,int,int);
     alias da_PQgetlength = int function(const(PGresult)*,int,int);

have a nice day
Mar 21 2018