LabVIEW Web Development Discussions

cancel
Showing results for 
Search instead for 
Did you mean: 

Accessing a PostgreSQL database from a web service

I did this a while back, but I had to redo it, so I thought I'd post it to save any some pain.

There are a few issues that need to be addressed in accessing a database from a Web Service. First, you are in 32 bit mode. Second, any files you need to access are locked up in the deployed server. Under Windows 7, the first part was painful, as you needed to enable 32 bit data sources, but under Windows 10, much easier:

Set up ODBC.png

Simply add a 32 bit data source. I installed the 32 bit odbc drivers for PostgreSQL, so choose this, point it at you server, and save it with a name that makes sense for your application:

ODBC DSN.png

In my case, this is "Effect_DB". Now, when I want to open this database in my VI, I just use the name of the data source:

Open DB VI.png

Note that if you are deploying the service on an older system (my production server is Windows 7), you need to configure the data source for the 32 bit driver. There is a discussion of this at http://communities.bentley.com/other/old_site_member_blogs/bentley_employees/b/allen_brown_bentleys_...

Of course, most of the examples you'll find want you to create a .udl file, but this isn't so good, as you need to get that file into your deployed server bundle. The easier way is to open ODBC Administrator:

W7 ODBC.png

Now create a System DSN called "Effect_DB" (or whatever name you used on the development server) referencing the 32 bit driver:

driver.png

The only remaining issue is authentication to the database. Ideally, you'd use the same credentials you used to authenticate to the web server, but NI doesn't make this easy. PostgeSQL even provides an external authentication mechanism, so in concept you could grab the remote username from the web service and have PostgreSQL call some process in LWS that validates the user, but if this can be done, I haven't figured this out. I've ranted about lack of documentation of the security API in Web Services before, and if anyone from NI cares to respond with a useful suggestion, I'd appreciate it.

0 Kudos
Message 1 of 1
(4,697 Views)