PostGreSQL Database Question

[swhite]

Hi

Does PB use ODBC to connect to a PostGreSQL database or does it use some other technique? If it uses something different what are the pros and cons?

Thanks,
Simon

[Jagermeister]

From the help file:

PostgreSQL is a powerful, server based database manager which support very large database and high concurrency. It is free to use in commercial projects, unlike MySQL which requires a licence to use it in a non-GPL program. There is no additional driver to install, all is ready to connect a PostgreSQL server.

PB uses a direct connection (YESSS!). No need to create a dsn or use the ODBC setup:

Code: Select all

UsePostgreSQLDatabase()
OpenDatabase(1, "host=192.168.1.254 port=5432 dbname=my_database", "user", "pass")

The main pro that i've experienced is not having to mess with the funky way to get 32bit ODBC drivers installed on Windows 7 64bit. As a tech, that means I get to push a single executable to an end user and not tinker with their system to get connected. The second benefit is speed since there is no middleman as with ODBC. Build your procedures as threads and you have a blazing fast application. And I know THAT because I built a program to replace an Access 2010 64bit form (note: Access 64bit forms will not run on 32bit systems) with 1.5 million records and it performed as if the entire Postgres database was preloaded in memory. I say go for it.

[swhite]

Thank-you for that information. Now I will have to experiment because I am wondering how the performance will be with say 30 users accessing the database server and loading a list of 500 customers. Does DatabaseQuery function produce a server side cursor or a client side cursor?

Thanks,
Simon

[AndrewM]

I note that there is a 3D programming section on the PureBasic Forum. Would it be a good idea to have database programming section too. I am a heavy ms-access user and an occasional Postgres experimenter. The thing that holds me back in developing applications in purebasic that run off postgres is getting my head around how to do things first. Most of my work related to environmental issues and these do not fit within a simple CRUD framework such as what you get when you use visual studio. Typically, I am not working with one record at a time. My data is like invoice data, where a single invoice is based on several tables at once (Customer, Order, Sales Items, etc.). Ms-access does this sort of stuff quite well, where a form includes subforms that can allow a relational heirarchy of data to be displayed at the same time and details (other than surrogate keys) can be edited at any time with results being saved automatically. I imagine other users face similar challenges and would benefit from a road map on how to use PB for data driven application development.

[Jagermeister]

@swhite: My dual core laptop w 2GB RAM (Lenovo x200) can handle about ten times that, no problemo. The port is opened, query string in, results out, memory freed. Sorry, I don't know the differences in cursors

@AndrewM: I think a database specific section would benefit everyone greatly as a lot of users, including myself, have come to PB to escape Access' clutches. I'd be glad to post up examples of my own learning process which is database heavy (though the code is actually really easy to grasp). Fred!

Cons of Access [for me]:
- users must have Windows permissions to the folder "Required Folder Privileges" http://support.microsoft.com/kb/208778 <- This is a tech headache
- all the Windows quirks http://support.microsoft.com/kb/300216
- 64bit forms won't run on 32bit Access http://www.tutcity.com/access/cant-open ... 72495.html
- it's a single file. I hope you have a consistent, redundant backup plan.

Tip: If you plan on using different types of databases simultaneously, don't forget to specify with flags at the end of your OpenDatabase()

Code: Select all

UsePostgreSQLDatabase()
UseODBCDatabase()

If OpenDatabase(0, "host=localhost port=5432 dbname=test_db", "postgres", "testpw", #PB_Database_PostgreSQL) <> 0
  CreateThread(@ListPostgres(), 100)     ; Thread for Postgres procedure
Else	
  MessageRequester("Error", "Postgres database could not be opened."+Chr(13)+Chr(13)+DatabaseError())
  End
EndIf

If OpenDatabase(1, "Catalog", "", "", #PB_Database_ODBC) <> 0
  CreateThread(@ListAccess(), 100)     ; Thread for Access procedure
Else
  MessageRequester("Error", "ODBC database could not be opened."+Chr(13)+Chr(13)+DatabaseError())
  End
EndIf

[AndrewM]

@ jagermeister
I think that what is needed is for a lot of people to pool their resource and make it easier to learn how to make database applications. As much as I like this forum, wonder if it is the right way to create a knowledge base. Recently I have been using StackExchange (in relation to QGIS) and my general feeling is that this is the way of the future. It is better that Wikis and forums. StackExchange also allows for more promotion of PureBasic and more cross fertilisation with other database communities. This is just a thought. In this case, PB could be considered a tool for using to Postgres and SQLite and discussions of building data driven apps using those databases could be hosted in the database section of StackExchange. I certainly don't want to divide the community but I am also aware that anyone writing data driven apps needs to be supported by resources that provide answers fast.

Cheers,

AndrewM

[blueznl]

Some information might be found here:

http://bluez.home.xs4all.nl/purebasic/p ... 13.htm#top

[AndrewM]

Thanks Bluenzl, I am reading the survival guide.

[ts-soft]

Only for information:
Direct Using a Database over LAN is not a problem, but over WAN (Internet) is a big security risk!

[AndrewM]

Can PB direct Postgres to run stored procedures?

[swhite]

Yes.

You should use the DatabaseUpdate() function and enter the sql command needed to tell PostgreSQL to execute the stored procedure if the stored procedure does not return a query. If it returns a query you should use DataBaseQuery().

Something like DatabaseQuery(0,"Select * from myStoredProc(param1,param2..)"