I made a small module that I use myself for doing all the work behind the scenes.
Maybe it will help here...
Module... save as "_Postgres.pbi"
Code: Select all
DeclareModule Postgres
UsePostgreSQLDatabase()
Declare.i pDB_Connect(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s)
Declare pDB_Disconnect(dbID.i)
Declare.s pDB_CreateDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbnew.s,dbowner.s)
Declare.s pDB_DeleteDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbdelete.s)
Declare.s pDB_CreateUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,newuser.s,newpassword.s)
Declare.s pDB_DeleteUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,userdelete.s)
EndDeclareModule
Module Postgres
Procedure.s DBQuery(dbID.i,Query.s)
Protected result$=""
If DatabaseQuery(dbID,Query)
result$="OK: "+Query
FinishDatabaseQuery(dbID)
Else
result$="Error: "+DatabaseError()
EndIf
ProcedureReturn result$
EndProcedure
Procedure.i pDB_Connect(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s)
ProcedureReturn OpenDatabase(#PB_Any, "host="+dbhost+" port="+dbport+" dbname="+dbname,dbuser,dbpassword,#PB_Database_PostgreSQL)
EndProcedure
Procedure pDB_Disconnect(dbID.i)
CloseDatabase(dbID)
EndProcedure
Procedure.s pDB_CreateDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbnew.s,dbowner.s)
Protected qry.s,result$
hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
If hDB
qry="create database "+dbnew+" owner="+dbowner+";"
result$=DBQuery(hDB,qry)
pDB_Disconnect(hDB)
EndIf
ProcedureReturn result$
EndProcedure
Procedure.s pDB_DeleteDB(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,dbdelete.s)
Protected qry.s,result$
hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
If hDB
qry="drop database "+dbdelete+";"
result$=DBQuery(hDB,qry)
pDB_Disconnect(hDB)
EndIf
ProcedureReturn result$
EndProcedure
Procedure.s pDB_CreateUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,newuser.s,newpassword.s)
Protected qry.s,result$
hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
If hDB
qry="create user "+newuser+" With CREATEDB PASSWORD '"+newpassword+"';"
result$=DBQuery(hDB,qry)
pDB_Disconnect(hDB)
EndIf
ProcedureReturn result$
EndProcedure
Procedure.s pDB_DeleteUser(dbhost.s,dbport.i,dbname.s,dbuser.s,dbpassword.s,userdelete.s)
Protected qry.s,result$
hDB=pDB_Connect(dbhost,dbport,dbname,dbuser,dbpassword)
If hDB
qry="drop user "+userdelete+";"
result$=DBQuery(hDB,qry)
pDB_Disconnect(hDB)
EndIf
ProcedureReturn result$
EndProcedure
EndModule
UseModule Postgres
Example code...
Code: Select all
XIncludeFile "_Postgres.pbi"
;Example #1
;Create User & Database --- must use Admin Account & Password to create New User
;and we'll say my Postgres Server is located at 192.168.0.101
Debug pDB_CreateUser("192.168.0.101",5432,"postgres","postgres","password","jack","black")
Debug pDB_CreateDB("192.168.0.101",5432,"postgres","jack","black","mydatabase","jack")
;Example #2
;Put Data into Database
dbID=pDB_Connect("192.168.0.101",5432,"mydatabase","jack","black")
If dbID
If DatabaseQuery(dbID,"CREATE TABLE software (app varchar(50),price double precision);")
DatabaseQuery(dbID,"INSERT INTO software (app,price)values('Pac Man',4.99);")
DatabaseQuery(dbID,"INSERT INTO software (app,price)values('Defender',2.49);")
DatabaseQuery(dbID,"INSERT INTO software (app,price)values('Asteroids',1.87);")
EndIf
pDB_Disconnect(dbID)
EndIf
;Example #3
;Read Database
dbID=pDB_Connect("192.168.0.101",5432,"mydatabase","jack","black")
If dbID
If DatabaseQuery(dbID,"SELECT app,price from software ORDER BY price asc;")
While NextDatabaseRow(dbID)
Debug GetDatabaseString(dbID,0)+" $"+GetDatabaseDouble(dbID,1)
Wend
EndIf
pDB_Disconnect(dbID)
EndIf