using Npgsql.dll to access PostgreSQL server

After downloading the dlls
from http://pgfoundry.org/frs/?group_id=1000140
as directed from http://www.mono-project.com/PostgreSQL

unity2 seems ok when

Code (csharp):

1.  
2. using Npgsql
3.  

in a c# script

however
when

Code (csharp):

1.  
2. IDbConnection dbcon;
3.        dbcon = new NpgsqlConnection(connectionString);
4.  

i get

any ideas what's not happening here ?

perhaps some guidelines from the gurus at OTEE ...
ie which distribution to use;

Npgsql2.0beta1
Npgsql1.0.1

etc

 

Code (csharp):

1.  
2.  
3.  using System;
4.  //using System.Data;   ## REMOVE THIS
5.  using Npgsql;
6.  
7.  public class Test
8.  {
9.     public static void Main(string[] args)
10.     {
11.        string connectionString =
12.           "Server=localhost;" +
13.           "Database=test;" +
14.           "User ID=postgres;" +
15.           "Password=fun2db;";
16.        // IDbConnection dbcon; ## CHANGE THIS TO
17.         NpgsqlConnection dbcon;
18.  
19.        dbcon = new NpgsqlConnection(connectionString);
20.        dbcon.Open();
21.        //IDbCommand dbcmd = dbcon.CreateCommand();## CHANGE THIS TO
22.         NpgsqlCommand dbcmd = dbcon.CreateCommand();
23.        // requires a table to be created named employee
24.        // with columns firstname and lastname
25.        // such as,
26.        //        CREATE TABLE employee (
27.        //           firstname varchar(32),
28.        //           lastname varchar(32));
29.        string sql =
30.            "SELECT firstname, lastname " +
31.            "FROM employee";
32.        dbcmd.CommandText = sql;
33.        //IDataReader reader = dbcmd.ExecuteReader(); ## CHANGE THIS TO
34.        NpgsqlDataReader reader = dbcmd.ExecuteReader();
35.       while(reader.Read()) {
36.             string FirstName = (string) reader["firstname"];
37.             string LastName = (string) reader["lastname"];
38.             Console.WriteLine("Name: " +
39.                  FirstName + " " + LastName);
40.        }
41.        // clean up
42.        reader.Close();
43.        reader = null;
44.        dbcmd.Dispose();
45.        dbcmd = null;
46.        dbcon.Close();
47.        dbcon = null;
48.     }
49.  }
50.  
51.  

and now Unity can query a PostgreSQL server

 

I used this and Unity server crashes when reach the ExecuteReader

 

Use Npgsql2.0beta2.

 

This saved my day! Thanks!

 

I just ran more tests about Npgsql issues on Unity 4.3.3:
- Npgsql 1.0.1 and 2.0RTM works fine in both Editor and player
- Any Npgsql >=2.0.1 version works fine in the player, but completely crashes the Unity editor when trying to play in the editor

That's unfortunate that we can't get the goodies from the latest Npgsql with Postgres 9.3 support, but at least the "old" versions are pretty robust.

 

Actually the problem is with Unity's default assembly finding algorithm
First it searches assemblies in "C:\Program Files\Unity\Editor\Data\Managed\" than in "C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0\" than in some other folder and it seems it searches project folder path the last.
Unity is shipped with it's own Npgsql.dll in "C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0\" that is why the editor crashes. Your assembly is compiled against your own Npgsql and is run with Unity's.

So you should place your own Npgsql.dll to "C:\Program Files\Unity\Editor\Data\Managed\" and Mono.Security.dll too i think. Also I recommend to build Npgsql from source without System.DirectoryServices dependency. It is used only for integrated security feature which can be commented out from the source.

 

Where would it go on Mac OS Yosemite?

 

I delved into and found to do this in Mac OS Yosemite you need to right click on Unity and Select Show Package Contents... If you can't find the Unity app for whatever reason, and it is in your dock, hold command down and left click it. After that Navigate to Contents>Frameworks>Managed and that is where you place the npgsql.dll and Mono.Security.dll which is the same directory UnityEngine.dll is in. I haven't recompiled it without the System.Directory.Services but this is something I will be doing as well. Thank you Yatagarasu!