SCM

Forum: help

Monitor Forum | Start New Thread Start New Thread
returning using output parameter [ reply ]
By: Eric Schneider on 2014-04-02 19:35
[forum:1020996]
Trying to run the following sql returning and output parameter, I have done this in Oracle but does not seem to work in PostgreSql.

Shouldn't this work?

FYI: ErrorId is a SERIAL column.

Insert into ERRORS ( ErrorId, Date, ErrorInfo ) values ( :ItemDate, :Text ) returning ErrorId into :ID

How to install for Excel [ reply ]
By: David Quastler on 2014-03-24 13:12
[forum:1020995]
Hi, I need some help. I am not a developer and am not familiar with .Net. I am simply trying to connect from Excel (PowerPivot) to a Postgres database and was referred to this site by Microsoft to get a supported driver. Is there a simple way to install and register the driver? I looked thru the instructions and do not use Visual Studio so i'm a little lost.

invalid sql with parameters [ reply ]
By: Roberto Quintela on 2014-03-16 15:31
[forum:1020994]
Hi

This SQL sentence doesn`t get my any row but in the SQL windows of Postgre works fine

select * from nominatim where usuario='us00000010' and etiqueta='county'

any idea?

Thanks


How to capture msg of RAISE NOTICE from plpgsql SQL function with Npgset in C#? [ reply ]
By: Charles Chen on 2014-03-16 03:33
[forum:1020993]
I want to capture message of RAISE NOTICE from plpgsql SQL function with Npgset in C#? Is this doable? If yes, please provide some sample code. Thank you in advance.

PING to a server [ reply ]
By: Mahoni Oyea on 2014-03-05 12:09
[forum:1020992]
Hello, ¿is there any option to test the connection of the server of NpgsqlConnection?

For example:

if(connection.Ping())
{
//do all things
}

Thanks!

Using npgsql in MS SQL Server CLR objects development [ reply ]
By: pier pier on 2014-04-11 10:52
[forum:1020991]
I need to send data from a MS SQL Server table to a PostgreSQL table on INSERT/UPDATE. I'm thinking about developing a CLR trigger on the source SQL Server table: the assmbly refereces the npgsql.dll and the mono.security.dll, establishes a connection to the PostgreSQL server, sends the data, closes the connection.
To deply the trigger in SQL Server I need to create the assembly first using the next T-SQL code:

create assembly SimpleTrigger
authorization dbo
from 'd:\TTC\ArchivioCircolare\Lavoro\Tests\SimpleTrigger\SimpleTrigger\bin\Debug\SimpleTrigger.dll'
WITH PERMISSION_SET = unsafe
go

When I run that code I got the following exception:

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'SimpleTrigger' failed because assembly 'Mono.Security' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000001C][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000039][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x00000059][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000005A] I puntatori non gestiti non sono un tipo verificabile.
[ : Mono.Math.BigInteger+Kernel::Multiply][mdToken=0x600005d][offset 0x0000004C] Tipi non compatibili nello stack a seconda del percorso.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000001C][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x00000039][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x00000059][trovato address of Int32][previsto unmanaged pointer] Tipo non previsto nello stack.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000005A] I puntatori non gestiti non sono un tipo verificabile.
[ : Mono.Math.BigInteger+Kernel::MultiplyMod2p32pmod][mdToken=0x600005e][offset 0x0000004C] Tipi non compatibili nello stack a seconda del percorso.
[ : Mono.Math.BigInteger+Kernel::SquarePositive][mdToken=0x600005f][offset 0x0000003C][trovato address of Int32][previsto unmanaged pointer] Tipo ...

Any suggestion? Any other way to send data from MS SQL Server to PostgreSQL Server?

Thank you in advance, regards
Pier

How to connect using SSPI [ reply ]
By: t oka on 2013-12-02 02:27
[forum:1020928]
Hello,
Though a question was asked before, it has been unsolvable yet.
I have set up a windows domain trying to use SSPI.
In the same server, it is no problem.
It is a problem when divided into AP server and DB server.

Step1.
AP Server and DB Server are set as the same domain.

Step2.
Windows firewall on DB Server setup.
It enables it to connect the port of PostgreSQL to DB Server from AP Server.

Step3.
The login roll of the same name as the login user of Windows is added to the login roll of PostgreSQL on DB Server.

Step4.
Method of pg_hba.conf of PostgreSQL of DB Server is changed into SSPI.
host all all 0.0.0.0/0 sspi
host all all ::1/0 sspi

Step5.
I run my .NET client program on AP server.

NpgsqlConnection conn = new NpgsqlConnection("Server=APserver;Port=5432;Database=postgres; Integrated Security= true;");
conn.Open();
NpgsqlCommand command = conn.CreateCommand();
command.CommandText = "select * from pg_locks";
command.ExecuteScalar();
conn.Close();

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

When running client program on DB Server, it has connected using SSPI.
When using md5, id and password were set up and it has connected from AP Server.

Are there those who have connected using SSPI?
Please teach me a method.

I hope you don't mind my poor English.

Npgsql and Membership, FormsAuthentication [ reply ]
By: Martin v on 2014-01-29 07:41
[forum:1020975]
I'm developing asp net application with postgresql and need to run it in linux uisng mono. I wanna use Membership and FormsAuthentication for users login and so on. Tried to find if Npgsql supports this but wasn't able to. Can anyone tell if its possible with Npgsql or should i look elsewhere and if so maybe can anyone propose anything else to use.

Connection establishment timeout [ reply ]
By: Quentin Le Guennec on 2014-01-30 15:45
[forum:1020976]
mono version: 3.2.3
postgres version: 9.3.2

client: #1 SMP PREEMPT
Linux 3.12.9.1-ARCH x86_64 GNU/Linux

server: #1 SMP PREEMPT
Linux 3.10.27-2-ARCH armv6l GNU/Linux


Hi, i'm trying to use Npgsql via the mono compiler, and i can't manage to connect to my remote database.
Here's my connection string
:dbcon = new NpgsqlConnection ("Server=" + server + ";" +
"Port=" + port + ";" +
"Database=" + database + ";" +
"User Id=" + user + ";" +
"Password=" + password + ";Pooling=True;MINPOOLSIZE=4;MAXPOOLSIZE=15" );

I tried different values for Pooling, MINPOOLSIZE and MAXPOOLSIZE. I triple checked the values for server, port, database, user id and password. Debugging shows that monodevelop seems to spawn various threads (i guess it's mandatory)

Finally, here's the exception thrown:

Unhandled Exception:
Npgsql.NpgsqlException:
Connection establishment timeout. Increase Timeout value in ConnectionString.
at Npgsql.NpgsqlClosedState.Open (Npgsql.NpgsqlConnector context) [0x001a5] in /build/mono/src/mono-3.2.3/mcs/class/Npgsql/Npgsql/NpgsqlClosedState.cs:154
at Npgsql.NpgsqlConnector.Open () [0x00056] in /build/mono/src/mono-3.2.3/mcs/class/Npgsql/Npgsql/NpgsqlConnector.cs:674
at Npgsql.NpgsqlConnectorPool.GetPooledConnector (Npgsql.NpgsqlConnection Connection) [0x000db] in /build/mono/src/mono-3.2.3/mcs/class/Npgsql/Npgsql/NpgsqlConnectorPool.cs:340
[ERROR] FATAL UNHANDLED EXCEPTION: Npgsql.NpgsqlException:
Connection establishment timeout. Increase Timeout value in ConnectionString.
at Npgsql.NpgsqlClosedState.Open (Npgsql.NpgsqlConnector context) [0x001a5] in /build/mono/src/mono-3.2.3/mcs/class/Npgsql/Npgsql/NpgsqlClosedState.cs:154
at Npgsql.NpgsqlConnector.Open () [0x00056] in /build/mono/src/mono-3.2.3/mcs/class/Npgsql/Npgsql/NpgsqlConnector.cs:674
at Npgsql.NpgsqlConnectorPool.GetPooledConnector (Npgsql.NpgsqlConnection Connection) [0x000db] in /build/mono/src/mono-3.2.3/mcs/class/Npgsql/Npgsql/NpgsqlConnectorPool.cs:340


System.NullReferenceException at Npgsql.NpgsqlState.d__a.MoveNext() [ reply ]
By: Jochen Wezel on 2014-01-27 12:10
[forum:1020974]
After using command text
"SELECT * FROM GeneralProperties WHERE upper(PropertyName) = upper(:PropertyName) LIMIT 1"

I get following exception:
System.NullReferenceException: Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.
bei Npgsql.NpgsqlState.d__a.MoveNext()
bei Npgsql.ForwardsOnlyDataReader.GetNextResponseObject()
bei Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
bei Npgsql.ForwardsOnlyDataReader.NextResult()
bei Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError)
bei Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
bei Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
bei Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
bei CompuMaster.Data.DataQuery.AnyIDataProvider.ExecuteReader(IDbCommand dbCommand, Automations automations) in G:\svn_repository\compumaster_products\tools\CompuMaster.Data\CompuMaster.Data\CompuMaster.Data.DataQuery.AnyIDataProvider.vb:Zeile 832.

And this is my line which throws the error:
Result = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)

I notice that this problem occurs on 1 workstation regularly, other workstations don't have got such issue.

Using SSPI to connect to remote PostgreSQL server using Npgsql [ reply ]
By: Apar V on 2014-01-27 11:40
[forum:1020972]
I am using nHibernate + Npgsql to connect to PostgreSQL server using windows authentication (SSPI). I have added the windows user to the Postgres and have configured the settings as recommended by http://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows.

Here is my connection string: "Server=;Port=5432;Database=;Integrated Security=true"

This is my code:

if (!sessionFactories.TryGetValue(sessionFactoryConfigPath, out sessionFactory))
{
Configuration cfg = new Configuration();
if (sessionFactoryConfigPath != DefaultConfigFile)
{
//Check.Require(File.Exists(sessionFactoryConfigPath),
// "The config file at '" + sessionFactoryConfigPath + "' could not be found");

cfg.Configure(sessionFactoryConfigPath);

}
else
{
cfg.Configure();
}


// Now that we have our Configuration object, create a new SessionFactory
sessionFactory = cfg.BuildSessionFactory();
When I run this I get the following error at sessionFactory = cfg.BuildSessionFactory(); "FATAL: XX000: could not accept SSPI security context"

When I checked the PostgreSQL logs the following entry was found: "The token supplied to the function is invalid (80090308)"

What am I missing here? Please help.

The given assembly name or codebase was invalid. (Exception from HRESULT: 0x8013 [ reply ]
By: Phil Arena on 2014-01-26 17:38
[forum:1020971]
I am trying to get npgsql working on a default project in VS2013.

I am getting the following error. Any help would be appreciated.

The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)


Source Error:

Line 28: public SimpleMembershipInitializer()
Line 29: {
Line 30: Database.SetInitializer<UsersContext>(null);
Line 31:
Line 32: try

web config:
<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
</configSections>
<connectionStrings>
<add name="DefaultConnection" providerName="Npgsql" connectionString="Server=127.0.0.1;Port=5432; Database=completrance; User Id=philarena; Password=RasterOpsx3!;"/>
<!-- <add name="DefaultConnection2" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-Test-20140125180531;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-Test-20140125180531.mdf" providerName="System.Data.SqlClient" /> -->
</connectionStrings>
<appSettings>
<add key="webpages:Version" value="2.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="PreserveLoginUrl" value="true" />
<add key="ClientValidationEnabled" value="true" />
<add key="UnobtrusiveJavaScriptEnabled" value="true" />
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.5.1" />
<httpRuntime targetFramework="4.5.1" />
<authentication mode="Forms">
<forms loginUrl="~/Account/Login" timeout="2880" />
</authentication>
<pages>
<namespaces>
<add namespace="System.Web.Helpers" />
<add namespace="System.Web.Mvc" />
<add namespace="System.Web.Mvc.Ajax" />
<add namespace="System.Web.Mvc.Html" />
<add namespace="System.Web.Optimization" />
<add namespace="System.Web.Routing" />
<add namespace="System.Web.WebPages" />
</namespaces>
</pages>
</system.web>
<system.webServer>
<validation validateIntegratedModeConfiguration="false" />
<handlers>
<remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" />
<remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" />
<remove name="ExtensionlessUrlHandler-Integrated-4.0" />
<add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness32" responseBufferLimit="0" />
<add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness64" responseBufferLimit="0" />
<add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
</handlers>
</system.webServer>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="DotNetOpenAuth.Core" publicKeyToken="2780ccd10d57b246" />
<bindingRedirect oldVersion="0.0.0.0-4.1.0.0" newVersion="4.1.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="DotNetOpenAuth.AspNet" publicKeyToken="2780ccd10d57b246" />
<bindingRedirect oldVersion="0.0.0.0-4.1.0.0" newVersion="4.1.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-1.3.0.0" newVersion="1.3.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Npgsql" PublicKeyToken="5d8b90d52f46fda7"/>
<!-- <bindingRedirect oldVersion="" newVersion="2.1.0-beta1"/> -->
</dependentAssembly>
</assemblyBinding>
</runtime>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="Npgsql" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.1.0-beta1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<!-- <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> -->
<!-- <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" /> -->
</providers>
</entityFramework>
</configuration>

Stack Trace:

[FileLoadException: The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)]
System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type) +0
System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName) +70
System.RuntimeType.GetType(String typeName, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark) +39
System.Type.GetType(String typeName, Boolean throwOnError) +34
System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName) +15
System.Data.Entity.Internal.AppConfig.<.ctor>b__2(ProviderElement e) +71
System.Linq.WhereSelectEnumerableIterator`2.MoveNext() +145
System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +381
System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
System.Data.Entity.Internal.AppConfig.<.ctor>b__1() +83
System.Lazy`1.CreateValue() +415
System.Lazy`1.LazyInitValue() +152
System.Lazy`1.get_Value() +75
System.Data.Entity.Internal.AppConfig.get_DbProviderServices() +9
System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.RegisterDbProviderServices() +18
System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetServiceFactory(Type type, String name) +92
System.Data.Entity.Infrastructure.DependencyResolution.<>c__DisplayClass1.<GetService>b__0(Tuple`2 t) +42
System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory) +72
System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetService(Type type, Object key) +140
System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetServiceAsServices(IDbDependencyResolver resolver, Type type, Object key) +13
System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetServices(Type type, Object key) +9
System.Data.Entity.Infrastructure.DependencyResolution.<>c__DisplayClass6.<GetServices>b__5(IDbDependencyResolver r) +19
System.Linq.<SelectManyIterator>d__14`2.MoveNext() +234
System.Linq.<ConcatIterator>d__71`1.MoveNext() +111
System.Linq.<OfTypeIterator>d__aa`1.MoveNext() +86
System.Data.Entity.Utilities.IEnumerableExtensions.Each(IEnumerable`1 ts, Action`1 action) +78
System.Data.Entity.Infrastructure.DependencyResolution.InternalConfiguration.Lock() +136
System.Data.Entity.Infrastructure.DependencyResolution.DbConfigurationManager.<.ctor>b__1() +90
System.Lazy`1.CreateValue() +415
System.Lazy`1.LazyInitValue() +152
System.Lazy`1.get_Value() +75
System.Data.Entity.Infrastructure.DependencyResolution.DbConfigurationManager.GetConfiguration() +207
System.Data.Entity.Database.SetInitializer(IDatabaseInitializer`1 strategy) +78
Test.Filters.SimpleMembershipInitializer..ctor() in c:\Users\Evolution\Documents\Visual Studio 2013\Projects\Test\Test\Filters\InitializeSimpleMembershipAttribute.cs:30

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) +0
System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) +113
System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) +232
System.Activator.CreateInstance(Type type, Boolean nonPublic) +83
System.Activator.CreateInstance(Type type) +6
System.Threading.LazyHelpers`1.ActivatorFactorySelector() +68
System.Threading.LazyInitializer.EnsureInitializedCore(T& target, Boolean& initialized, Object& syncLock, Func`1 valueFactory) +115
System.Threading.LazyInitializer.EnsureInitialized(T& target, Boolean& initialized, Object& syncLock) +106
Test.Filters.InitializeSimpleMembershipAttribute.OnActionExecuting(ActionExecutingContext filterContext) in c:\Users\Evolution\Documents\Visual Studio 2013\Projects\Test\Test\Filters\InitializeSimpleMembershipAttribute.cs:23
System.Web.Mvc.Async.AsyncControllerActionInvoker.InvokeActionMethodFilterAsynchronously(IActionFilter filter, ActionExecutingContext preContext, Func`1 nextInChain) +69
System.Web.Mvc.Async.<>c__DisplayClass3b.<BeginInvokeActionMethodWithFilters>b__35() +22
System.Web.Mvc.Async.AsyncControllerActionInvoker.InvokeActionMethodFilterAsynchronously(IActionFilter filter, ActionExecutingContext preContext, Func`1 nextInChain) +492
System.Web.Mvc.Async.<>c__DisplayClass3b.<BeginInvokeActionMethodWithFilters>b__35() +22
System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__31(AsyncCallback asyncCallback, Object asyncState) +190
System.Web.Mvc.Async.WrappedAsyncResult`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +129
System.Web.Mvc.Async.AsyncControllerActionInvoker.BeginInvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters, AsyncCallback callback, Object state) +182
System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__1e(AsyncCallback asyncCallback, Object asyncState) +445
System.Web.Mvc.Async.WrappedAsyncResult`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +129
System.Web.Mvc.Async.AsyncControllerActionInvoker.BeginInvokeAction(ControllerContext controllerContext, String actionName, AsyncCallback callback, Object state) +302
System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__17(AsyncCallback asyncCallback, Object asyncState) +30
System.Web.Mvc.Async.WrappedAsyncResult`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +129
System.Web.Mvc.Controller.BeginExecuteCore(AsyncCallback callback, Object state) +338
System.Web.Mvc.Async.WrappedAsyncResult`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +129
System.Web.Mvc.Controller.BeginExecute(RequestContext requestContext, AsyncCallback callback, Object state) +316
System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.BeginExecute(RequestContext requestContext, AsyncCallback callback, Object state) +15
System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__2(AsyncCallback asyncCallback, Object asyncState) +71
System.Web.Mvc.Async.WrappedAsyncResult`1.Begin(AsyncCallback callback, Object state, Int32 timeout) +129
System.Web.Mvc.MvcHandler.BeginProcessRequest(HttpContextBase httpContext, AsyncCallback callback, Object state) +251
System.Web.Mvc.MvcHandler.BeginProcessRequest(HttpContext httpContext, AsyncCallback callback, Object state) +48
System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData) +16
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +301
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Npgsql version for Entityframework 6.0.2 [ reply ]
By: bhupendra singh on 2014-01-24 04:14
[forum:1020969]
Hi All,

Which version of Npgsql is compatible with EF 6.0.2 in VS2012.
If anybody have the web config setting for that then Please share.

Thanks in advance
Bhupendra

ProviderIncompatibleException while doing &quot;update model-&gt; database&quot; in 2.0.14.3 [ reply ]
By: Seva Zaslavsky on 2014-01-03 22:21
[forum:1020963]
I'm getting this error while doing "update model-> database".

This was working fine under 2.0.12.0. I'm using VS 2012, EF 5.0, Postgres 9.0.8

It started to break when I changed the Npgsql version in machine.conf from 2.0.12.0 to 2.0.14.3.

---------------------------
Microsoft Visual Studio
---------------------------
An error occurred while connecting to the database. The database might be unavailable. An exception of type 'System.Data.ProviderIncompatibleException' occurred. The error message is: 'The store data provider failed to return information for the StoreSchemaDefinition request. See the inner exception for details.

The inner exception caught was of type 'System.ArgumentNullException', with this error message: 'Value cannot be null.

Parameter name: input'.'.
---------------------------
OK
---------------------------

IncompatibleProviderException [ reply ]
By: bhupendra singh on 2014-01-23 12:49
[forum:1020964]
Hi All,

I am trying to connect PostgreSQL 9.3.2 with Entity Framework in VS2012 using Npgsql provider.

I get error about provider.

A null was returned after calling the 'get_ProviderFactory' method on a store provider instance of type 'Npgsql.NpgsqlConnection'. The store provider might not be functioning correctly.

Which version of EF and Npgsql is compatible and what will be the setting in app.config file?

Please suggest me working code for this.
Thanks in advance

Bhupendra

Entity Framework DatabaseGeneratedOption.Identity and DEFAULT uuid_generate_v4 [ reply ]
By: Vladimir Grishin on 2013-12-27 06:12
[forum:1020952]
Hello,

Please correct me if I`am wrong. Is it true that Npgsql(2.0.14.3) does not support uuid keys with DEFAULT uuid_generate_v4()?

Sample code below

public class User
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid ID { get; set; }

[Required, MaxLength(20)]
public string Name { get; set; }

[Required, MaxLength(20)]
public string Password { get; set; }
}

SQL code

CREATE EXTENSION "uuid-ossp";

CREATE TABLE "dbo"."User" (
"ID" uuid NOT NULL DEFAULT uuid_generate_v4(),
"Name" varchar(20) NOT NULL,
"Password" varchar(20) NOT NULL,
CONSTRAINT "PK_dbo.User" PRIMARY KEY ("ID")
);

Let's add some user

public partial class MyDbContext : DbContext
{
public IDbSet<User> Users { get; set; }
}

var user = new User
{
Name = "User1",
Password = "Pwd1"
};
context.Users.AddOrUpdate(user);
context.SaveChanges(); // exception here

Exception message
A null storage-generated value was returned for a non-nullable member 'ID' of type 'Guid'.

Postgres log contains sql code
INSERT INTO "dbo"."User"("Name","Password") VALUES ('User1','Pwd1');SELECT currval(pg_get_serial_sequence('"dbo"."User"', 'ID')) AS "ID"

Thank you

MVC Npgsql Linq EF6 insert bool value [ reply ]
By: Jean-Philippe Chenel on 2013-12-03 21:26
[forum:1020947]
Hi,

I'm using Entity Framework 6 and Npgsql (2.0.13 beta 1) to query, insert, update, delete. Working fine for querying, but I've a problem to insert value of boolean type.

Model:
public bool active { get; set; }

Controller:
db.Support.Add(model);
db.SaveChanges();

I've an error when the SaveChanges() occurs saying that the field etrue, or efalse don't exist. I think that the e in the front of the false or true is comming from the last letter of the active field. Also, the ModelState.IsValid return always false when I've a bool datatype in the Model.

Thank you very much,
Jean-Philippe

Error with npgsql 2.0.14.2 on EF6 [ reply ]
By: Daniel Yeung on 2013-12-17 04:10
[forum:1020945]
I added the latest version using Nuget.
Modified the Web.config as follows:

......
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="v11.0" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="Npgsql" type="Npgsql.NpgsqlFactory, Npgsql" />
</providers>

</entityFramework>
<!--07/15/2013 DY <system.data></system.data> added here for postgres as given in chinookconsole example
Also added connection strings as shown at top of file-->
<system.data>
<DbProviderFactories>
<add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" />
</DbProviderFactories>
</system.data>
</configuration>

When I try to enable-migrations, get the following error:

PM> enable-migrations -ContextTypeName DIPS_Context -MigrationsDirectory DataContexts\DIPS_Migrations -verbose
.....
ERROR:

The 'Instance' member of the Entity Framework provider type 'Npgsql.NpgsqlFactory, Npgsql, Version=2.0.14.2, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'. Entity Framework providers must inherit from this class and the 'Instance' member must return the singleton instance of the provider. This may be because the provider does not support Entity Framework 6 or later; see http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

What did I do wrong?

Any suggestions will be most helpful.

Thanks,

Daniel

how to use source code from link [ reply ]
By: Andrus Moor on 2013-11-19 20:28
[forum:1020913]
npgsql source code was downloaded from git and added as existing project to VS 2013 Express colution.

This project was referenced from other class library project in this solution by adding npgsql project to its references.

In this project line

using Npgsql;

causes compile error

The type or namespace name 'Npgsql' could not be found (are you missing a using directive or an assembly reference?)

How to try latest npgsql from git ?

Intellisense works, eq. typing Npgsql. in code shows npgsql members.
Cleaning solution does not fix it. I removed refereneces to npgsql production dll in every project in solution and added reference to npgsql project instead, re-build solution but problem persist.

It worked if I tried it in month ago in VS2012 . No idea why it does not work anymore.

I posted it also in http://stackoverflow.com/questions/20081386/why-adding-npgsql-project-reference-in-vs2013-express-does-not-find-it

The value of HasRows changes by a row position [ reply ]
By: t oka on 2013-11-26 07:36
[forum:1020917]
Hello,
The value of HasRows changes by a row position.
Is it Npgsql's specification or bug?
Since Oracle and SQL Server differ from a result, I am troubled.

I hope you don't mind my poor English.

NpgsqlCommand command = conn.CreateCommand();
command.CommandText = "select * from trimspace";

NpgsqlDataReader reader;
reader = command.ExecuteReader();

while (reader.Read())
{
Debug.WriteLine(reader.HasRows);
}
reader.Close();

Result:
True
...
True
False

Oracle's document:
The value of HasRows does not change based on the row position. For example, even if the application has read all the rows from the result set and the next Read method invocation will return false, the HasRows property still returns true since the result set was not empty to begin with.
Rows are fetched to determine the emptiness of the OracleDataReader when HasRows property is accessed for the first time after the creation of the OracleDataReader object.

Connection Pool and multi-threading [ reply ]
By: Marcelo Zabani on 2013-12-01 17:55
[forum:1020919]
Hi there, I'm getting the following exception from time to time:

System.NotSupportedException: Backend sent unrecognized response type: i at Npgsql.NpgsqlState+d__a.MoveNext () [0x00000] in :0 at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject () [0x00000] in :0

I have read that it is because the same connection is being used in different threads. Let me explain exactly my context: I have one thread that receives HTTP connections and dispatches them to the application in a System.Threading.Tasks.Task so that it can keep receiving new connections.
The application then uses NHibernate+Npgsql to do its database work, and sometimes I get the exception shown above.

I would like to further understand how the connection pool works. I'm afraid that two different requests may be running in the same thread and as such may be getting the same connection from the pool, leading to problems.

Am I right this could be the cause of this problem? If so, what can I do? What other care should I take with this approach?

Thank you in advance,
Marcelo.

Failed to establish a connection to '127.0.0.1'. [ reply ]
By: Maxim Lastname on 2013-11-30 15:13
[forum:1020918]

image.jpg (20) downloads
FreeBSD 9.2
PostgreeSQL 9.3
Monodevelop 4.0.12
Mono JIT compiler version 3.2.3

Error connecting
What could be the reason?

Backend sent unrecognized response type: e [ reply ]
By: Adao Silva on 2013-11-21 14:20
[forum:1020915]
This error occurs when executing a simple insert table in the middle of a transaction.
If the insert command is isolated and executed in the pgAdmin/Query tool there's no problem.
It occurs in only one specific situation hard to give details. No problem in the most of situations.
I've tried this using the following versions: 2.0.12.0 and 2.0.11.0.

Thanks.

false parameter value is passed as true [ reply ]
By: Andrus Moor on 2013-10-17 10:38
[forum:1020862]

Using code from git master and NauckIt membershipprovider, false parameter value is passed as true server.

Tried NauckIT PgMembershipProvicer.cs modified ResetPassword method:

using (NpgsqlCommand dbUpdateCommand = dbConn.CreateCommand())
{
dbUpdateCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "UPDATE kasutaja SET Passwordha = @Password, LastPasswordChangedDate = @LastPasswordChangedDate WHERE kasutaja = trim(upper(@Username)) and islocked = @IsLockedOut");

dbUpdateCommand.Parameters.Add("@Password", NpgsqlDbType.Varchar, 128).Value = EncodePassword(newPassword);
dbUpdateCommand.Parameters.Add("@LastPasswordChangedDate", NpgsqlDbType.TimestampTZ).Value = DateTime.Now;
dbUpdateCommand.Parameters.Add("@Username", NpgsqlDbType.Varchar, 255).Value = username;

// actually true is passed to server:
dbUpdateCommand.Parameters.Add("@IsLockedOut", NpgsqlDbType.Boolean).Value = false;
dbUpdateCommand.Prepare();
rowsAffected = dbUpdateCommand.ExecuteNonQuery();

output in server log:


LOG: execute npgsqlplan2: UPDATE kasutaja SET Passwordha = $1::varchar(128), LastPasswordChangedDate = $2::timestamptz WHERE kasutaja = trim(upper($3::varchar(255))) and islocked = $4::bool
2013-10-17 12:23:09 EEST DETAIL: parameters: $1 = 'ya6PS3VfXHL2VT3/8H6eC7JJ9ZU=', $2 = '2013-10-17 12:23:07.042988+03', $3 = 'ADMIN', $4 = 't'


note that $4 value is passed as true mut in code it is passed as false.


islocked is defined as ebool:

CREATE TABLE kasutaja( ... islocked ebool )

ebool is defined as

CREATE DOMAIN ebool AS boolean DEFAULT false NOT NULL;


How to fix ?

Andrus.

Same Query For one cient is 16x faster than for another [ reply ]
By: Seva Zaslavsky on 2013-11-06 22:19
[forum:1020870]
I have written a simple test to measure the round-trip time for a trivial query using Npgsql. The objective of this test is to measure the overhead due to Npgsql and network.

The results are:

Client A is approx 1 ms (although it drops to .6 ms at times) for each query
Client B is consistently 15-16 ms. for each query

So I'm trying to figure out what is causing the high overhead on Client B.

The test does the following 1000 trivial select commands:

select 1;
select 2;
...
select 999;
select 1000;

Client Machines A and B sit in the same rack, on the same subnet and connected to the same postgres 9.2.4 server. They are both high-end servers, similarly configured

with Windows Server 2008 R2. The only major difference is that Machine A has MSVS 2010 and MSVS 2012 Professional installed and Machine B does not.

I've tried a number of things.

The results of the test are unchanged when:

- I switch from npgsql 2.0.12.0 to 2.0.13.91
- I switch from .net 4 to .net 4.5
- I use npgsql in the GAC or in the local directory
- I run the code using a local copy or from a shared drive
- Run elevated permissions
- turn firewalls on/off

I checked all of the tcp parameters in the registry and they are the same between Client A and Client B.

The issue does not appear to be in the network because:

- the results of pgbench.exe are very similar on both machines
- Analyzing the output of Sysinternals Process Monitor revealed that the intervals between timestamps of the

requests/responses are equally fast on both machines so the postgres server is not discriminating against Client B in any way

- I created a SQL script with the equivalent sql select statements and used psql.exe to send it to the server. The results were similarly fast on both machines.

I ran the test in a profiler (Eqatec) on both machines and there are significant differences that I observed. They center around the calls to Socket.Poll:

(Calls / Total Time (ms))

Client A:

NpgContextholder.ProcessServerMessages (3432 ms / 3426 calls = 1 ms / call)
NpgSqlState.ProcessBackendResponseEnum(0 ms / 1000 calls = 0 ms/call)
NpgSqlConnector.socket.Poll(3432 ms / 4426 calls = 0.8 ms / call )



Client B:

NpgContextholder.ProcessServerMessages (18162 ms / 1191 calls= 15 ms / call)
NpgSqlState.ProcessBackendResponseEnum(156 ms / 1000 calls = 0.2 ms / call)
NpgSqlConnector.socket.Poll(18318 ms / 2191 calls = 8.4 ms / call)

It appears to me that there is some kind of delay between the receipt of the data on the socket and Poll() exiting. So I turned on .Net network tracing on Machine B and it actually ran faster! Unfortunately the data in the .net network tracing output is accurate to 16 ms and therefore not very useful.

There is clearly an issue with Machine B, but I have other machines that suffer the same problem that do not have Visual Studio installed. In addition to Machine A I have another machine that returns fast results and that machine also has Visual Studio installed. So the the fast results are correlated with having visual studio installed. This doesn't make sense because I run all tests from the command line.

Can you think of some explanation or another test to try?

How to reset session of a connection in connection pool? [ reply ]
By: Suresh Balasubramanian on 2013-07-25 11:31
[forum:1020850]
Hi,

How to reset session (my worry is sequence info) once I am done with the connection? What I want is, if I run currval('xxxx_id_seq') using a new connection I should get 'sequence not defined' instead of last inserted value.

If I close my program and open again I am getting the desired behavior. But I want this when switching between forms.

I tried appending the below commands to clear the session. But that doesn't seem to reset the sequence info retained in the session.

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;

Please help.

Regards,
Suresh Bala

Infinite exceptions thrown by ExecuteNonQuery() [ reply ]
By: Michał Dimmich on 2013-11-07 13:30
[forum:1020876]
Hello,

When I'm calling ExecuteNonQuery(or ExecuteReader) on some command, which is illegal in my database, and database throws an exception, the function throws infinite exceptions. Here is a code snippet:

command = new NpgsqlCommand("DELETE FROM COMPANY WHERE id='1';", conn);
try
{
int rowAffected = command.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}

The try catch didn't catch any exceptions. I did get a message "The application stopped working" though.

So I tried handling "AppDomain.CurrentDomain.FirstChanceException += CurrentDomain_FirstChanceException;".

It turned out, the system is flooded with exceptions:
The first is a postgresql exception, but the rest are "There is already a DataReader associated with this command which must be closed first."

Am I missing something? How is one supposed to handle those exceptions?

Postgres 9.3 [ reply ]
By: Thomas Matelich on 2013-11-07 17:32
[forum:1020878]
Was getting lots of "Backend sent unrecognized response type: a" messages, chased my tail a bit on blaming multi-threading. So I looked at the beta and saw it referenced supporting 9.3 error messages. Seems to have fixed my issue, but I'm going to be shipping in the next month or so. Do I have to fall back to 9.2 (we ship postgres with our product) or is a new release pending?

Npgsql &amp; Excel 2013 Power Query [ reply ]
By: Ann Smilebox on 2013-10-29 16:30
[forum:1020864]
I set up the connection to my postgres DB using the instructions found here: http://office.microsoft.com/en-us/excel-help/connect-to-a-postgresql-database-HA104028095.aspx#_Install_the_PostgreSQL

I can log in to the DB but I'm getting an error in Excel Power Query that says:
"DataSource.Error: PostgreSQL: ERROR: 42601: syntax error at or near "TABLE_TYPE" Details: Message=ERROR: 42601:..., ErrorCode=-2147467259."

Any ideas on what's going on? Or how to fix it?

Thanks

Command Timeout exception thrown [ reply ]
By: Jeremy Bowen on 2012-09-02 21:23
[forum:1019887]
I'm getting periodic errors from my application when connecting to Postgres. I get an exception from Npgsql saying:
"A timeout has occured. If you were establishing a connection, increase Timeout value in ConnectionString. If you were executing a command, increase the CommandTimeout value in ConnectionString or in your NpgsqlCommand object."

I think this is occurring in ProcessBackendResponseEnum.

This seems to be entirely random and on a lightly loaded system and unfortunately doesn't seem to occur in my development environment. It occurs once every couple of days in a production system though.

CommandTimeout=60 and Timeout=30 seconds in my connection string. It doesn't seem to make a difference by extending these (although I haven't tried anything more than a couple of minutes).

Is it possible that some other part of the system (eg GUI thread) is running and starving the Npgsql thread of CPU cycles ?
Is there any way of logging the failed query that has triggered the timeout ?

This issue has shown up in various versions of Npgsql and upgrading hasn't eliminated it.

I don't believe the query is actually timing out as the PostgreSQL server is normally very responsive. The queries themselves are relatively simple and would normally return results within 1 second.

Any help in diagnosing this would be greatly appreciated.

KB2805226 breaks SQL generation [ reply ]
By: Olivier MATROT on 2013-10-16 13:18
[forum:1020861]
Hello,

I just experienced a weird issue with Entity Framework/Npgsql for one of our customer.

It started to have incorrect SQL generated after an Update of .NET 4.5.

After some tests, we found out that it is related to the following update :
http://support.microsoft.com/kb/2805226

We solved the issue by uninstalling the update.
I'm not sure if this is a bug in the provider or a regression in the fix.

Following are affected queries :

Before UPDATE : SQL is OK :

SELECT "Project1"."tcpid" AS "tcpid","Project1"."cstid" AS "cstid","Project1"."tcpnom" AS "tcpnom","Project1"."tcpcode" AS "tcpcode","Project1"."tcpdeviceid" AS "tcpdeviceid","Project1"."tcpressource" AS "tcpressource","Project1"."tcpft" AS "tcpft","Project1"."tcpprice" AS "tcpprice","Project1"."tcpthresholdcoeff" AS "tcpthresholdcoeff","Project1"."tcpadeli" AS "tcpadeli","Project1"."tcphprimcode" AS "tcphprimcode","Project1"."tcprpps" AS "tcprpps","Project1"."tcphprimid" AS "tcphprimid","Project1"."datastatus" AS "datastatus","Project1"."tcpcpamcode" AS "tcpcpamcode","Project1"."xmin" AS "xmin","Project1"."mdtid" AS "mdtid","Project1"."mdccode" AS "mdccode","Project1"."tcrid" AS "tcrid","Project1"."equid" AS "equid","Project1"."mdtaetitle" AS "mdtaetitle","Project1"."mdtxraydoseunit" AS "mdtxraydoseunit","Project1"."mdtxraydoseunitoptional" AS "mdtxraydoseunitoptional","Project1"."mdtisxraydosebympps" AS "mdtisxraydosebympps","Project1"."mdtipaddress" AS "mdtipaddress","Project1"."mdtipport" AS "mdtipport","Project1"."mdtregistration" AS "mdtregistration","Project1"."mdtpower" AS "mdtpower","Project1"."mdtpowerunit" AS "mdtpowerunit","Project1"."mdtbillingid" AS "mdtbillingid","Project1"."mdtbillingmodalitycode" AS "mdtbillingmodalitycode","Project1"."mdtreadingonterminaldefaultvalue" AS "mdtreadingonterminaldefaultvalue","Project1"."xmin1" AS "xmin1"
FROM
(
SELECT "Extent1"."tcpid" AS "tcpid","Extent1"."cstid" AS "cstid","Extent1"."tcpnom" AS "tcpnom","Extent1"."tcpcode" AS "tcpcode","Extent1"."tcpdeviceid" AS "tcpdeviceid","Extent1"."tcpressource" AS "tcpressource","Extent1"."tcpft" AS "tcpft","Extent1"."tcpprice" AS "tcpprice","Extent1"."tcpthresholdcoeff" AS "tcpthresholdcoeff","Extent1"."tcpadeli" AS "tcpadeli","Extent1"."tcphprimcode" AS "tcphprimcode","Extent1"."tcprpps" AS "tcprpps","Extent1"."tcphprimid" AS "tcphprimid","Extent1"."datastatus" AS "datastatus","Extent1"."tcpcpamcode" AS "tcpcpamcode","Extent1"."xmin" AS "xmin","Extent2"."mdtid" AS "mdtid","Extent2"."mdccode" AS "mdccode","Extent2"."tcrid" AS "tcrid","Extent2"."equid" AS "equid","Extent2"."mdtaetitle" AS "mdtaetitle","Extent2"."mdtxraydoseunit" AS "mdtxraydoseunit","Extent2"."mdtxraydoseunitoptional" AS "mdtxraydoseunitoptional","Extent2"."mdtisxraydosebympps" AS "mdtisxraydosebympps","Extent2"."mdtipaddress" AS "mdtipaddress","Extent2"."mdtipport" AS "mdtipport","Extent2"."mdtregistration" AS "mdtregistration","Extent2"."mdtpower" AS "mdtpower","Extent2"."mdtpowerunit" AS "mdtpowerunit","Extent2"."mdtbillingid" AS "mdtbillingid","Extent2"."mdtbillingmodalitycode" AS "mdtbillingmodalitycode","Extent2"."mdtreadingonterminaldefaultvalue" AS "mdtreadingonterminaldefaultvalue","Extent2"."xmin" AS "xmin1"
FROM "acstudy"."ttechnicalpackages" AS "Extent1"
INNER JOIN "acadmin"."tmodalities" AS "Extent2" ON "Extent1"."tcpdeviceid"="Extent2"."mdtid"
WHERE ("Extent1"."cstid"= (((0)))) AND (4="Extent1"."datastatus")
) AS "Project1"
ORDER BY "Project1"."tcpprice" ASC

After the update : SQL is KO :

SELECT "Project1"."tcpid" AS "tcpid","Project1"."cstid" AS "cstid","Project1"."tcpnom" AS "tcpnom","Project1"."tcpcode" AS "tcpcode","Project1"."tcpdeviceid" AS "tcpdeviceid","Project1"."tcpressource" AS "tcpressource","Project1"."tcpft" AS "tcpft","Project1"."tcpprice" AS "tcpprice","Project1"."tcpthresholdcoeff" AS "tcpthresholdcoeff","Project1"."tcpadeli" AS "tcpadeli","Project1"."tcphprimcode" AS "tcphprimcode","Project1"."tcprpps" AS "tcprpps","Project1"."tcphprimid" AS "tcphprimid","Project1"."datastatus" AS "datastatus","Project1"."tcpcpamcode" AS "tcpcpamcode","Project1"."xmin" AS "xmin","Project1"."mdtid" AS "mdtid","Project1"."mdccode" AS "mdccode","Project1"."tcrid" AS "tcrid","Project1"."equid" AS "equid","Project1"."mdtaetitle" AS "mdtaetitle","Project1"."mdtxraydoseunit" AS "mdtxraydoseunit","Project1"."mdtxraydoseunitoptional" AS "mdtxraydoseunitoptional","Project1"."mdtisxraydosebympps" AS "mdtisxraydosebympps","Project1"."mdtipaddress" AS "mdtipaddress","Project1"."mdtipport" AS "mdtipport","Project1"."mdtregistration" AS "mdtregistration","Project1"."mdtpower" AS "mdtpower","Project1"."mdtpowerunit" AS "mdtpowerunit","Project1"."mdtbillingid" AS "mdtbillingid","Project1"."mdtbillingmodalitycode" AS "mdtbillingmodalitycode","Project1"."mdtreadingonterminaldefaultvalue" AS "mdtreadingonterminaldefaultvalue","Project1"."xmin1" AS "xmin1","Project1"."equid1" AS "equid1","Project1"."etycode" AS "etycode","Project1"."equlabel" AS "equlabel","Project1"."equgmaonum" AS "equgmaonum","Project1"."equserialnum" AS "equserialnum","Project1"."equroomimpact" AS "equroomimpact","Project1"."equunavailablestart" AS "equunavailablestart","Project1"."equunavailableend" AS "equunavailableend","Project1"."equinstallationdate" AS "equinstallationdate","Project1"."equmake" AS "equmake","Project1"."equmodel" AS "equmodel","Project1"."equcomment" AS "equcomment","Project1"."datastatus1" AS "datastatus1","Project1"."equmammographapprovalnumber" AS "equmammographapprovalnumber","Project1"."xmin2" AS "xmin2"
FROM (
SELECT "Filter1"."tcpid" AS "tcpid","Filter1"."cstid" AS "cstid","Filter1"."tcpnom" AS "tcpnom","Filter1"."tcpcode" AS "tcpcode","Filter1"."tcpdeviceid" AS "tcpdeviceid","Filter1"."tcpressource" AS "tcpressource","Filter1"."tcpft" AS "tcpft","Filter1"."tcpprice" AS "tcpprice","Filter1"."tcpthresholdcoeff" AS "tcpthresholdcoeff","Filter1"."tcpadeli" AS "tcpadeli","Filter1"."tcphprimcode" AS "tcphprimcode","Filter1"."tcprpps" AS "tcprpps","Filter1"."tcphprimid" AS "tcphprimid","Filter1"."datastatus" AS "datastatus","Filter1"."tcpcpamcode" AS "tcpcpamcode","Filter1"."xmin" AS "xmin","Filter1"."mdtid" AS "mdtid","Filter1"."mdccode" AS "mdccode","Filter1"."tcrid" AS "tcrid","Filter1"."equid" AS "equid","Filter1"."mdtaetitle" AS "mdtaetitle","Filter1"."mdtxraydoseunit" AS "mdtxraydoseunit","Filter1"."mdtxraydoseunitoptional" AS "mdtxraydoseunitoptional","Filter1"."mdtisxraydosebympps" AS "mdtisxraydosebympps","Filter1"."mdtipaddress" AS "mdtipaddress","Filter1"."mdtipport" AS "mdtipport","Filter1"."mdtregistration" AS "mdtregistration","Filter1"."mdtpower" AS "mdtpower","Filter1"."mdtpowerunit" AS "mdtpowerunit","Filter1"."mdtbillingid" AS "mdtbillingid","Filter1"."mdtbillingmodalitycode" AS "mdtbillingmodalitycode","Filter1"."mdtreadingonterminaldefaultvalue" AS "mdtreadingonterminaldefaultvalue","Filter1"."xmin1" AS "xmin1","Extent4"."equid" AS "equid1","Extent4"."etycode" AS "etycode","Extent4"."equlabel" AS "equlabel","Extent4"."equgmaonum" AS "equgmaonum","Extent4"."equserialnum" AS "equserialnum","Extent4"."equroomimpact" AS "equroomimpact","Extent4"."equunavailablestart" AS "equunavailablestart","Extent4"."equunavailableend" AS "equunavailableend","Extent4"."equinstallationdate" AS "equinstallationdate","Extent4"."equmake" AS "equmake","Extent4"."equmodel" AS "equmodel","Extent4"."equcomment" AS "equcomment","Extent4"."datastatus" AS "datastatus1","Extent4"."equmammographapprovalnumber" AS "equmammographapprovalnumber","Extent4"."xmin" AS "xmin2"
FROM (
SELECT "Extent1"."tcpid" AS "tcpid","Extent1"."cstid" AS "cstid","Extent1"."tcpnom" AS "tcpnom","Extent1"."tcpcode" AS "tcpcode","Extent1"."tcpdeviceid" AS "tcpdeviceid","Extent1"."tcpressource" AS "tcpressource","Extent1"."tcpft" AS "tcpft","Extent1"."tcpprice" AS "tcpprice","Extent1"."tcpthresholdcoeff" AS "tcpthresholdcoeff","Extent1"."tcpadeli" AS "tcpadeli","Extent1"."tcphprimcode" AS "tcphprimcode","Extent1"."tcprpps" AS "tcprpps","Extent1"."tcphprimid" AS "tcphprimid","Extent1"."datastatus" AS "datastatus","Extent1"."tcpcpamcode" AS "tcpcpamcode","Extent1"."xmin" AS "xmin","Extent2"."mdtid" AS "mdtid","Extent2"."mdccode" AS "mdccode","Extent2"."tcrid" AS "tcrid","Extent2"."equid" AS "equid","Extent2"."mdtaetitle" AS "mdtaetitle","Extent2"."mdtxraydoseunit" AS "mdtxraydoseunit","Extent2"."mdtxraydoseunitoptional" AS "mdtxraydoseunitoptional","Extent2"."mdtisxraydosebympps" AS "mdtisxraydosebympps","Extent2"."mdtipaddress" AS "mdtipaddress","Extent2"."mdtipport" AS "mdtipport","Extent2"."mdtregistration" AS "mdtregistration","Extent2"."mdtpower" AS "mdtpower","Extent2"."mdtpowerunit" AS "mdtpowerunit","Extent2"."mdtbillingid" AS "mdtbillingid","Extent2"."mdtbillingmodalitycode" AS "mdtbillingmodalitycode","Extent2"."mdtreadingonterminaldefaultvalue" AS "mdtreadingonterminaldefaultvalue","Extent2"."xmin" AS "xmin1","Extent3"."equid" AS "equid"
FROM "acstudy"."ttechnicalpackages" AS "Extent1"
INNER JOIN "acadmin"."tmodalities" AS "Extent2" ON "Extent1"."tcpdeviceid"="Extent2"."mdtid"
LEFT OUTER JOIN "acadmin"."tmodalities" AS "Extent3" ON "Extent1"."tcpdeviceid"="Extent3"."mdtid"
WHERE (4="Extent1"."datastatus") AND (4="Extent1"."datastatus")
)
AS "Filter1"
LEFT OUTER JOIN "acadmin"."tequipments" AS "Extent4" ON "Filter1"."equid"="Extent4"."equid"
WHERE "Filter1"."cstid"= (((3)))
) AS "Project1"
ORDER BY "Project1"."tcpprice" ASC

I'll keep you posted.




Urgent : Backend sent unrecognized response type [ reply ]
By: Olivier MATROT on 2013-09-12 08:35
[forum:1020857]
Hello,

I have a problem with prepared transaction under heavy server load.
Maybe it's a problem with the backend configuration. I need help.

The problem is the following :

The PREPARE TRANSACTION statement fails during command.ExecuteBlind(). A System.NotSupportedException is thrown with the following message

"Backend sent unrecognized response type"

I've modified the code to display the actual reponse type.
It is 'Q' or 'P'.

The main problem here is that the transaction is successfully prepared ! it must be rolledback manually which is very painfull.

Any help appreciated.

How often should I invoke Flush when I use NpgsqlCopySerializer ? [ reply ]
By: Alex Kuznetsov on 2013-10-24 14:07
[forum:1020863]
In all the examples I have seen so far Flush() is called immediately after each EdnRow() call, as follows:

npgsqlCopySerializer.EndRow();
npgsqlCopySerializer.Flush();

However, I called Flush() only once, after adding all the rows, and successfully inserted a batch of rows. What is the best practice? How often should I invoke Flush()?





Using Integrated Security with mono [ reply ]
By: Avega A on 2013-10-09 08:47
[forum:1020860]
I'm trying to use Integreated Security with mono. But there is exception occur when I try to access DB:

System.IndexOutOfRangeException
Array index is out of range.
Exception stack trace:
at Npgsql.NpgsqlConnectionStringBuilder.get_UserName () [0x00000] in <filename unknown>:0
at Npgsql.NpgsqlConnector.get_UserName () [0x00000] in <filename unknown>:0
at Npgsql.NpgsqlConnectedState.Startup (Npgsql.NpgsqlConnector context) [0x00000] in <filename unknown>:0
at Npgsql.NpgsqlConnector.Open () [0x00000] in <filename unknown>:0
at Npgsql.NpgsqlConnectorPool.GetPooledConnector (Npgsql.NpgsqlConnection Connection) [0x00000] in <filename unknown>:0

On native windows .Net (without mono) all going well, without any exceptions.

Is there any way to use npgsql+mono+Integrated Security?

Best way to deal with error 23505 duplicate key [ reply ]
By: Pieter Kruger Jr on 2013-09-16 07:41
[forum:1020858]
Hello everyone,

Currently I use the following code to insert a row into a table:

_sqlConnection = new NpgsqlConnection(_connStr);
_sqlConnection.Open();
string insertQuery = @"INSERT INTO ""Bla"" (""b1"", ""b2"") VALUES(" +
"@b1, @b2)";
NpgsqlCommand sqlCommand = new NpgsqlCommand(insertQuery, _sqlConnection);
NpgsqlParameter[] sqlParams = new NpgsqlParameter[]
{
new NpgsqlParameter("@b1",DbType.String),
new NpgsqlParameter("@b2",DbType.String)
}; // end NpgsqlParameter
sqlParams[0].Value = Bla.B1;
sqlParams[1].Value = Bla.B2;
sqlCommand.Parameters.AddRange(sqlParams);
rc = sqlCommand.ExecuteNonQuery();
_sqlConnection.Close();

What would be the best way to pick up Error 23505? Currently I have the above code in a try block with the following catch:

catch (NpgsqlException ex)
{
if (ex.ErrorCode == 23505)
{
// Duplicate
} // end if
} // end catch

Unfortunately, testing on ErrorCode does not work.

What am I missing? :-)

How to add a smallint using NpgsqlCopySerializer? [ reply ]
By: Thomas Higgins on 2013-08-28 19:11
[forum:1020856]
I need to insert a LOT of rows. My table has some smallint fields. Is there a way to use NpgsqlCopySerializer to do the inserts? There is no AddInt16 method. Thank you.

   Older Messages
Powered By FusionForge