Need example of usage DerelictPQ

[number]

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