SCM

Forum: help

Monitor Forum | Start New Thread Start New Thread
What is the correct dbtype of char ? [ reply ]
By: t oka on 2013-08-12 03:19
[forum:1020852]
What is the correct dbtype of char ?
I would like to use the same result as PgAdmin query tool.

create table chartable
(
caseno int,
char10 char(10)
);

insert into chartable values(1, '123456');
insert into chartable values(2, '1 ');
insert into chartable values(3, ' ');


-----
string query = "select caseno, char10 from chartable where char10 = @col1";

NpgsqlParameter.DbType = DbType.StringFixedLength;
NpgsqlParameter.Value = "1";
Result:
Hit caseno 2.

NpgsqlParameter.NpgsqlDbType = NpgsqlDbType.Char;
NpgsqlParameter.Value = "1";
Result:
Hit caseno 2.

pgAdmin query tool;
select caseno, char10 from chartable where char10 = '1'
Hit caseno 2.

-----
string query = "select caseno, char10 from chartable where char10 = @col1";

NpgsqlParameter.DbType = DbType.StringFixedLength;
NpgsqlParameter.Value = "1 ";
Result:
No hit.

NpgsqlParameter.NpgsqlDbType = NpgsqlDbType.Char;
NpgsqlParameter.Value = "1 ";
Result:
Hit caseno 2.

pgAdmin query tool;
select caseno, char10 from chartable where char10 = '1 '
Hit caseno 2.

-----
string query = "select caseno, char10 from chartable where like = @col1";

NpgsqlParameter.DbType = DbType.StringFixedLength;
NpgsqlParameter.Value = "1_____ ";
Result:
Hit caseno1, caseno 2.

NpgsqlParameter.NpgsqlDbType = NpgsqlDbType.Char;
NpgsqlParameter.Value = "1_____ ";
Result:
No hit.

pgAdmin query tool;
select caseno, char10 from chartable where char10 like '1_____ '
Hit caseno1, caseno 2.

Entity Framework with npgsql won't accept money datatype [ reply ]
By: Peter . on 2013-07-28 13:18
[forum:1020851]
I'm creating a .NET C# app using Entity Framework 5 and PostgreSQL DB. I'm using npgsql 2.0.12 as Data Provider and Database First approach.

First, I created model files (csdl, ssdl, mdl) and object layer using edmgen with connection string to the postgresql db as parameter.

The edmgen shows a warning when doing it's job:

warning 6005: The data type 'money' is not supported, the column 'amount' in table 'tablename' was excluded.

Then it all works fine, just that column is not included in the model. Of course, I'm not happy with that, since I need this column. I should not modify DB structure.

So, what I did is I modified the model files manually - CSDL, SSDL, MDL respectively:
<Property Name="amount" Type="Decimal" Nullable="false" Precision="19" Scale="4" />
<Property Name="amount" Type="money" Nullable="false" />
<ScalarProperty Name="amount" ColumnName="amount" />

I also edited the ObjectLayer by adding this attribute, just like any others in this table.

Now when I run the app I get:

Schema specified is not valid. Errors: The Type money is not qualified with a namespace or alias. Only primitive types can be used without qualification.

Who's fault is that? EF? npgsql? edmgen?

I found something on pgfoundry.org/forum, but changing to numeric doesn't help me. [User Manual][5] indicates that money is a supported type.

How can I make this thing work with money-typed column?

How to automatically generate C# wrapper code for database access with npgsql? [ reply ]
By: enuhtac enuhtac on 2013-07-12 18:37
[forum:1020777]
Hello,

I would like to write a PostgreSQL database application in C# using Visual Studio. The idea is to use some database modelling tool to design the database and automatically generate the actual database and C# wrapper code to access the database. The C# application should not be allowed to modify the database directly - only by calling Stored Procedures that implement all the necessary business logic. All this should be achieved using tools that are freely available.

I would presume this is quite a standard task. I cannot be the first one to perform a task like this. Nevertheless I haven't found a solution yet.

Obviously npgsql has to be part of solution as it's the only free PostgreSQL provider for .NET.
The most straight forward approach would be to use Microsoft Entity Framework. But as far as I could figure out npgsql does not support the Microsoft Database designer (please correct me if I'm wrong).
Nevertheless EF can be used if the database is already present (so I use pgModeler to create the database model and the database itself). The tool Edmgen creates the EF internal ssdl, csdl and msl files from an existing database. Additionally it creates the needed C# wrapper code.
But this does not work in the presence of stored procedures: they are included in the ssdl file but not in the other automatically generated files (including the C# wrapper code).
I fixed this issue by writing a tool that reads in the ssdl file and generates the missing entries in the other files. Now the C# wrapper code can be generated.
Alas this is still not enough. When calling a stored procedure using this wrapper code npgsql throws an execption. I filed a bug report (http://pgfoundry.org/tracker/index.php?func=detail&aid=1011343&group_id=1000140&atid=590) addressing this issue. So hopefully this will get fixed in a future version of npgsql.

Nevertheless my initial question remains: how do other people solve my initila problem?

Many thanks,
enuhtac

Close command is very slow [ reply ]
By: t oka on 2013-07-09 12:59
[forum:1020757]
This code is very slow.

command.CommandText = "select * from bigtable";
NpgsqlDataReader reader = command.ExecuteReader();
// nop
reader.Close(); // very slow.

Oracle(ODP.NET) is not slow.
PostgreSQL(Npgsql) is very slow.

Why is it very slow?

Integrated Authentication Problem [ reply ]
By: t oka on 2013-07-12 04:42
[forum:1020776]
I am not good at English. Please excuse me if you don't understand what I am trying to say.

I have set up a windows workgroup trying to use SSPI.

In the same server, it is no problem.

It is a problem when divided into AP server and DB server.
I run my .NET client program(Npgsql) on AP server.

pg_log:
FATAL: could not accept SSPI security context
Detail:The token supplied to the function is invalid
(80090308)

I run psql on AP server. It was no problem.
I run my .NET client program(Devart dotConnect for PostgreSQL) on AP server. It was no problem.

I think the same problem as this person.
http://www.postgresql.org/message-id/1363746831694-5748984.post@n5.nabble.com

Establishing SSL Connection [ reply ]
By: Jarrod Kinsley on 2009-05-19 18:14
[forum:1004978]
Could someone provide an example of establishing an SSL connection to PostgreSQL. I have a Windows application that talks to a PostgreSQL server on Linux. It works fine if I have SSL disabled, and I can make an SSL connection to the server using psql on the Vista box.

When I set ssl=true and sslmode=require in the application, an IO Exception is thrown stating 'The authentication or decryption has failed.'

At this point, I am assuming I am missing a step in establishing the SSL connection. However, I have been unable to find any example code for establishing an SSL connection such that one wuld expect to find in the user manual. I am not going to post code or trace information until I know the issue is not caused by me missing a step in the process.

I am using VS2008 with Npgsql-2.0.5. For now I am just seeking an example of establishing the connection. If there is a underlying issue causing this problem, I will gladly provide additional information so that it can be fixed.

Save PostgreSql &quot;Point&quot; type with Npgsql and EF [ reply ]
By: Anestis Kivranoglou on 2013-07-15 11:14
[forum:1020778]

I am trying to save a Point Type into Postgresql I have tried this way.

(Db table Model definition)

public NpgsqlPoint Position { get; set; }

I set the Object with this method

posData.Position = new NpgsqlPoint (34.3244,23.2344);

But when i call .SaveChanges on the dbContext i get an exception of Not Null Constraint Violation , so i guess EF is trying to insert a null.

I have debuged the posData.Position before entry and the object contains proper data.

I have searched google but i didn't find any example or someone having the same problem.

Any Clues?

(I have tried changing the Model variable type to String like "20.1224,41.32656" this saves a point successfully but when i try to access the point back as string EF gives me only the class type as string)

Thank You In Advance

Is it possible to call stored procedures using EF and Npgsql 2.0.12? [ reply ]
By: enuhtac enuhtac on 2013-07-09 21:05
[forum:1020766]
I tried to do so but I failed.
I suppose this to be a missing feature of npgsql. But please correct me if I am wrong.

When calling a stored procedure from within entity framework the following steps are executed (as far as I could reconstruct using the debugger):
* EF creates an object of type "System.Data.Common.CommandTrees.DbInsertCommandTree" that references the EDM function definition.
* passing this object EF calls the method "CreateDbCommand" of class "NpgsqlServices".
* "CreateDbCommand" in turn calls "TranslateCommandTree" (also a "NpgsqlServices" method).
* The task of "TranslateCommandTree" is to create the actual SQL code. It branches depending on the actual type of the command tree (e.g. "DbQueryCommandTree", "DbInsertCommandTree", ...) but is doesn't check for "DbInsertCommandTree", instead an exception is thrown.

So I conclude that npgsql does not support calling stored procedures from within EF.
Refering to my google findings doing so is quite an unusual approach anyway. So I'm wondering what I right/usual approach would be to call stored procedures from C# code without having to write wrapper code for each procedure?

For more details about my test code please refer to http://stackoverflow.com/questions/16497672/entity-framework-and-postgresql-exception-when-calling-stored-procedure.

I hope anybody can help me.

Is it possible to call stored procedures using EF and Npgsql 2.0.12? [ reply ]
By: enuhtac enuhtac on 2013-07-09 21:01
[forum:1020765]
I tried to do so but I failed.
I suppose this to be a missing feature of npgsql. But please correct me if I am wrong.

When calling a stored procedure from within entity framework the following steps are executed (as far as I could reconstruct using the debugger):
* EF creates an object of type "System.Data.Common.CommandTrees.DbInsertCommandTree" that references the EDM function definition.
* passing this object EF calls the method "CreateDbCommand" of class "NpgsqlServices".
* "CreateDbCommand" in turn calls "TranslateCommandTree" (also a "NpgsqlServices" method).
* The task of "TranslateCommandTree" is to create the actual SQL code. It branches depending on the actual type of the command tree (e.g. "DbQueryCommandTree", "DbInsertCommandTree", ...) but is doesn't check for "DbInsertCommandTree", instead an exception is thrown.

So I conclude that npgsql does not support calling stored procedures from within EF.
Refering to my google findings doing so is quite an unusual approach anyway. So I'm wondering what I right/usual approach would be to call stored procedures from C# code without having to write wrapper code for each procedure?

For more details about my test code please refer to http://stackoverflow.com/questions/16497672/entity-framework-and-postgresql-exception-when-calling-stored-procedure.

I hope anybody can help me.

Large SQL query generated with incorrect identifier [ reply ]
By: Alex LastName on 2013-07-03 23:14
[forum:1020751]

sqlerror.txt (64) downloads
I have a fairly messy query in LINQ in order to retrieve the data that I need. I have tried posting on stackoverflow for different solutions and have so far come up with nothing that results in a working or more efficient method. Everything works fine until it comes time to sort the data.

Depending on what the sort identifier is, the SQL query produced when .ToList() is called will be incorrect.

I have attached the LINQ code and the produced SQL query that is executed when .ToList() is called.

If I sort by ManagerName ASC, ManagerName DESC, StatusName ASC, or StatusName DESC the final ORDER BY line of the generated SQL uses the identifier "Distinct1" when no other reference to "Distinct1" is made.

The actual SQL query should properly name this identifier to "Limit1". At least if it did, the query would retrieve the requested data.

The temporary solution to this is to produce the list before any ordering or paging is done. This however causes a rather large performance issue with large amounts of data (which this project has).

If anyone has a possible solution or remedy to this I would greatly appreciate it.

Close command is very slow. [ reply ]
By: t oka on 2013-07-09 11:43
[forum:1020755]
This code is very slow.

command.CommandText = "select * from bigtable";
NpgsqlDataReader reader = command.ExecuteReader();
// nop
reader.Close(); // very slow.

Oracle(ODP.NET) is not slow.
PostgreSQL(Npgsql) is very slow.

Why is it very slow?

possibility to retrieve value as stream? [ reply ]
By: Chris Notsch on 2013-06-20 13:07
[forum:1020746]
Hi,
I am having a quite severe issue with writing and retrieving large fields.
Specifically, I am taking about double-arrays and bytea (files).

For bytea the performance is not that critical, but beyond 10MiB the memory consumption for setting the value already reaches close to 1GB putting it close to the limit of a 32bit-app.

For double[] the performance is quite important, I need to retrieve occationally several thousand fields where each holds an array of 1e+4 to 5e+7 elements.
I do realize that this is a lot of data and will take time to process, however, the data is retrieved from the server in less than 1/100th of the time that it takes the driver to convert it to a system type.

Now my question is: Is it possible to get acess to the base stream for a field/row without modifiying the source code of the driver to expose it?
Basically I am looking for something like the large object manager approach, but with the support of pretty much any table/query.

Reason for the question is: I could solve the memory issue in that case by processing the arrays/files in small chunks and due to knowing exactly what to expect as return value also use specific/static casts to convert the values much faster.

Could not load file or assembly 'policy.2.0.Npgsql' or one of its dependencies. [ reply ]
By: D Das on 2013-06-18 11:52
[forum:1020742]

Npgsql_error_2013-06-18_13-46-16.png (10) downloads
I am a developer and am trying to move to using the newer Npgsql 2.0.12, but am crippled by this error.
I just downloaded the latest Npgsql2.0.12.0-bin-ms.net2.0.zip and am having trouble running it against a .Net 2.0 Application Pool.

I get the error as in the Subject of this thread:

Could not load file or assembly 'policy.2.0.Npgsql' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.BadImageFormatException: Could not load file or assembly 'policy.2.0.Npgsql' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

The error message is pretty self-explanatory that I have to use 4.0 .Net framework version for the Application pool to solve this error, but, my query is why cannot I continue using 2.0 .Net framework version of the Application pool anymore, like with 2.0.11.0??

Please help as I am quite stuck. We have many websites and I have to justify the need to move to using 4.0 .Net framework version of the Application pool.



NPGSQL and money datatype (EURO) issue [ reply ]
By: Samuele Forconi on 2011-09-03 14:27
[forum:1014982]
Hi all,
I have problems with the money datatype using npgsql.

I have a small test application that I'm using to test the money datatype with the following query:
"UPDATE prices SET price = @price WHERE id = 16"

I paste below a small piece of my code in order to understand what I'm doing:

NpgsqlCommand = new NpgsqlCommand(query, conn);
decimal myMoney = 8.99m;
NpgsqlParameter np = command.Parameters.Add("@price", NpgsqlTypes.NpgsqlDbType.Money);
np.Value = myMoney;

The result is that the row is updated but the amount of money is wrong.


Setting:
myMoney = 8.99m I obtain in db €899,00 (I expected €8,99)
myMoney = 100m, I obtain in db €100,00 (correct)
myMoney = 75.25m, I obtain in db €7.525,00 (I expected €75,25)

I cannot figure out what I'm doing wrong.
Any help or suggestions are appreciated.

Thank you.
Regards,
Samuele Forconi.

Linq to entities : Include + OrderBy + Skip or Take : Wrong SQL generation [ reply ]
By: Olivier MATROT on 2013-05-21 11:48
[forum:1020694]
Hello,

I'm experiencing a small problem when using linq to entities.
The usage of Include() with at least two levels of related objects in conjonction with Skip Or Take does produce incorrect SQL.

This is always reproductible :

var query = context.ExaminationDpi.Include(@"Examination.FunctionalUnits").OrderBy(ed => ed.Name).Skip(0).Take(25);

does generate the following :

SELECT "Project1"."exdid" AS "exdid","Project1"."exmid" AS "exmid","Project1"."exdlabel" AS "exdlabel","Project1"."exdcode" AS "exdcode","Project1"."exdfuncunit" AS "exdfuncunit","Project1"."exddefaultstatus" AS "exddefaultstatus","Project1"."exdlaterality" AS "exdlaterality","Project1"."xmin" AS "xmin","Project1"."exmid1" AS "exmid1","Project1"."extid" AS "extid","Project1"."anpcode" AS "anpcode","Project1"."mdccode" AS "mdccode","Project1"."exmlabel" AS "exmlabel","Project1"."exmdescription" AS "exmdescription","Project1"."exmduration" AS "exmduration","Project1"."exmftcount" AS "exmftcount","Project1"."exmindicator" AS "exmindicator","Project1"."exmcolour" AS "exmcolour","Project1"."datastatus" AS "datastatus","Project1"."xmin1" AS "xmin1","Project1"."C1" AS "C1","Project1"."funid" AS "funid","Project1"."svcid" AS "svcid","Project1"."funlabel" AS "funlabel","Project1"."funnumber" AS "funnumber","Project1"."funtypes" AS "funtypes","Project1"."funapplication" AS "funapplication","Project1"."corid" AS "corid","Project1"."datastatus1" AS "datastatus1","Project1"."tppid" AS "tppid","Project1"."xmin2" AS "xmin2"
FROM (
SELECT "Extent1"."exdid" AS "exdid","Extent1"."exmid" AS "exmid","Extent1"."exdlabel" AS "exdlabel","Extent1"."exdcode" AS "exdcode","Extent1"."exdfuncunit" AS "exdfuncunit","Extent1"."exddefaultstatus" AS "exddefaultstatus","Extent1"."exdlaterality" AS "exdlaterality","Extent1"."xmin" AS "xmin","Extent2"."exmid" AS "exmid1","Extent2"."extid" AS "extid","Extent2"."anpcode" AS "anpcode","Extent2"."mdccode" AS "mdccode","Extent2"."exmlabel" AS "exmlabel","Extent2"."exmdescription" AS "exmdescription","Extent2"."exmduration" AS "exmduration","Extent2"."exmftcount" AS "exmftcount","Extent2"."exmindicator" AS "exmindicator","Extent2"."exmcolour" AS "exmcolour","Extent2"."datastatus" AS "datastatus","Extent2"."xmin" AS "xmin1","Extent4"."funid" AS "funid","Extent4"."svcid" AS "svcid","Extent4"."funlabel" AS "funlabel","Extent4"."funnumber" AS "funnumber","Extent4"."funtypes" AS "funtypes","Extent4"."funapplication" AS "funapplication","Extent4"."corid" AS "corid","Extent4"."datastatus" AS "datastatus1","Extent4"."tppid" AS "tppid","Extent4"."xmin" AS "xmin2", CASE WHEN ("Extent3"."exmid" IS NULL ) THEN (NULL) ELSE (1) END AS "C1"
FROM "acadmin"."tconfigexaminationdpi" AS "Extent1"
INNER JOIN "acadmin"."tconfigexaminations" AS "Extent2" ON "Extent1"."exmid"="Extent2"."exmid"
ORDER BY "Extent1"."exdlabel" ASC OFFSET 0 LIMIT 25
LEFT OUTER JOIN "acadmin"."tconfigexmfuncunits" AS "Extent3"
INNER JOIN "acadmin"."tfunctionalunits" AS "Extent4" ON "Extent3"."funid"="Extent4"."funid" ON "Extent1"."exmid"="Extent3"."exmid"
) AS "Project1"
ORDER BY "Project1"."exdlabel" ASC ,"Project1"."exdid" ASC ,"Project1"."exmid1" ASC ,"Project1"."C1" ASC

This one is very easily reproductible.
I hope it will be fixed soon.

What does support=&quot;FF&quot; mean? [ reply ]
By: Katsutoshi Kasahara on 2013-05-02 00:51
[forum:1020672]
Hello,

I use DbProviderFoctory with App.config for database connecting.

I read user's manual,
3.4 Using Npgsql with ProviderFactory
http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html

Example of App.config as below.
Anyone knows how support="FF" works ? and any values applicable?

---
<?xml version="1.0" encoding="iso-8859-1" ?>
<configuration>

<system.data>
<DbProviderFactories>

<add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />

</DbProviderFactories>
</system.data>

</configuration>
---

Thanks,
Katsutoshi

Entity Framework View missing column [ reply ]
By: Kristoffer None on 2013-05-07 12:38
[forum:1020674]
Hey there,

let me start by thanking you for providing such a great tool!

Unfortunately I ran into a Problem:

I am using Entity Framework to access my PostgreSql Database via an WCF-ODataService. The problem is the following when trying to access a view an exception is raised: {"ERROR: 42703: Column Extent1.C1 does not exist"}. Taking a closer look at the produced SQL it seems that it is invalid:

--- Outer Query
SELECT ... -- other defined columns
,CAST("Extent1"."Id" AS int4) AS "C1"
,CAST("Extent1"."Id_Grab" AS int4) AS "C2"
,CAST("Extent1"."Id_Parent" AS int4) AS "C3"
,CAST("Extent1"."Id_InstanceOf" AS int4) AS "C4"
,CAST("Extent1"."Id_File" AS int4) AS "C5"
,CAST("Extent1"."Id_GrabItemType" AS int4) AS "C6"
FROM (
SELECT
... -- all columns of the view
FROM "TestSchema"."MyView" "MyView"
) AS "Extent1"
WHERE 4 = (CAST("Extent1"."Id_Grab" AS int4)) -- 4 is a value that the resultset should be filtered to
ORDER BY
"Extent1"."C1" ASC OFFSET 5 LIMIT 5 -- <--- !!!! This is the problem,
-- the column C1 is not defined in the subquery, it gets defined on the outer query (by EF)
--- Outer Query

Could someone give some pointers how to solve this? Thank you very much for your time.

Best Wishes,
Kris

Can't load Npgsql driver with FluentNHibernate [ reply ]
By: Brent Douglas on 2013-05-05 12:25
[forum:1020673]
Hi guys,

I am pretty new to .net so please excuse any obvious oversights I am making. I am trying to run some unit tests for a class library I have and am getting the following exception:


<pre>
Test method xxxxx.core.tests.util.DatabaseTest.TestOpenSession threw exception:
FluentNHibernate.Cfg.FluentConfigurationException: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.

---> NHibernate.HibernateException: Could not create the driver from NHibernate.Driver.NpgsqlDriver, NHibernate, Version=3.3.1.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Configuration.ConfigurationErrorsException: Failed to find or load the registered .Net Framework Data Provider.
at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
at NHibernate.Driver.ReflectionBasedDriver..ctor(String providerInvariantName, String driverAssemblyName, String connectionTypeName, String commandTypeName)
at NHibernate.Driver.NpgsqlDriver..ctor()
--- End of inner exception stack trace ---
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, ref Boolean canBeCached, ref RuntimeMethodHandleInternal ctor, ref Boolean bNeedSecurityCheck)
at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, ref StackCrawlMark stackMark)
at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, ref StackCrawlMark stackMark)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at System.Activator.CreateInstance(Type type)
at NHibernate.Bytecode.ActivatorObjectsFactory.CreateInstance(Type type)
at NHibernate.Connection.ConnectionProvider.ConfigureDriver(IDictionary`2 settings)
--- End of inner exception stack trace ---
at NHibernate.Connection.ConnectionProvider.ConfigureDriver(IDictionary`2 settings)
at NHibernate.Connection.ConnectionProvider.Configure(IDictionary`2 settings)
at NHibernate.Connection.ConnectionProviderFactory.NewConnectionProvider(IDictionary`2 settings)
at NHibernate.Cfg.SettingsFactory.BuildSettings(IDictionary`2 properties)
at NHibernate.Cfg.Configuration.BuildSettings()
at NHibernate.Cfg.Configuration.BuildSessionFactory()
at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()
--- End of inner exception stack trace ---
at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()
at xxxxx.core.SessionFactory..ctor(String connectionString) in SessionFactory.cs: line 13
at xxxxx.core.tests.util.DatabaseTest.TestOpenSession() in DatabaseTest.cs: line 11
</pre>


Which, correct me if I am wrong, sounds like Npgsql.dll can't be found. The project came from the default VS2012 C# class library template and the test project came from the C# unit test project. Both are targeting .NET 4.5 and have no other changes (except lower case packages) from the default properties.

The test project has an app.config file with the following:


<pre>
<configuration>
<connectionStrings>
<add name="SomeDB" providerName="Npgsql" connectionString="Server=127.0.0.1;Port=5432;Database=xxxxxx;User Id=xxxxxx;Password=xxxxxx;"/>
</connectionStrings>
<system.data>
<DbProviderFactories>
<remove invariant="Npgsql"></remove>
<add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for Postgresql Server" support="FF" type="Npgsql.NpgsqlFactory,
Npgsql,
Version=2.0.12.0,
Culture=neutral,
PublicKeyToken=5d8b90d52f46fda7"/>
</DbProviderFactories>
</system.data>
</configuration>
</pre>


I am using postgresql 9.2 on windows 7 professional. Both my class library and test projects have references to the Npgsql driver and Mono.Security with Copy Local set to true. I have checked and both these dll's are being copied into the project/bin/Debug directories. The Npgsql copy I have came from nuget and the references are to the copies localed in ${PROJECT_ROOT}\packages\Npgsql.2.0.12.1\lib\net40\Npgsql.dll (and same Mono.Security). VS reports Npgsql's version to be 2.0.12.0.

I tried to load these to into the gac but when I did the copy of gacutil I have said it was built with .net 3.5 and could not load this version (I have v6.1 of the windows sdk installed).

Can anyone please help me with what could be wrong because I feel like I have tried everything google can suggest to me. If you need any additional info to help me please ask and I will be only too happy to oblige.

I have set a breakpoint in my test and the connection string is being loaded correctly so app.config file is being read as some stage. I don't really know how to debug library code in .Net though (any advice on that front would also be really appreciated btw).

Brent Douglas

Linq .cont() generates invalid SQL [ reply ]
By: Olivier MATROT on 2013-04-15 08:50
[forum:1020637]
I have a problem with the driver (2.0.12.0) that, in some cases, generates invalid SQL.
First, a query is composed, filters are applied dynamically via query continuation.
Second, rowcount is calculated (using .Count()) to obtain the total number of rows that will be returned. As you might have guessed already, the goal is to have paging support.
Third, the query, without the row count, is issued and works well.

I've been able to circumvent the problem to a specific restriction filter, and solved it by applying it earlier in the query continuation !? This is strange and this is why I suspect a provider bug.

The INVALID SQL is following :

SELECT "GroupBy1"."A1" AS "C1"
FROM
(
SELECT CAST (count(1) AS int4) AS "A1"
FROM
(
SELECT "Extent5"."rbkid" AS "rbkid" FROM "acaccount"."tmovement" AS "Extent1"
INNER JOIN "acaccount"."tbillinginformation" AS "Extent2" ON "Extent1"."bilid"="Extent2"."bilid"
INNER JOIN "acadmin"."tperformingphysicians" AS "Extent3" ON "Extent2"."physicianid"="Extent3"."ppyid"
LEFT OUTER JOIN "acadmin"."tressources" AS "Extent4" ON "Extent3"."resid"="Extent4"."resid"
LEFT OUTER JOIN "acaccount"."tpayment" AS "Extent5" ON "Extent1"."pytid"="Extent5"."pytid"
LEFT OUTER JOIN "acaccount"."tpaymentmode" AS "Extent6" ON "Extent5"."pmid"="Extent6"."pmid"
WHERE ("Extent1"."mvtstatus" IS NOT NULL ) AND (2!= (CAST ("Extent1"."mvtstatus" AS int4)))
) AS "Filter1"
LEFT OUTER JOIN "acadmin"."tretailbanks" AS "Extent7" ON "Filter1"."rbkid"="Extent7"."rbkid"
WHERE ((((("Filter1"."sitid"= (@p__linq__0))
AND ("Filter1"."svcid"= (@p__linq__1)))
AND ("Filter1"."funid"= (@p__linq__2)))
AND ("Filter1"."corid"= (@p__linq__3)))
AND ("Filter1"."mvtentrydate">= (@p__linq__4)))
AND ("Filter1"."mvtentrydate"<= (@p__linq__5))
) AS "GroupBy1"

THE VALID SQL is FOLLOWING :

SELECT "GroupBy1"."A1" AS "C1"
FROM
(
SELECT CAST (count(1) AS int4) AS "A1" FROM "acaccount"."tmovement" AS "Extent1"
INNER JOIN "acaccount"."tbillinginformation" AS "Extent2" ON "Extent1"."bilid"="Extent2"."bilid"
INNER JOIN "acadmin"."tperformingphysicians" AS "Extent3" ON "Extent2"."physicianid"="Extent3"."ppyid"
LEFT OUTER JOIN "acaccount"."tpayment" AS "Extent4" ON "Extent1"."pytid"="Extent4"."pytid"
LEFT OUTER JOIN "acaccount"."tpaymentmode" AS "Extent5" ON "Extent4"."pmid"="Extent5"."pmid"
LEFT OUTER JOIN "acadmin"."tretailbanks" AS "Extent6" ON "Extent4"."rbkid"="Extent6"."rbkid"
WHERE ((((("Extent1"."sitid"= (@p__linq__0)) AND ("Extent1"."svcid"= (@p__linq__1)))
AND ("Extent1"."funid"= (@p__linq__2))) AND ("Extent1"."corid"= (@p__linq__3)))
AND ("Extent1"."mvtentrydate">= (@p__linq__4))) AND ("Extent1"."mvtentrydate"<= (@p__linq__5))
) AS "GroupBy1"

Does anyone ever hit the same problem ?
TIA.

Problem with nant tests when building from sources [ reply ]
By: Maksym Boguk on 2013-03-14 23:22
[forum:1020585]
I run into problem with nant tests when tried build Npgsql2 from source.
nant tests show next error sequence:

build:

[csc] Compiling 10 files to '/home/mboguk/postgres/Npgsql2/src/build/nunit2tests/NpgsqlTests.dll'.
[csc] /home/mboguk/postgres/Npgsql2/testsuite/noninteractive/NUnit20/BaseClassTests.cs(31,7): error CS0246: The type or namespace name `NUnit' could not be found. Are you missing a using directive or an assembly reference?
[csc] /home/mboguk/postgres/Npgsql2/testsuite/noninteractive/NUnit20/CommandTests.cs(32,7): error CS0246: The type or namespace name `NUnit' could not be found. Are you missing a using directive or an assembly reference?
[csc] /home/mboguk/postgres/Npgsql2/testsuite/noninteractive/NUnit20/CommandTests.cs(49,6): error CS0246: The type or namespace name `TestFixture' could not be found. Are you missing a using directive or an assembly reference?
[csc] /home/mboguk/postgres/Npgsql2/testsuite/noninteractive/NUnit20/CommandTests.cs(49,6): error CS0246: The type or namespace name `TestFixtureAttribute' could not be found. Are you missing a using directive or an assembly reference?

and so on...

It seems I missing some installed files or libraries.
Any idea whow I can fix it?


PS: nant version
NAnt 0.91 (Build 0.91.3881.0; alpha2; 8/17/2010)

Adapter.Fill fails on ARM (BeagleBone) [ reply ]
By: mickey feldman on 2013-04-10 21:32
[forum:1020627]
I have an application that I am mostly developing on a desktop PC but with the intention that it run on a BeagleBone (ARM architchture). Code that run on the x86 mono fails on the armel, throwing an exception when it hits Adapter.Fill. In both cases I'm running mono 2.10.8.1 on a Ubuntu 12.04 build, npgsql is 2.0.12.0.

I did find another ARM related issue with mono that has to do with the formatting of DateTime (not sql datetime, not npgsql related), and I'm not surprised if arm support lags x86.

Is this a known problem, and is there a fix or workaround?

Here's my stacktrace:

2013/04/10 13:30:49 System.TypeInitializationException() An exception was thrown by the type initializer for System.Data.SqlTypes.SqlDecimal
at System.Data.DataColumn..ctor (System.String columnName, System.Type dataType, System.String expr, MappingType type) [0x00000] in <filename unknown>:0
at System.Data.DataColumn..ctor (System.String columnName, System.Type dataType) [0x00000] in <filename unknown>:0
at System.Data.DataColumnCollection.Add (System.String columnName, System.Type type) [0x00000] in <filename unknown>:0
at Npgsql.NpgsqlDataReader.GetResultsetSchema () [0x00000] in <filename unknown>:0
at Npgsql.NpgsqlDataReader.GetSchemaTable () [0x00000] in <filename unknown>:0
at System.Data.Common.DataAdapter.BuildSchema (IDataReader reader, System.Data.DataTable table, SchemaType schemaType, MissingSchemaAction missingSchAction, MissingMappingAction missingMapAction, System.Data.Common.DataTableMappingCollection dtMapping) [0x00000] in <filename unknown>:0
at System.Data.Common.DataAdapter.BuildSchema (IDataReader reader, System.Data.DataTable table, SchemaType schemaType) [0x00000] in <filename unknown>:0
at System.Data.Common.DataAdapter.FillTable (System.Data.DataTable dataTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, System.Int32& counter) [0x00000] in <filename unknown>:0
at System.Data.Common.DataAdapter.FillInternal (System.Data.DataSet dataSet, System.String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) [0x00000] in <filename unknown>:0
at System.Data.Common.DataAdapter.Fill (System.Data.DataSet dataSet, System.String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) [0x00000] in <filename unknown>:0
at System.Data.Common.DbDataAdapter.Fill (System.Data.DataSet dataSet, Int32 startRecord, Int32 maxRecords, System.String srcTable, IDbCommand command, CommandBehavior behavior) [0x00000] in <filename unknown>:0
at System.Data.Common.DbDataAdapter.Fill (System.Data.DataSet dataSet) [0x00000] in <filename unknown>:0
at Vigil.Public.Database.SQLDatabase.GetDataTable (System.String strSQL) [0x00000] in <filename unknown>:0

Connection pool : Timeout after some time [ reply ]
By: Olivier MATROT on 2013-03-13 18:58
[forum:1020581]
Hello,

I'm using Npgsql 2.0.12.0 in the context of Entity Framework in a WCF service hosted in IIS.

With connection pooling enabled at the connection string, everything works fine for several hours and suddenly, it is impossible to get a connection from the pool :

System.Data.EntityException: The underlying provider failed on Open. ---> System.Exception: Timeout while getting a connection from pool.
at Npgsql.NpgsqlConnectorPool.RequestPooledConnector(NpgsqlConnection Connection)
at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection)
at Npgsql.NpgsqlConnection.Open()
at EFProviderWrapperToolkit.DbConnectionWrapper.Open()
at EFProviderWrapperToolkit.DbConnectionWrapper.Open()
at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
--- End of inner exception stack trace ---
at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
at System.Data.EntityClient.EntityConnection.Open()
at System.Data.Objects.ObjectContext.EnsureConnection()
at System.Data.Objects.ObjectContext.ExecuteStoreCommand(String commandText, Object[] parameters)
at Accel.Is.Data.EF.AccelIsExtendedContext.SetApplicationName(String clientAppName, String login)
at Accel.Is.Data.Service.BillingService.GetBill(Int32 Id, EagerLoadingRules loadDependencies)

What are the known reasons for a timeout getting a connection ?
How could I help diagnose the problem ?

Thanks in advance.

WCF, Entity Framework, Connection pool, and distributed (PREPARED) transactions [ reply ]
By: Olivier MATROT on 2013-03-17 19:21
[forum:1020591]
Hi,

It seems that there is a problem with connection pooling and TransactionScope.

In a WCF service, a connection that is enlisted in a transaction (BeginTransaction() has been called) should not be put back in the pool, because it will be used by another thread and you can't Prepare and Commit or Rollback the transaction.

I also have done some tests without connection pooling and it also fails.

PREPARE TRANSACTION does not return an error but the server log says that there is no current transaction (Begin has not been called)

Because COMMIT PREPARED is called from a Npgsql owned thread, it throws an exception and kills the WCF Service.

I will try to give you traces in order to understand the problem.

Stay tuned.

2.0.12 CommandBuilder/DataAdapter problem [ reply ]
By: Nenad Konstantinovik on 2012-11-18 22:29
[forum:1020368]
I switched from 2.0.11 to 2.0.12 and started to receive errors "Update requires a valid UpdateCommand when passed DataRow collection with modified rows", and likewise for insert and delete operations. I checked the CommandBuilder, it's generating the commands, but when trying to execute the DataAdapter.Update command, I get the error. Is something changed in relation to this in the new version?
Thank you

Resource Manager Guid [ reply ]
By: Olivier MATROT on 2013-03-13 16:48
[forum:1020579]
Hello,

I'm using Npgsql in the context of Entity Framework in a WCF service Hosted in IIS. I have several services hosted in the same process (one per appdomain I think).

I have an issue with the System.Transactions implementation.
I have the following error :

System.Data.EntityException: The underlying provider failed on Open. ---> System.Runtime.InteropServices.COMException: A resource manager with the same identifier is already registered with the specified transaction coordinator. (Exception from HRESULT: 0x8004D102)

Server stack trace:
at System.Transactions.Oletx.IDtcProxyShimFactory.CreateResourceManager(Guid resourceManagerIdentifier, IntPtr managedIdentifier, IResourceManagerShim& resourceManagerShim)
at System.Transactions.Oletx.OletxResourceManager.get_ResourceManagerShim()
at System.Transactions.Oletx.OletxResourceManager.EnlistDurable(OletxTransaction oletxTransaction, Boolean canDoSinglePhase, IEnlistmentNotificationInternal enlistmentNotification, EnlistmentOptions enlistmentOptions)
at System.Transactions.Oletx.OletxTransaction.EnlistDurable(Guid resourceManagerIdentifier, ISinglePhaseNotificationInternal singlePhaseNotification, Boolean canDoSinglePhase, EnlistmentOptions enlistmentOptions)
at System.Transactions.TransactionStatePromotedBase.EnlistDurable(InternalTransaction tx, Guid resourceManagerIdentifier, ISinglePhaseNotification enlistmentNotification, EnlistmentOptions enlistmentOptions, Transaction atomicTransaction)
at System.Transactions.Transaction.EnlistDurable(Guid resourceManagerIdentifier, ISinglePhaseNotification singlePhaseNotification, EnlistmentOptions enlistmentOptions)
at Npgsql.NpgsqlResourceManager.DurableResourceManager.Enlist(Transaction tx)
at Npgsql.NpgsqlResourceManager.DurableResourceManager.Enlist(Byte[] token)
at Npgsql.NpgsqlResourceManager.Enlist(INpgsqlTransactionCallbacks callbacks, Byte[] txToken)
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Npgsql.INpgsqlResourceManager.Enlist(INpgsqlTransactionCallbacks transactionCallbacks, Byte[] txToken)
at Npgsql.NpgsqlPromotableSinglePhaseNotification.Enlist(Transaction tx)
at Npgsql.NpgsqlConnection.Open()
at EFProviderWrapperToolkit.DbConnectionWrapper.Open()
at EFProviderWrapperToolkit.DbConnectionWrapper.Open()
at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
--- End of inner exception stack trace ---
at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
at System.Data.EntityClient.EntityConnection.Open()
at System.Data.Objects.ObjectContext.EnsureConnection()
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
at Accel.Is.Data.Service.StudyService.UpdateEnhancedTrackableCollectionOfTechnicalpackage(EnhancedTrackableCollection`1 listTechnicalpackage)
at SyncInvokeUpdateEnhancedTrackableCollectionOfTechnicalpackage(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

From what I understand, this is because The resource manager is using a static guid.
The documentation states that we should provide "A unique identifier for a resource manager, which should persist across resource manager failure or reboot."

Well I understand that this Guid should be unique given an appdomain. The current design prevents the enlistment in transactions if there is more than one process/appdomain on the current machine using Npgsql.

I will try to use a new generated Guid.

VisualStudio NpgsqlException [ reply ]
By: Lucas Phillip on 2013-01-04 15:15
[forum:1020432]
Hi there,

We started testing with Npgsql to migrate our database from sqlserver.
So far we got everything working just fine, except for one little thing. Our code works fine if executed outside from Visual Studio. But if it is executed from VS, we get a "ERROR: 3F000: schema \"dbo\" does not exist" exception the first time a connection is made. If we continue the execution, then we do not get the exception anymore and every query works.

Can anyone enlight this problem for us? We could not find anything similar on the web.

Thanks,
Lucas

Listen/Notify question [ reply ]
By: Michael Dillon on 2012-09-20 18:13
[forum:1020138]
I'm using npgsql version 2.0.12.0 and .NET 4

I'm have a simple test application that uses npgsql to listen for channel notifications. I'm setting the SyncNotification flag to true. The source is here http://pastebin.com/k7kP7RSQ

Using PgAdmin, I'll send all these notifications at once:

notify my_channel, 'starting';

BEGIN;
notify my_channel, 'ping 1';
notify my_channel, 'ping 2';
COMMIT;

notify my_channel, 'test 1';
notify my_channel, 'test 2';

notify my_channel, 'done';

The application will only print the initial "starting" message.

Once I send an additional message, the application will print all the previous messages.

Using Wireshark, I've verified that our server sends each notification as I would expect, but npgsql seems to queue all the messages. If I send individual messages, things also work as expected (except for the messages done in a transaction).

In short, it seems like the npgsql driver is holding all the messages until it's 'nudged' by a new incoming notification. How can I prevent this behavior?

Thanks,

Mike

ERROR: 42704 hides underlying error [ reply ]
By: Nathan Alden on 2013-02-18 22:37
[forum:1020555]
First, some system info:

Windows 7 x64
PostgreSQL 9.2.3, compiled by Visual C++ build 1600, 64-bit
Npgsql 2.0.12.0
C#/.NET 4.5

I am using TransactionScope within the context of integration tests that call out to my PostgreSQL DB. The SQL I'm running executes successfully and then the transaction rolls back, restoring the DB to its original state.

Consider the following code:

Assert.DoesNotThrow(() =>
{
using (new TransactionScope())
{
using (var connection = new NpgsqlConnection("Server=localhost; Database=tank; User ID=tank; Password=tank; Enlist=true;"))
{
connection.Open();

const string sql = @"select 1 / 0;";
var command = new NpgsqlCommand(sql, connection);

command.ExecuteNonQuery();
}
}
});

If I debug that code, the debugger breaks correctly on the ExecuteNonQuery line with this error:

ERROR: 22012: division by zero

If I continue execution, I receive another error (stack trace truncated for brevity):

Npgsql.NpgsqlException was unhandled by user code
Message=ERROR: 42704: prepared transaction with identifier "a985734b-52d3-4924-bfa5-74ee170ac081" does not exist
Source=Npgsql
StackTrace:
at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs:line 853
at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1175
at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1191
at Npgsql.ForwardsOnlyDataReader.NextResult() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1377
at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1040
at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 611
at Npgsql.NpgsqlCommand.ExecuteBlind() in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 499
at Npgsql.NpgsqlTransactionCallbacks.RollbackTransaction() in C:\projects\Npgsql2\src\Npgsql\NpgsqlTransactionCallbacks.cs:line 128
at Npgsql.NpgsqlPromotableSinglePhaseNotification.Rollback(SinglePhaseEnlistment singlePhaseEnlistment) in C:\projects\Npgsql2\src\Npgsql\NpgsqlPromotableSinglePhaseNotification.cs:line 128
at System.Transactions.DurableEnlistmentAborting.EnterState(InternalEnlistment enlistment)
at System.Transactions.DurableEnlistmentActive.InternalAborted(InternalEnlistment enlistment)
at System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)
at System.Transactions.TransactionStateActive.Rollback(InternalTransaction tx, Exception e)
at System.Transactions.Transaction.Rollback()
at System.Transactions.TransactionScope.InternalDispose()
at System.Transactions.TransactionScope.Dispose()
...
InnerException:

What I am assuming is happening here is the transaction is being rolled back upon failure of the original command. Then, TransactionScope.Dispose() tries to rollback the same transaction again, which fails because the transaction has already been rolled back. This behavior is problematic because the TransactionScope.Dispose() logic appears to hide or eat the original exception (the more meaningful one). Running my test with ReSharper outputs this:

Npgsql.NpgsqlException : ERROR: 42704: prepared transaction with identifier "c99ffc70-4862-48d4-b120-275bc3638710" does not exist
at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs: line 853
at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs: line 1175
at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs: line 1191
at Npgsql.ForwardsOnlyDataReader.NextResult() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs: line 1377
at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs: line 1041
at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs: line 613
at Npgsql.NpgsqlCommand.ExecuteBlind() in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs: line 499
at Npgsql.NpgsqlPromotableSinglePhaseNotification.Rollback(SinglePhaseEnlistment singlePhaseEnlistment) in C:\projects\Npgsql2\src\Npgsql\NpgsqlPromotableSinglePhaseNotification.cs: line 128
at System.Transactions.DurableEnlistmentAborting.EnterState(InternalEnlistment enlistment)
at System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)
at System.Transactions.Transaction.Rollback()
at System.Transactions.TransactionScope.InternalDispose()
at System.Transactions.TransactionScope.Dispose()
...

There is no hint of the original exception.

Shouldn't Npgsql's use of TransactionScope track that the internal transaction was rolled back and make TransactionScope.Dispose() a no-op? That would allow the original exception to bubble up. I realize I could add try/catch blocks within each using block, but that's very tedious and adds a lot of boilerplate code that should be unnecessary.

command parameters with CommandType.StoredProcedure [ reply ]
By: Tim Romano on 2013-01-23 11:25
[forum:1020495]
I am just installing PostgreSQL now and reading the .NET provider documentation. I've been working with Microsoft SQL Server since SQL Server 2000, but we're looking into PostgreSQL because of its adherence to the ISO 8601 3.5.2 standard.

My question relates to the NpgsqlCommand.Parameters collection, when CommandType = CommandType.StoredProcedure, and whether the behavior is the same as it is with SQL Server, where command parameters are matched *by name* to the parameter in the stored procedure signature. Are the NpgsqlCommand.Parameters also matched by name, or are they matched to the arguments of a function on the server by the order in which they appear in the function signature?

Thanks

.Net 4.5/EF 5 Code First Timespan Problem [ reply ]
By: Jon Sutter on 2012-12-05 23:27
[forum:1020402]
We are experimenting with PostgreSQL providers and .Net 4.5/EF 5 Code First with the idea of converting our code base from MSSQL only to support both MSSQL and PostgreSQL databases. I used the "Reverse Engineer Code First" to generate the classes and mapping and most operations work well with the same code regardless of the data source.

One problem with the Npgsql provider - if any of the poco classes are declared with System.Timespan (which is the type the Reverse Engineer uses for database "time" fields), then when a linq statement is executed against any of the classes, the EF mapping fails with an exception "The PrimitiveType is not a DateTime type."

Note that this problem does not happen with the Devart provider and it also does not appear to matter what the data type the field is in the database.

The part of the stack trace appropriate to npgsql is "Npgsql.NpgsqlProviderManifest.GetStoreType(TypeUsage edmType)". Looking at the source of NpgsqlProviderManifest.GetStoreType(), it appears that the "case PrimitiveTypeKind.Time:" calls CreateDateTimeTypeUsage() when it should call CreateTimeTypeUsage().

Entity Framework with access to Large Object [ reply ]
By: Andreas G on 2012-11-21 16:28
[forum:1020373]
I think, that no functionality is generated for large objects.

Is it right? Do I have to implement some code in the generated files, or have I missed something in the generated code?

Npgsql on .NET 4.5 with EF [ reply ]
By: Geoff Smith on 2012-06-20 12:54
[forum:1016945]
Hi, Has anyone had any success running npgsql on the 4.5 RC? Something is generating bad SQL when I use Npgsql through entity framework. I wasn't expecting this to be supported, just curious :)

Newer Messages  Older Messages
Powered By FusionForge