postgreSQL is easy!

BlitzMax Forums/BlitzMax Programming/postgreSQL is easy!

Chris C

(Posted 2005) [#1]

install postgresql with the dev libs find the following files and place in the same folder you are working in

libqp.a
libpq.dll
comerr32.dll
libeay32.dll
libintl-2.dll
krb5_32.dll
ssleay32.dll

I was going to do somthing with mysql but you need hideous C structs (shudder) and postgresql is *so* easy to use as you can see below!
anyone know how to do a string grid with the gui?!!

Import "libpq.a"

Const CONNECTION_GOOD:Int=0
Const CONNECTION_BAD:Int=1
Const PGRES_TUPLES_OK:Int=2

Extern
	Function PQconnectdb(s$z)
	Function PQstatus:Int(conn:Int)
	Function PQerrorMessage$z(conn:Int)
	Function PQexec:Int(conn:Int, query_string$z)
	Function PQresultStatus:Int(res:Int)
	Function PQclear(res:Int)
  Function PQfinish(conn:Int)
  Function PQgetvalue$z(res:Int,i:Int,n:Int)
	Function PQntuples:Int(res:Int) 
EndExtern

' assumes a table that will accept 
' insert into customers (surname,dob) values ('brown','1971-2-20')

Rem
-- Table: customers

-- DROP TABLE customers;

CREATE TABLE customers
(
  id int8 NOT NULL DEFAULT nextval('customers_id_seq'::regclass),
  surname text[],
  dob date,
  CONSTRAINT "primary key" PRIMARY KEY (id)
) 
WITHOUT OIDS;
ALTER TABLE customers OWNER TO postgres;

EndRem


conn = PQconnectdb("dbname=test user=postgres password=xxxxxx")
If PQstatus(conn) = CONNECTION_BAD Then
	Print "connection To db failed"
	Print PQerrorMessage(conn)
	End
EndIf

query_string:String="select * from customers"
res = PQexec(conn, query_string)

If PQresultStatus(res) <> PGRES_TUPLES_OK Then
	Print "query failed"
	Print PQerrorMessage(conn)
  PQclear(res)
  PQfinish(conn)
	End 	
EndIf

For i = 0 To PQntuples(res)-1               ' /* loop through all rows returned */
	line:String="id "+String(PQgetvalue(res, i, 0))+" surname="+String(PQgetvalue(res, i, 2))
	line=line+" DOB="+String(PQgetvalue(res, i, 1))
	Print line
Next


PQclear(res) '     /* free result */
PQfinish(conn) ' free connection

semar

(Posted 2005) [#2]

install postgresql with the dev libs..

1) Could you please tell me where to find the postgresql installation ?

2) With which kind of database does postgresql work ? (MSSQL, SQLBASE, MSACCESS, MYSQL, ORACLE...) ?

Sergio.

Chris C

(Posted 2005) [#3]

1 postgresql.org
2 postgres

enjoy!

FlameDuck

(Posted 2005) [#4]

postgreSQL is easy!

Nice. That does seem easy indeed. I like the way it handles the result set.

With which kind of database does postgresql work ?

It is a database.

Chris C

(Posted 2005) [#5]

its got to be the most straight forward and down right sensible interface I've come across!

xlsior

(Posted 2005) [#6]

Very nice...

Any MySQL libs in the works, anyone?

Chris C

(Posted 2005) [#7]

mysql has a hideous c++ binding, its doable but a pain...

FlameDuck

(Posted 2005) [#8]

And PostgreSQL is better.

Chris C

(Posted 2005) [#9]

[tounge-in-cheek]
at last someone who isn't getting zombie like onto the mysql band wagon!
[/tounge-in-cheek]

Jim Teeuwen

(Posted 2005) [#10]

Im gonna be a @#!*-ant and point out a typo in yer code :)

CONNEVTION_GOOD should probably be CONNECTION_GOOD

Otherwise, very nice find! :)

Dubious Drewski

(Posted 2005) [#11]

I'm sorry for my naivety, but could someone give me an
example of a practical use for....whatever it is you guys are
discussing, so I might get a clue?

I have no idea what postgresql or mysql are, but I'd like to find out!

Chris C

(Posted 2005) [#12]

@defiance - good spot! the one time I dont use superstrict!!

@drew - quick intro for you...

sql is a Structured Query Language, a "standard" of !sorts! that most database engines have in common

a database can have multiple tables each table has its own collection of fields in which each record (or item) in the table stores its data.

for instance you might execute the query
select * from customers where id=2

this will select all fields (*) from the customer table that have records containing a value of 2 in its id field

a great deal of web sites exist soley in databases google mambo for a good example of a database driven web site engine

There are many uses for database engines, not usally in a game thoough!

Brucey

(Posted 2005) [#13]

postgresql and mysql are relational databases. (Good for storing and retrieving large quantities of data ;-)

Other names in the database world include Oracle, Informix, DB2, SQL Server....

Ideally, a very nice max module would work like the MaxGUI Drivers currently do, where you could use a particular database-driver (say, postgresql), but access it the same for all. eg:

local db:TDatabase = PostgresDriver.Create("host", "db", "user", "pass")
local res:TResultSet = db.execute("SELECT * FROM names")
print "There are " + res.count() + " names in the database."

in such a way that you could swap "PostgresDriver" for "MySQLDriver" etc...

:-p

Chris C

(Posted 2005) [#14]

we have a volunteer! well done Brucey, I look forward to seeing the modules ;p

seriously tho using postgres takes a dozen or so commands hardly worth a module

on the other hand mysql would require a c++ wrapper writing and it hardly seems worth the effort, compared to what you get with postgres

Brucey

(Posted 2005) [#15]

heh...
Well, maybe if I wasn't so busy finding bugs in the GUI....

But great work you're doing there... I do like postgresql. And if you can get it working over the three platforms... ;-)

Chris C

(Posted 2005) [#16]

havent tried it in Linux (gave up with max in Linux) but providing you have postgres installed the code should need no changes yiu just need the libpq.a file to compile it in linux...

Let us know if you have chance to try in in linux will you Brucey