Test Connect Script to PostgreSQL Server

Unity Engine
1

I’m struggling to find how to connect to an existing postgreSQL database from within Unity, let alone send a query. I’m sure there is some basic concept I’m missing. I am basically familiar with some SQL, javascript, and a some of the C variants. I’m trying to understand the WWW command, but I’m not clicking.

Could someone please post a small script in any of the above languages that connects to a postgreSQL db and performs a SELECT * FROM testdb using the following:

Server Name: testserver or ip 192.168.2.65 (whichever is easier)
Database: testdb
Username: testuser
Password: testpass

Thanks.

2

The WWW class is for interacting with web pages, or at least
a HTTP based server. The easiest way to perform the query is
probably to set up some kind of web-based front-end to the
PostgreSQL-server.
Something like Ruby on Rails (rubyonrails.com) might be useful
for this.
Otherwise you could take a look at Mono (mono-project.com).
Since Unity is based on Mono it might be possible to use a
PostgreSQL wrapper from there.
But performing queries on a PostgreSQL server from Unity is
probably going to take some work.

3

Has anybody ever tried using classes of the System.Data namespace(s) in a Unity game? That might be the most convenient and quick way to do it. Obviously, that’ll only work for game servers or game clients that are in a local network with the database server - otherwise you’ll run into all kinds of unpleasant issues and in WANs I would guess that having some sort of “Web service” (in the general sense “a service provided over the Web”) would be easier and more practical to set up.

I’m not sure if the System.Data namespaces are the same in Mono as they are in Microsoft’s .NET, but if they are, that would probably be the most direct and convenient way for accessing a database from Unity (I think it’s just some 3 or 5 or so lines of code to get a SELECT executed). If there’s a .NET-driver for PostgreSQL, it’ll probably be based on the System.Data namespace, so that would probably be the “natural way” to do it.

Jashan

4

I’ve gotten unity to talk to PHP that then can do just about anything via SQL with a database.

It obviously requires SQL/PHP/Linux. It works great for me. Still have to add some functionality and straighten things out a bit though.

5

Thanks for the pushes in the right direction. Mono does indeed have a set of libraries to support postgreSQL.

Npgsql is a .Net Data Provider for Postgresql Database Server.

Npgsql/bin/ms1.1 - Npgsql compiled for MS.Net 1.1

Npgsql/bin/mono - Npgsql compiled for Mono

I believe the following workflow will solve the problem:

postgreSQL > Npgsql > C# Mono > Unity

Anyone (read Unity devs from their Asset Server) want to share a simple C# script to accomplish the above? Or maybe a little plugin :slight_smile:

6

Here is a c# script that uses Npgsql
Perhaps it will point you in the right direction

65990–2436–$dbmesh_197.cs (11.9 KB)

7

Thanks for the script. I thought it had helped, but I’m still struggling with I think the most basic of things. I’m not sure I’ve got Npgsql installed correctly.

I get this error:

Assets/DBTestConnect.cs(5) error: The type or namespace name `Npgsql’ could not be found. Are you missing a using directive or an assembly reference?

Can someone confirm where the Npgsql needs to be. I get the same error regarding “using System.Data” as well.

Finally, here is the C# demo script that comes w/the Npgsql documentation. Could someone translate this to a Unity script? I would post my version but as I have been completely unsuccessful in connecting, I don’t want to clutter the thread with bad code.

using System;
using System.Data;
using Npgsql;

public class NpgsqlUserManual
{
  public static void Main(String[] args)
  {
    NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");
    conn.Open();
    conn.Close();
  }
}

BTW I have read, created and edited records from the DB I’m trying to connect to with RealBasic and Visual Studio on a Mac and PC so I can confirm that the db is in fact listening and operating correctly.

Thanks,
Will

8

for any with the same problem that will_w remember also copy from C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0 the “System.Data.dll” into assets

2 Likes