SQL :- Many Flavors (PostGreSQL).
Recently I installed PostGreSQL on windows so, I can work on a little project that I’ve been wanting to do. The objective was to learn PostgreSQL and since I’ve started to dig into the free software DAZ Studios (DAZ3D) I wanted a better understanding of the data in the tables and wanted to be able to learn various configurations of PostgreSQL,
By default DAZ Studio installs pgAdmin3 LTS as the graphical interface manager. But, there is a new pgAdmin4 1.4 that I’ll eventually be switching too for viewing data tables. For now as pgAdmin30 is out I’ll use that for this article mainly because that’s what DAZ Studio utilizes. After all items are connected I’ll show you how to create external connections via ODBCmanager for 32bit and 64bit connections. With those connections, I’ll show you how to connect to Excel and SSRS reporting. I’m keeping the examples short for now and in the future I will be posting more complex ideas and samples of how to work with the data to enhance more control over content management.
Installed PostgreSQL By DAZ
It is not necessary to actually download PostgreSQL as DAZ Studio installs a version in to the default setup as in the below image. I won’t go over DAZ Studio setup here.
And, to run the PostgreSQL graphical interface program you can select the pgAdmine3.exe
Download and Install PostgreSQL
If you want more power then what the DAZ studio installed version you can download
and install from here.
DAZ STUDIO CMS Version
Once you get here with your installation or DAZ defaults, you will need to connect PostgreSQL to your DAZ content database. From within DAZ studio you can find the
default port that DAZ Studio has used for setup. I’ve kept things at the default but you can change to suit your environment.
To get the port number to use for PostgreSQL for DAZ Studio select Edit preferences or “F2”. Select the CMS Settings tab and you’ll see the installation folder and the Port number that daz uses to communicate with the database.
To connect PostgreSQL to DAZ Studio I’ll keep the default port at 17237.
Although I’ve already connected to DAZ3D CMS database in this image, the application displayed should look like this.
Next we will want to add a new server in this case is 127.0.0.1 which is the local machine.
Fill out the values like this. If you want a password on the database you can add one here.
I named my Server “MyDaz” and used the user name of “dzcms” a name to use that I found
on DAZ3d forums.
After setup if you’re not familiar with PostgreSQL make sure you use DAZ Studio Interface to backup your database.
DAZ Studio Query Tables
To show connectivity is working I’ll show you a couple quick methods for displaying data from within tables without major coding. 🙂
From the PostgreSQL interface you can choose to view rows
Selected Content Table Rows (100 rows) is the default. On real large databases you’ll want to limit the number of results returned.
And, you want to select rows it’s similar with a slightly different interface. Select the “Select Script”
Just a few ways to query the data in the tables.
PostgreSQL ODBC Driver Installation
To access PostgreSQL form other windows applications you will need to install ODBC drivers for PostgreSQL for both 32/64bit versions.
You can find them at https://odbc.postgresql.org/
And since I’m running 64bit windows I’ll just download the MSI version for now and the 32bit if required. And, I’ve downloaded the code so I can see how it is constructed. https://www.postgresql.org/ftp/odbc/versions/msi/ get both
Psqlodbc_09_06_0200-x86.zip and Psqlodbc_09_06_0200-x64.zip as I’ve noticed that some apps that I’ve tried to connect with like 32bit versus the 64bit version.
Download and virus checked. And, unzipped into a folder. Always read the readme file.
PSQLODBC.DLL – A library to talk to the PostgreSQL DBMS using ODBC.
ODBC Setup Screens
Both installations for 32/64bit follow the same installation and setup style.
Create DSN for ODBC driver assocation
You can search for this “What is a DSN (Data Source Name)?” or go to this link https://support.microsoft.com/en-us/help/966849/what-is-a-dsn-data-source-name
further explanation of what a “DSN” name is.
From windows “RUN” type the following for 64BIT DSN creation.
I chose Unicode for my DSN.
From windows “RUN” type the following for 32BIT DSN creation.
When you select finish you can create your DSN name that will be used in other applications.
The name of the database is defined when you connect using pgAdmin3. The ports, server name and user name are the same as used
for PostgreSQL database attachment.
You can actually create this with corresponding names based on 32/64bit naming conventions.
Using “DSN” created in Excel
After you open up a new sheet in Excel, select item “From Other Sources” located on the “Data Tab”
We can select the “From Data Connection Wizard” and from there select the ODBC DSN.
What is nice about this method we get a list of tables that we can work with.
Choosing next gives us this screen.
You can paste into existing sheet or create a new sheet.
As this isn’t really a tutorial on using Excel, I thought I might add that you can sort columns filter and organize
And, by filtering you can see what purchases you made for a given date much faster then having to track this down.
Using “DSN” Created in SSRS
Not many people have SSRS or the desire to create reports with SSRS. This isn’t a full blown tutorial on using SSRS, but on
creation of the data sources. Either Shard Data Sources or Shared Data Sets and using those to connect the DSN for reporting
from DAZ Studios CMS database.
After opening up Visual Studio I’m using 2013 here. right click on Shared Data Sources
Create a name for your Shared Data Source with type as ODBC, then select edit.
Select the PostgreSQL30_32 item after testing the connection select OK.
Your connection string will be “Dsn=PostgreSQL30_32” Select OK on the Shared Data Source Properties screen.
I’ll create a new report.
Select the newly created “Shared Data Source” and select next. If you have a query
already created and is syntax checked enter that as your query string. You can select next or
enter the query builder where if there are errors will show you.
Product ‘4422’ is one that I’ve been using in my postings about DAZ3d Content Management. Or you could just drop the
WHERE clause and return all records. And if you enter the query editor you can select tables or views to work with for this report
this is the selection returned form query editor
SELECT store_id, name, guid, last_update
FROM dzcontent.product
From that Here is a simple created report from or DSN through created report.
Article Recap
Hopefully I’ve kept this article simple and easy to use. I like the follow along approach an article like this offers.
- PostgreSQL up front information
- Setup and installation of ODBC drivers
- Simple Excel Report Process
- Simple SSRS Report Process
Also, these steps for using PostgreSQL aren’t only meant for usage with DAZ Studio. You can use theses tips to install ODBC for
other usages as well. You could also set this up with Microsoft access.
My goal is to build a mini accounting system for my content. Knowing how much I paid in relation to content in my database and content on my machine
will help me out if I ever get to that PAID ARTIST category. 🙂
If you see errors or omissions please let me know. Comments are always welcome.
Thank you,
Joseph
Categories: DAZ3D, PostgreSQL, Technology, TSQL
Leave a Reply