SCM

Forum: help

Monitor Forum | Start New Thread Start New Thread
Large SQL query generated with incorrect identifier [ reply ]
By: Alex LastName on 2013-07-03 23:14
[forum:1020751]

sqlerror.txt (59) 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.

Error closing connection: cannot execute UNLISTEN during recovery (hot standby) [ reply ]
By: Luke Hill on 2013-05-27 23:04
[forum:1020711]
We're using WAL file shipping to replicate data between a primary and a secondary PostgreSQL server.

After executing a read-only query via Npgsql to the secondary PostgreSQL server (running in "hot standby" mode), attempting to dispose of the connection causes the following error:

Stack Trace:

[NpgsqlException (0x80004005): ERROR: 25006: cannot execute UNLISTEN during recovery]
Npgsql.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs:842
Npgsql.NpgsqlState.IterateThroughAllResponses(IEnumerable`1 ienum) in C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs:319
Npgsql.NpgsqlConnectorPool.UngetPooledConnector(NpgsqlConnection Connection, NpgsqlConnector Connector) in C:\projects\Npgsql2\src\Npgsql\NpgsqlConnectorPool.cs:567
Npgsql.NpgsqlConnectorPool.ReleasePooledConnector(NpgsqlConnection Connection, NpgsqlConnector Connector) in C:\projects\Npgsql2\src\Npgsql\NpgsqlConnectorPool.cs:282
Npgsql.NpgsqlConnection.Close() in C:\projects\Npgsql2\src\Npgsql\NpgsqlConnection.cs:616
NHibernate.Connection.ConnectionProvider.CloseConnection(IDbConnection conn) +95

Our postgresql.conf file on the secondary server contains:
hot_standby = on

Our postgresql.conf file on the primary server contains:
wal_level = hot_standby
archive_mode = on

Is there any way to configure Npgsql to skip the UNLISTEN statement on closing/disposing a connection?
If not, can support for read-only queries to hot standby servers be built-in to the next Npgsql release?

Currently, we're only executing a single query on the secondary server (to monitor the replication status), so are catching and silencing the NpgsqlException with Code == 25006, however we intend to use the secondary server for far more widespread queries, so this workaround is less than ideal.

(Using Npgsql.dll version 2.0.11.0)

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 (9) 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 :)

Performance issue when using NpgsqlParameters [ reply ]
By: Ben Schalley on 2012-11-16 16:25
[forum:1020354]
I'm having an issue with a (complex) query, which when executed in pgAdmin takes about 75 milliseconds but when executed from code takes about 10 seconds. It seems to be related to the use of NpgsqlParameters, because when debugging and changing the commandtext to the exact query used in pgAdmin and clearing the parameter array it is as fast as in pgAdmin.

It's the line "oDataAdapter.Fill(oDataTable)" that takes so long to execute.
Might this be related to the performance issue when prepared statements?

Any help would be much appreciated.

Regards,
Ben

PS: The query and my code are bellow.

The query:

SELECT DISTINCT gf.inv_nr, gf.ep, adr_gemeentenaam, bp.naam, blok, rij, nr, naam_grafteken, COALESCE(NULLIF(' ' || usr.naam,' '),'') || COALESCE(NULLIF(' ' || usr.voornaam,' '),'') AS gewijzigd_door, gewijzigd, foto_algemeen
FROM ((((((((graf_fiche AS gf INNER JOIN begraafplaatsen AS bp ON (gf.niscodegem = bp.niscodegem AND gf.code = bp.code))
LEFT JOIN begravenen AS b ON (gf.inv_nr = b.graf_fiche_inv_nr AND gf.ep = b.graf_fiche_ep))
LEFT JOIN graf_fotos AS gfo ON (gf.inv_nr = gfo.graf_fiche_inv_nr AND gf.ep = gfo.graf_fiche_ep))
LEFT JOIN graf_fiche_documenten AS gfd ON (gf.inv_nr = gfd.graf_fiche_inv_nr AND gf.ep = gfd.graf_fiche_ep))
LEFT JOIN (graf_fiche_fotograaf AS gff_1
INNER JOIN persoonlijkheden AS p1 ON gff_1.persoonlijkheid = p1.id) ON (gf.inv_nr = gff_1.graf_fiche_inv_nr AND gf.ep = gff_1.graf_fiche_ep))
LEFT JOIN (graf_fiche_architectuur AS gfa_1
LEFT JOIN (architectuur_ontwerper_uitvoerder AS aou_1
INNER JOIN persoonlijkheden AS p2 ON aou_1.persoonlijkheid = p2.id
) ON gfa_1.id = aou_1.graf_fiche_architectuur
) ON (gf.inv_nr = gfa_1.graf_fiche_inv_nr AND gf.ep = gfa_1.graf_fiche_ep))
LEFT JOIN (graf_fiche_sculptuur_ingewerkt AS gfsi_1
LEFT JOIN (sculptuur_ingewerkt_ontwerper_uitvoerder AS siou_1
INNER JOIN persoonlijkheden AS p3 ON siou_1.persoonlijkheid = p3.id
) ON gfsi_1.id = siou_1.graf_fiche_sculptuur_ingewerkt
) ON (gf.inv_nr = gfsi_1.graf_fiche_inv_nr AND gf.ep = gfsi_1.graf_fiche_ep))
LEFT JOIN (graf_fiche_sculptuur_vrijstaand AS gfsv_1
LEFT JOIN (sculptuur_vrijstaand_ontwerper_uitvoerder AS svou_1
INNER JOIN persoonlijkheden AS p4 ON svou_1.persoonlijkheid = p4.id
) ON gfsv_1.id = svou_1.graf_fiche_sculptuur_vrijstaand
) ON (gf.inv_nr = gfsv_1.graf_fiche_inv_nr AND gf.ep = gfsv_1.graf_fiche_ep))
LEFT JOIN gebruikers AS usr ON (gf.invuller = usr.id)
WHERE (
(b.naam ILIKE :Name AND b.voornaam ILIKE :Firstname)
OR (gfo.fotograaf_naam ILIKE :Name AND gfo.fotograaf_voornaam ILIKE :Firstname)
OR (gfd.auteur_naam ILIKE :Name AND gfd.auteur_voornaam ILIKE :Firstname)
OR (gf.naam_grafteken ILIKE :Name AND gf.naam_grafteken ILIKE :Firstname)
OR (p1.naam ILIKE :Name AND p1.voornaam ILIKE :Firstname)
OR (p2.naam ILIKE :Name AND p2.voornaam ILIKE :Firstname)
OR (p3.naam ILIKE :Name AND p3.voornaam ILIKE :Firstname)
OR (p4.naam ILIKE :Name AND p4.voornaam ILIKE :Firstname)
)
AND gf.ep = :GraveSheetStatus;


The code:

Public Function Execute(query As String, filterName As String, filterFirstname As String, filterStatus As String) As DataTable
Dim params As New List(Of NpgsqlParameter)
params.Add(DatabaseHelper.CreateParameter("Name", NpgsqlTypes.NpgsqlDbType.Varchar, 102, filterName))
params.Add(DatabaseHelper.CreateParameter("Firstname", NpgsqlTypes.NpgsqlDbType.Varchar, 102, filterFirstname))
params.Add(DatabaseHelper.CreateParameter("GraveSheetStatus", NpgsqlTypes.NpgsqlDbType.Varchar, filterStatus))

return ExecuteTable(CommandType.Text, query, params.ToArray)
End Sub

Public Function ExecuteTable(ByVal cmdType As CommandType, ByVal cmdText As String, Optional ByVal cmdParms As NpgsqlParameter() = Nothing) As DataTable
Using connection As NpgsqlConnection = Me.OpenConnection()
Using cmd As NpgsqlCommand = New NpgsqlCommand
Using oDataAdapter As New NpgsqlDataAdapter
Dim oDataTable As New DataTable
Try
PrepareCommand(cmd, connection, cmdType, cmdText, cmdParms)
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataTable)
cmd.Parameters.Clear()
Return oDataTable
Catch ex As NpgsqlException
Me.LogError(vErrorLog, "ExecuteTable(CommandType, String, [NpgsqlParameter()])", ex)
Throw New Exception("SQL Exception : " & ex.Message, ex)
Catch exx As Exception
Me.LogError(vErrorLog, "ExecuteTable(CommandType, String, [NpgsqlParameter()])", exx)
Throw New Exception("ExecuteTable Exception :", exx)
End Try
End Using
End Using
End Using
End Function

Public Function PrepareCommand(ByRef cmd As NpgsqlCommand, ByRef conn As NpgsqlConnection, ByRef cmdType As CommandType, ByRef cmdText As String, ByRef cmdParms As NpgsqlParameter()) As Boolean
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If
Try
cmd.Connection = conn
cmd.CommandText = cmdText
cmd.Parameters.Clear()
cmd.CommandType = cmdType
If Not IsNothing(cmdParms) Then
Dim param As NpgsqlParameter
For Each param In cmdParms
cmd.Parameters.Add(param)
Next
End If
Me.LogSqlStatement(Me.vSQLLog, conn, Nothing, cmdType, cmdText, cmdParms)
Return True
Catch ex As NpgsqlException
Me.LogError(vErrorLog, "PrepareCommand(NpgsqlCommand, NpgsqlConnection, CommandType, String, [NpgsqlParameter()])", ex)
Throw ex
Catch exx As Exception
Me.LogError(vErrorLog, "PrepareCommand(NpgsqlCommand, NpgsqlConnection, CommandType, String, [NpgsqlParameter()])", exx)
Throw exx
End Try
Return False
End Function

MigrationSqlGenerator [ reply ]
By: C Liu on 2012-11-14 16:25
[forum:1020351]
I'm currently trying to use EF auto DB migration for my app. That requires a MigrationSqlGenerator. Looks like the class is not implemented in Npgsql. Do I have to create one myself or I just didn't find it? Thanks!

Error 55000 caused by currval() in EF [ reply ]
By: Christian Jacques on 2012-10-17 00:09
[forum:1020224]
I am using Entity Framework with Npgsql2.0.11.94-bin-ms.net4.0 with Postgres 9.1.

When I call EF's SaveChanges() to push the changes I made to my Object Context to the database I get an exception with the following message:

-----
ERROR: 55000: currval of sequence \"Stations01_StationID_seq1\" is not yet defined in this session
-----

The exception also shows the SQL statement that caused the issue (scroll down). I understand (correct me if I am wrong) that the use of currval() is the culprit because nextval() has not previously been called since the session was opened. Aside from that the SQL statement looks fine as the only change I had made was to add a new object to my context expecting Npgsql to work out the details for the bigserial StationID column. Further down I also show the stack trace leading to the exception.

Is there something special I need to do in my code to cause Npgsql to issue an SQL statement that works?

Thanks

--SQL STATEMENT--
INSERT INTO "public"."WeatherStations"("PrimaryId","Name","Country","Elevation","StartDate","EndDate","Timezone","WarningPolygonID","Province","Type","Notes","Latitude","Longitude","SecondaryID1","SecondaryID2","SecondaryID3","SecondaryID4","SecondaryID5") VALUES ('CXAF','Abee','Canada',cast(664 as float4),TIMESTAMP '2012-10-16T00:00:00.0000000Z',NULL,'America/Edmonton','076200','AB','observed',NULL,cast(54.2833333333333 as float8),cast(-112.966666666667 as float8),'71285','3010010',NULL,NULL,NULL);SELECT currval(pg_get_serial_sequence('"public"."WeatherStations"', 'StationID')) AS "StationID"
-------

--STACK TRACE--
at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()
at Npgsql.ForwardsOnlyDataReader.GetNextRow(Boolean clearPending)
at Npgsql.ForwardsOnlyDataReader.Read()
at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Objects.ObjectContext.SaveChanges()

where can I find the latest Npgsql test set [ reply ]
By: fu cl on 2012-10-08 14:08
[forum:1020202]
npgsql test set in Npgsql Release 2.11.94 is for old version, then don't run under postgres 9.1.5. How can I get the newest npgsql test set for postgres 9.1.5 ?

2.0.12.0 on Mono 2.11.4 (4.5) [ reply ]
By: jian zhou on 2012-10-06 03:17
[forum:1020197]
I am trying to see if the 2.0.12.0 can work with the Mono 2.11.4 (4.5 -- since it has EntityFramework now). Somehow I always get stuck on System.Xml.Schema.XmlSchemaElement.CheckRecursion(...) and got a stack overflow.

I am trying the modified version of EFQuerySample.zip.

Thanks in advance.

Npgsql2 and NauckIT.PostgreSQLProvider [ reply ]
By: Lee Ibbotson on 2009-03-17 11:29
[forum:1004734]
Has anyone else encountered problems with the lastest Npgsql2 and/or Daniel Nauck's NauckIT.PostgreSQLProvider?

I have downloaded both, but when I use the <asp:login> control I get a syntax error "Error 42601: Syntax error at or near \"(\""

Here is the npgsql log:

17/03/2009 10:26:41 4972 Debug Get NpgsqlParameterCollection.Count
17/03/2009 10:26:41 4972 Debug Entering NpgsqlReadyState.Parse()
17/03/2009 10:26:41 4972 Debug Entering PGUtil.WriteString()
17/03/2009 10:26:41 4972 Debug String written: npgsqlplan1.
17/03/2009 10:26:41 4972 Debug Entering PGUtil.WriteString()
17/03/2009 10:26:41 4972 Debug String written: SELECT "Password", "IsApproved" FROM "Users" WHERE "Username" = $1::varchar AND "ApplicationName" = $2::varchar AND "IsLockedOut" = $3::bool(255)(255).
17/03/2009 10:26:41 4972 Debug Entering NpgsqlReadyState.Flush()
17/03/2009 10:26:41 4972 Debug Entering NpgsqlState.ProcessBackendResponses()
17/03/2009 10:26:41 4972 Debug Entering PGUtil.ReadString()
17/03/2009 10:26:41 4972 Debug Get NpgsqlEventLog.LogLevel
17/03/2009 10:26:41 4972 Debug String read: ERROR.
17/03/2009 10:26:41 4972 Debug Entering PGUtil.ReadString()
17/03/2009 10:26:41 4972 Debug Get NpgsqlEventLog.LogLevel
17/03/2009 10:26:41 4972 Debug String read: 42601.
17/03/2009 10:26:41 4972 Debug Entering PGUtil.ReadString()
17/03/2009 10:26:41 4972 Debug Get NpgsqlEventLog.LogLevel
17/03/2009 10:26:41 4972 Debug String read: syntax error at or near "(".
17/03/2009 10:26:41 4972 Debug Entering PGUtil.ReadString()
17/03/2009 10:26:41 4972 Debug Get NpgsqlEventLog.LogLevel
17/03/2009 10:26:41 4972 Debug String read: 146.
17/03/2009 10:26:41 4972 Debug Entering PGUtil.ReadString()
17/03/2009 10:26:41 4972 Debug Get NpgsqlEventLog.LogLevel
17/03/2009 10:26:41 4972 Debug String read: scan.l.
17/03/2009 10:26:41 4972 Debug Entering PGUtil.ReadString()
17/03/2009 10:26:41 4972 Debug Get NpgsqlEventLog.LogLevel
17/03/2009 10:26:41 4972 Debug String read: 807.
17/03/2009 10:26:41 4972 Debug Entering PGUtil.ReadString()
17/03/2009 10:26:41 4972 Debug Get NpgsqlEventLog.LogLevel
17/03/2009 10:26:41 4972 Debug String read: base_yyerror.
17/03/2009 10:26:41 4972 Debug ErrorResponse message from Server: syntax error at or near "(".
17/03/2009 10:26:41 4972 Normal An NpgsqlException occured: ERROR: 42601: syntax error at or near "(".
17/03/2009 10:26:41 4972 Debug Entering NpgsqlReadyState.Sync()

Any pointers would be appreciated.

Thanks

npgsql 2.0.12 Commandbuilder date problem [ reply ]
By: David Jaspers on 2012-09-19 11:35
[forum:1020126]
Hello,

I am testing to finally migrate our software from npgsql 1 to npgsql 2.0.12.0
One problem occurs with the NpgsqlCommandBuilder with:
var cmb = new NpgsqlCommandBuilder(da as NpgsqlDataAdapter);
da.UpdateCommand = cmb.GetUpdateCommand();

This works fine, but not for database columns of type 'date'.
The query sent to the backend is for example:
UPDATE "dbtest"."tabletest" SET columnoftypeint = 4,columnoftypedate=((E'2012-09-19 11:04:40.889517')::timestamp) WHERE columnoftypeint = 4 AND ("columnoftypedate " = ((E'2012-09-19 11:04:40.889517')::timestamp))

This is a valid query but will also return "No rows were affected exception".
The reason is the filter AND ("columnoftypedate " = ((E'2012-09-19 11:04:40.889517')::timestamp)) is never true, because in the database is the value '2012-09-19' for the date column.

I see 2 solutions:
1. The commandbuilder will generate this:
AND ("columnoftypedate " = ((E'2012-09-19 11:04:40.889517')::date)) instead of ((E'2012-09-19 11:04:40.889517')::timestamp))

2.
Or the commandbuilder generates this:
AND ("columnoftypedate " = ((E'2012-09-19):: timestamp)) instead of ((E'2012-09-19 11:04:40.889517')::timestamp))

What do you think or am I missing something here?

regards,
David

Npgsql 2.0.12.0 and DDEX [ reply ]
By: d blank on 2012-09-19 09:29
[forum:1020125]
Hi all
I am trying to generate an entity framework model from a PostgreSQL database, but Visual Studio (version 2012 in a .NET 4.5 project on a x64 Windows 7) won't let me choose a PostgreSQL data source (I have added Npgsql as DbProvider in Machine.config and app.config).
I am able to generate the model from the commandline with EdmGen, but it would be nice to do it from Visual Studio.
I have google the problem and am I correct in saying that it can't be done because Npgsql 2.0.12.0 does not support DDEX? Or am I just doing it wrong?

Best regards
/dblank

NOTICE breaks CommandTimeout Support [ reply ]
By: Olivier MATROT on 2012-09-14 09:18
[forum:1020092]
When issuing a SELECT query using NpgsqlDataAdapter, the CommandTimeout associated with the SelectCommand is not used, if the server raises a notice during this query. The query waits for the server to respond indefinitely.

Steps to reproduce the problem :
1) Use a function like this one :

CREATE OR REPLACE FUNCTION public.longfunction ()
RETURNS VOID AS
$BODY$
DECLARE
BEGIN
RAISE NOTICE 'Timeout !';
PERFORM pg_sleep(30);
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.longfunction ()
OWNER TO postgres;

2) Call this function and experience the problem :

String cnstr = "DATABASE=" + _DatabaseName + ";SERVER=" + _Server + ";PORT=" + _Port +
";UID="+ _User + ";PWD=" + _Password + ";SyncNotification=True;Pooling=False;CommandTimeout=20";
NpgsqlConnection db_Connection = new NpgsqlConnection(cnstr);
NpgsqlCommand cmdNQ=new NpgsqlCommand(@"SELECT * FROM public.longfunction()", db_Connection);
System.Data.DataSet ds = new System.Data.DataSet();
NpgsqlDataAdapter da=new NpgsqlDataAdapter(cmdNQ);
try
{
da.Fill(ds);
}
catch (NpgsqlException e)
{
// Never goes here :-(
}

Of course the immediate solution is to remove the notice from the function, but this is impossible in production with thousands of function written like this one.

Newer Messages  Older Messages
Powered By FusionForge