SCM

Forum: help

Monitor Forum | Start New Thread Start New Thread
pass parameters into NpgsqlConnection? [ reply ]
By: Brad R on 2007-09-13 02:59
[forum:1002672]
Is there a way to pass parameters into NpgsqlConnection? Would like to be collect username/password/database info dynamically, and use that for the connection.


cross-platform programming [ reply ]
By: Brad R on 2007-09-12 16:52
[forum:1002665]
This may be a little off-topic, so please forgive my ignorance.

Developing a base app with a Postgresql backend with both Windows and Linux in mind. Will extend its functionality in the near future with additional plugins.

In the Windows world, these can be implemented via supplemental DLLs. However, how would one implement these same plugins in the Linux world?

Thanks for any info/links on this.

Using Datasets and Transactions [ reply ]
By: Owen Hartnett on 2007-09-10 21:01
[forum:1002649]
(I originally posted this to the mailing list, but Francisco recommended I post it here, which may be why you're reading it twice. [but there is an update!])

UPDATE: While tracing through the code, it looks like the updatecommands check first for a connector object, failing this, they open up a new connection, so even though I'm sending a transaction, since it's a new connection, it doesn't work as a transaction (at least that's what I think I'm seeing.)

I'm trying to use Npgsql to write back dataset changes using a single transaction, but I can't get it to work correctly.

My code follows. Basically, I'm opening a connection, creating a NpgsqlCommandBuilder and a NpgsqlTransaction and calling BeginTransaction. Then I do the GetChanges/GetCommands routine for all the tables I want, then calling commit. The code updates the database, but transaction support doesn't work. Basically, it seems that Npgsql wants to make it's own connection, and my transaction doesn't get included.

In some SQL server code I got this technique from, it would pass the connection to the CommandBuilder as well as the transaction. In Npgsql, there is no connection property on the NpgsqlCommandBuilder object.

I'm not sure where I should go now. Any ideas?


-Owen

Option Explicit On
Imports System.Windows.Forms
Imports npgsql
Imports System.Xml.Serialization
Imports System.IO
Imports System.Collections.Generic
Imports System.Configuration
' Note: some controls, in the forms designer, cover other controls, i.e. CommUsageCB covers styleCB
Public Class ParcelDisplayFrm

Public Sub WriteAllData()
Dim trans As NpgsqlTransaction = Nothing
Dim cmd As NpgsqlCommandBuilder
Dim i As Integer
Dim success As Boolean

Try
If Not statusReadOnly Then
i = vbCancel

dt = full_DataSet.Tables(currentSchema & ".parcel")
m_SqlConnection.Open()
' create a transaction for the rest of all the changes

trans = m_SqlConnection.BeginTransaction

cmd = New NpgsqlCommandBuilder(parcel_DataAdapter)

' other code has cmd.connection = m_SqlConnection
' which won't work in Npgsql

Dim parcelchanges As DataTable = dt.GetChanges(DataRowState.Modified)

If parcelchanges IsNot Nothing Then
parcel_DataAdapter.UpdateCommand = cmd.GetUpdateCommand(dt.Rows(0))
parcel_DataAdapter.UpdateCommand.Transaction = trans

parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Deleted)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.DeleteCommand = cmd.GetDeleteCommand(dt.Rows(0))
parcel_DataAdapter.DeleteCommand.Transaction = trans

parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Added)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.InsertCommand = cmd.GetInsertCommand(dt.Rows(0))

parcel_DataAdapter.InsertCommand.Transaction = trans

parcel_DataAdapter.Update(parcelchanges)
End If

' accounts table
cmd = New NpgsqlCommandBuilder(accts_DataAdapter)
dt = full_DataSet.Tables(currentSchema & ".accounts")
Dim acctchanges As DataTable = dt.GetChanges(DataRowState.Modified)

If acctchanges IsNot Nothing Then
accts_DataAdapter.UpdateCommand = cmd.GetUpdateCommand(dt.Rows(0))
accts_DataAdapter.UpdateCommand.Transaction = trans

accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Deleted)
If acctchanges IsNot Nothing Then
accts_DataAdapter.DeleteCommand = cmd.GetDeleteCommand(dt.Rows(0))
accts_DataAdapter.DeleteCommand.Transaction = trans

accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Added)
If acctchanges IsNot Nothing Then
accts_DataAdapter.InsertCommand = cmd.GetInsertCommand(dt.Rows(0))

accts_DataAdapter.InsertCommand.Transaction = trans

accts_DataAdapter.Update(acctchanges)
End If

' do for every building
dt = full_DataSet.Tables(currentSchema & ".bldg")
If dt.Rows.Count > 0 Then
If dirtySketch Then
For i = currentBuilding To howManyBuildings - 1
returnSketchToDatabase(dt.Rows(0).Item("maplot"), i, trans, Me)
Next i
End If
cmd = New NpgsqlCommandBuilder(bldg_DataAdapter)

' add modified dates
addModDates(dt, "modified")


'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim bldgchanges As DataTable = dt.GetChanges(DataRowState.Deleted)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.DeleteCommand = cmd.GetDeleteCommand(dt.Rows(0))
bldg_DataAdapter.DeleteCommand.Transaction = trans

bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Modified)
If bldgchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
bldg_DataAdapter.UpdateCommand = cmd.GetUpdateCommand(dt.Rows(j))
bldg_DataAdapter.UpdateCommand.Transaction = trans

bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Added)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.InsertCommand = cmd.GetInsertCommand(dt.Rows(0))
bldg_DataAdapter.InsertCommand.Transaction = trans

bldg_DataAdapter.Update(bldgchanges)
End If
End If

dt = full_DataSet.Tables(currentSchema & ".commcost")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(commbldg_DataAdapter)

'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim commBldgChanges As DataTable
commBldgChanges = dt.GetChanges(DataRowState.Deleted)
If commBldgChanges IsNot Nothing Then
commbldg_DataAdapter.DeleteCommand = cmd.GetDeleteCommand(dt.Rows(0))
commbldg_DataAdapter.DeleteCommand.Transaction = trans

commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Modified)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.UpdateCommand = cmd.GetUpdateCommand(dt.Rows(j))
commbldg_DataAdapter.UpdateCommand.Transaction = trans

commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Added)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.InsertCommand = cmd.GetInsertCommand(dt.Rows(j))
commbldg_DataAdapter.InsertCommand.Transaction = trans

commbldg_DataAdapter.Update(commBldgChanges)
End If
End If

dt = full_DataSet.Tables(currentSchema & ".outbuildings")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(outbldg_DataAdapter)

For i = 0 To dt.Rows.Count - 1
If dt.Rows(i).RowState = DataRowState.Added Then
dt.Rows(i).Item("MapLot") = full_DataSet.Tables(currentSchema & ".parcel").Rows(0).Item("MapLot")
End If
Next i
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim outchanges As DataTable
outchanges = dt.GetChanges(DataRowState.Deleted)
If outchanges IsNot Nothing Then
outbldg_DataAdapter.DeleteCommand = cmd.GetDeleteCommand(dt.Rows(0))
outbldg_DataAdapter.DeleteCommand.Transaction = trans

outbldg_DataAdapter.Update(outchanges)
End If
outchanges = dt.GetChanges(DataRowState.Modified)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.UpdateCommand = cmd.GetUpdateCommand(dt.Rows(j))
outbldg_DataAdapter.UpdateCommand.Transaction = trans

outbldg_DataAdapter.Update(outchanges)

End If
outchanges = dt.GetChanges(DataRowState.Added)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.InsertCommand = cmd.GetInsertCommand(dt.Rows(j))
outbldg_DataAdapter.InsertCommand.Transaction = trans

outbldg_DataAdapter.Update(outchanges)
End If
End If
' write changes to sales tables
dt = full_DataSet.Tables(currentSchema & ".sales")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(sales_DataAdapter)

'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesChanges As DataTable
salesChanges = dt.GetChanges(DataRowState.Deleted)
If salesChanges IsNot Nothing Then
sales_DataAdapter.DeleteCommand = cmd.GetDeleteCommand(dt.Rows(0))
sales_DataAdapter.DeleteCommand.Transaction = trans

sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Modified)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.UpdateCommand = cmd.GetUpdateCommand(dt.Rows(j))
sales_DataAdapter.UpdateCommand.Transaction = trans

sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Added)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.InsertCommand = cmd.GetInsertCommand(dt.Rows(j))
sales_DataAdapter.InsertCommand.Transaction = trans

sales_DataAdapter.Update(salesChanges)
End If
End If
' write changes to sales overflow table
dt = full_DataSet.Tables(currentSchema & ".salesovflowtype")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(salesOF_DataAdapter)

'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesOFChanges As DataTable
salesOFChanges = dt.GetChanges(DataRowState.Deleted)
If salesOFChanges IsNot Nothing Then
salesOF_DataAdapter.DeleteCommand = cmd.GetDeleteCommand(dt.Rows(0))
salesOF_DataAdapter.DeleteCommand.Transaction = trans

salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Modified)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.UpdateCommand = cmd.GetUpdateCommand(dt.Rows(j))
salesOF_DataAdapter.UpdateCommand.Transaction = trans

salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Added)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.InsertCommand = cmd.GetInsertCommand(dt.Rows(j))
salesOF_DataAdapter.InsertCommand.Transaction = trans

salesOF_DataAdapter.Update(salesOFChanges)
End If
End If


trans.Commit()
m_SqlConnection.Close()
dirtySketch = False
BrowserPanel.Refresh()
End If
Catch ex As Exception
MsgBox(" error on writing data " & ex.Message, MsgBoxStyle.AbortRetryIgnore)
If trans IsNot Nothing Then trans.Rollback()
If m_SqlConnection.State = ConnectionState.Open Then m_SqlConnection.Close()
End Try
End Sub

End Class

Syncronous notification problem [ reply ]
By: Clive Simmens on 2007-09-10 19:32
[forum:1002648]
Hi Francisco,
I very much appreciate the Synchronous Notification in Npgsql2 but have found that it sometimes fails to work. Particularly noticeable when I use Npgsql2 in a Windows service application.

The problem is in the function ProcessSereverMessages(). After the line: this.connector._notificationAutoResetEvent.Set(); if you add a line: Thread.Sleep(0); to gives the connection thread a chance to pick up on the event the Syncronous notifications works fine.


Connecting to PostgreSQL server with Mono [ reply ]
By: Andreas Tille on 2007-09-07 09:33
[forum:1002634]
Hi,

it is my first shot using Mono and I failed to get the example from
http://www.mono-project.de/wiki/keyword/PostgreSQL/

working. The reason is obviousely that whatever I tried NpgsqlConnection tries to use password authentication but I have configured my system that ident authentication is used by default and that's why I just left out the PASSWORD parameter from the connection string which leads to a failing connection because of a missing password. Is there a working example using ident authentication?

I'm using Debian GNU/Linux testing running postgresql 8.2 and mono 1.2. Could anybody enlight me how to connect to a database where users have no password set because always ident authentication is used on local host.

Kind regards

Andreas.

--
http://fam-tille.de

Problem inserting values into a table [ reply ]
By: Marcos David Marin Amador on 2007-09-05 00:49
[forum:1002629]
Hello, I'm having this strange error in this part of my code

http://monoport.com/4461

So, say in the TextBox I enter "Adam", I get this error: "ERROR: 42703: column "adam" does not exist"

This happens for whatever string I use, it seems to look for the column called with the string I used when the string is the value! not the column, right?

I tried being more explicit using "insert into amigo(nombre, id) values(:nombre, :id)"
and same thing happens...

Is this some weird bug or am I being really stupid?

BTW, I'm using C# on MS .NET 2.0 running on Windows XP SP2 with Npgsql 1.0.

Error With Large Objects/Transactions [ reply ]
By: Rob Walters on 2007-08-25 14:06
[forum:1002604]
I'm trying to handle errors when dealing with large objects. I have a piece of code that attempts to load a large object, as outlined in the manual. If an error occurs such as an unrecognised OID, the transaction is rolled back and the connection closed, as below.

catch (Exception)
{
t.Rollback();
t.Dispose();
if (this.databaseConnection.State != ConnectionState.Closed)
{
databaseConnection.Close();
}
throw new ImageNotAvailableException();
}

When unit testing this routine, directly after running the above test, another routine is meant to successfully save an image.

The second routine intermittently fails, reporting:
"Object reference not set to instance of object" within FastPath.GetID
OR
"This stream does not support seek operations", with stack trace exactly as reported by matrix66677.

I've created a new routine to try to debug this as follows:

ImageManager manager = new ImageManager(conn);
try
{
Image unavailable = manager.Load(1234);
}
catch (Exception)
{
int oid = manager.Save(testImage);
Image restoredFromDB = manager.Load(oid);
restoredFromDB.Save("C:\\Projects\\UnitTest\\imagemanageroutput.bmp");
manager.Delete(oid);
}

This routine still fails, either with one of the above 2 errors, or with the following error, despite the fact that ImageManager clearly rolls back the transaction when an error occurs.

ImageManager manager = new ImageManager(conn);
try
{
Image unavailable = manager.Load(1234);
}
catch (Exception)
{
int oid = manager.Save(testImage);
Image restoredFromDB = manager.Load(oid);
restoredFromDB.Save("C:\\Projects\\UnitTest\\imagemanageroutput.bmp");
manager.Delete(oid);
}

Any help would be gratefully received!!!
Many thanks

Rob

Session State Provider [ reply ]
By: Tim Schwallie on 2007-08-11 19:30
[forum:1002584]
Hey folks, a little while back I wrote and posted some code on using PostgreSQL for a Session State Provider.
see:
http://www.codeproject.com/useritems/SampleSessionProviders.asp

I'm sure with a little touching up it could be added like the other providers.

Any thoughts?

DbProviderFactories.GetFactory [ reply ]
By: Tim Schwallie on 2007-08-12 04:53
[forum:1002585]
fxjr was kind and posted a sample of the DbProviderFactory configuration setting in his blog (fxjr.blogspot.com).

Just remember, as he makes builds and changes the assembly version numbers, we may need to change the configuration setting.

Below, is my latest that seems to work.

<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=1.97.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
</DbProviderFactories>
</system.data>

This is only part of the configuration file.

Perhaps, the usage for file version numbers and assembly version numbers can help to clean this up a little. Until then, watch the version numbers.

Npgsql2 and ProviderFactory [ reply ]
By: Weronika R. on 2007-08-07 18:32
[forum:1002572]
I managed adding Npgsql2 libraries as ProviderFactory - it works fine. But I still can't add it to .Net as a Data Source Provider - I mean when I want to choose a Data Source while creating new database connection - Npgsql is just not there in a listbox. Is there any way to do that? Or do I still have to do all the connections and datasets manually?

Timeout while getting a connection from pool [ reply ]
By: Andrus Moor on 2007-07-25 17:56
[forum:1002544]
I have WinForms application with NHibernate.

After opening some forms error

Timeout while getting a connection from pool.

occurs in Connection Open() method.

How to fix ?

StackTrace:
at Npgsql.NpgsqlConnectorPool.RequestPooledConnector(NpgsqlConnection Connection) in c:\Desenvolvimento\Npgsql\src\Npgsql\NpgsqlConnectorPool.cs:line 191
at Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection) in c:\Desenvolvimento\Npgsql\src\Npgsql\NpgsqlConnectorPool.cs:line 151
at Npgsql.NpgsqlConnection.Open() in c:\Desenvolvimento\Npgsql\src\Npgsql\NpgsqlConnection.cs:line 466
........

Npgsql and Visual Basic .NET 2005 [ reply ]
By: Weasel Weasel on 2007-07-16 11:13
[forum:1002531]
Hi!

I use VB.NET 2005 I downloaded npgsql and added the dll to VB.NET 2005 all ok, the controls were added to toolbox im able to connect to database work fine.

The problem is, i dont know how to use npgsql, the examples are all in C# im with VB, anyone that can give me a simple example in VB.NET 2005 to run a query and getting some result from a database ?

Thanks in advance!


NpgsqlCommand is trying to help too much [ reply ]
By: Unga Wunga on 2007-07-25 13:25
[forum:1002540]
I've worked around this issue, but think it's odd that the NpgsqlCommand class does this.

If you set the CommandType to StoredProcedure, it seems that it'll append a "select * from " to your statement. Doing so broke all my code when I switched to using Npgsql from Odbc. Had to switch all my commandTypes to Text to prevent this.

While I thank you for creating this project, please be careful trying to help the developers too much.

Thanks

This stream does not support seek operations [ reply ]
By: matrix 666777 on 2007-06-25 19:11
[forum:1002471]
I'm using version 1.0 of PostgreSQL with an ASP.NET 2.0 application.
My code now is very simple, using a DataAdapter to Fill a typed dataset, but I'm getting this error. Below is the stack trace. Any idea what is causing this...

Thanx


[NotSupportedException: This stream does not support seek operations.]
System.Net.Sockets.NetworkStream.Seek(Int64 offset, SeekOrigin origin) +101
System.IO.BufferedStream.FlushRead() +45
System.IO.BufferedStream.WriteByte(Byte value) +78
Npgsql.NpgsqlQuery.WriteToStream(Stream outputStream, Encoding encoding) +227
Npgsql.NpgsqlReadyState.Query(NpgsqlConnector context, NpgsqlCommand command) +273
Npgsql.NpgsqlConnector.Query(NpgsqlCommand queryCommand) +78
Npgsql.NpgsqlCommand.ExecuteCommand() +302
Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) +185
Npgsql.NpgsqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior cb) +162
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +233
System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +568
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +193
Ipro.DataAccess.RequestServiceDataAccess.GetPendingRequestForServices() in D:\Projects\IPRO\source\Ipro.DataAccess\RequestServiceDataAccess.cs:91
Ipro.Business.RequestForService.GetPendingRequestForServices() in D:\Projects\IPRO\source\Ipro.Business\RequestForService.cs:476


AuthenticationMD5Password supported for now. [ reply ]
By: Evandro Pasquali on 2007-07-17 17:43
[forum:1002536]
string conection:
"SERVER = localhost; PORT = 5432; DATABASE = teste; UID = postgres; PWD = xxxx";

What cause this problem?


Npgsql.NpgsqlException:
Only AuthenticationClearTextPassword and AuthenticationMD5Password supported for now. Received: 4
em Npgsql.NpgsqlConnector.CheckErrors() na c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:linha 359
em Npgsql.NpgsqlConnector.Open() na c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:linha 707
em Npgsql.NpgsqlConnectorPool.GetPooledConnector(NpgsqlConnection Connection) na c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnectorPool.cs:linha 349
em Npgsql.NpgsqlConnectorPool.RequestPooledConnectorInternal(NpgsqlConnection Connection) na c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnectorPool.cs:linha 215
em Npgsql.NpgsqlConnectorPool.RequestPooledConnector(NpgsqlConnection Connection) na c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnectorPool.cs:linha 171
em Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection) na c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnectorPool.cs:linha 151
em Npgsql.NpgsqlConnection.Open() na c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnection.cs:linha 466
em _Default.Page_Load(Object sender, EventArgs e) na d:\web\localuser\ejcasistemas\www\webservices\licenca_conciliacao\Default.aspx.cs:linha 35

thanks.

Queue empty [ reply ]
By: Andrus Moor on 2007-06-30 19:22
[forum:1002483]
I'm running WinForms application from Visual C# 2005 Express IDE.

After certain transaction when application is idle about 5 seconds exception

Queue empty

appears.
How to fix this ?

Exception detail:

System.InvalidOperationException was unhandled by user code
Message="Queue empty."
Source="mscorlib"
StackTrace:
at System.Collections.Queue.Dequeue()
at Npgsql.NpgsqlConnectorPool.TimerElapsedHandler(Object sender, ElapsedEventArgs e) in c:\Desenvolvimento\Npgsql\src\Npgsql\NpgsqlConnectorPool.cs:line 94
at System.Timers.Timer.MyTimerCallback(Object state)


Part of debug log.
Error occurs at 22:12:26

30.06.2007 22:12:11 3596 Debug Entering PGUtil.WriteString()
30.06.2007 22:12:11 3596 Debug String written: unlisten *.
30.06.2007 22:12:11 3596 Debug Entering NpgsqlState.ProcessBackendResponses()
30.06.2007 22:12:11 3596 Debug Entering PGUtil.ReadString()
30.06.2007 22:12:11 3596 Debug String read: UNLISTEN.
30.06.2007 22:12:11 3596 Debug CompletedResponse message from Server: UNLISTEN.
30.06.2007 22:12:11 3596 Debug ReadyForQuery message received from server.
30.06.2007 22:12:11 3596 Debug Entering PGUtil.ReadString()
30.06.2007 22:12:11 3596 Debug String read: I.
30.06.2007 22:12:11 3596 Debug Entering NpgsqlState.ChangeState()
30.06.2007 22:12:26 3596 Debug Entering NpgsqlReadyState.Close()
30.06.2007 22:12:26 3596 Debug Get NpgsqlClosedState.Instance
30.06.2007 22:12:26 3596 Debug Entering NpgsqlState.ChangeState()
30.06.2007 22:14:41 3596 Debug NpgsqlConnection leaking! Fixing pool count...
30.06.2007 22:14:41 3596 Debug NpgsqlConnection leaking! Fixing pool count...
30.06.2007 22:14:41 3596 Debug NpgsqlConnection leaking! Fixing pool count...
30.06.2007 22:14:41 3596 Debug NpgsqlConnection leaking! Fixing pool count...
30.06.2007 22:14:41 3596 Debug NpgsqlConnection leaking! Fixing pool count...
30.06.2007 22:14:41 3596 Debug NpgsqlConnection leaking! Fixing pool count...

Dataset with Multiple Selects [ reply ]
By: Fernando Grijalba on 2007-07-12 14:51
[forum:1002511]
On SQLServer you can create a procedure that can return the result of multiple selects as tables of a dataset.

Is this possible with PosgtreSQL functions?

Thank you for your help.

JFercan

DataTables with synchronous notifications [ reply ]
By: Dan Sherwin on 2007-07-10 00:06
[forum:1002501]
I have a simple piece of code below that creates a connection, issues a listen command, and a method to handle the notification event. in the method, I am trying to reload the data in my data table, and it hangs at CheckConnectionState(). I know the reason why this is happening is that the notification handler is being call in a different thread than what the connection was made. Any recommendations on how to handle this?

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Threading;
using Npgsql;

namespace NpgsqlNotificationExample
{
class Program
{
private static NpgsqlConnection conn = new NpgsqlConnection("SERVER=localhost;DATABASE=npgsql_test;Userid=postgres;SyncNotification=true");
private static NpgsqlDataAdapter dataadapter = new NpgsqlDataAdapter("select * from test_table", conn);
private static DataTable datatable = new DataTable("test_table");

static void Main(string[] args)
{
NpgsqlEventLog.Level = LogLevel.Debug;
NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";
NpgsqlEventLog.EchoMessages = true;
conn.Notification += new NotificationEventHandler(NotificationSupportHelper);
Console.WriteLine("Opening Connection");
conn.Open();
NpgsqlCommand listencmd = new NpgsqlCommand("listen test_table", conn);
listencmd.ExecuteNonQuery();
dataadapter.Fill(datatable);
DataRow found = datatable.Rows[0];
}
private static void NotificationSupportHelper(Object sender, NpgsqlNotificationEventArgs e)
{
Console.WriteLine("NotificationSupportHelper");
Console.WriteLine("Filling Table");
dataadapter.Fill(datatable);
Console.WriteLine("Retrieving Row");
DataRow found = datatable.Rows[0];
}
}
}

semicolon in password [ reply ]
By: Ben Sagal on 2007-07-08 00:30
[forum:1002495]
How would i put a semicolon in the passwords in a connection string for an npgsql connection.

Thank you

Ben

Npgsql.NpgsqlException: ERROR: 42804 [ reply ]
By: Francisco Guerrero on 2007-06-27 01:42
[forum:1002474]
Hi:

I'm using NHibernate to persist to a Postgresql database using the Npgsql driver.

I'm having problems when I'm trying to persist a decimal (c#) type into money (postgresql).

This is my debug log:

Npgsql.NpgsqlException:
column "Price" is of type money but expression is of type numeric
Severity: ERROR
Code: 42804
Hint: You will need to rewrite or cast the expression.
at Npgsql.NpgsqlConnector.CheckErrors() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:line 359
at Npgsql.NpgsqlConnector.CheckErrorsAndNotifications() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:line 410
at Npgsql.NpgsqlCommand.ExecuteCommand() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlCommand.cs:line 1479
at Npgsql.NpgsqlCommand.ExecuteNonQuery() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlCommand.cs:line 460
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
2007-06-26 18:12:38,250 [13] NHibernate.Util.ADOExceptionReporter.LogExceptions(:0) - Npgsql.NpgsqlException:
column "Price" is of type money but expression is of type numeric
Severity: ERROR
Code: 42804
Hint: You will need to rewrite or cast the expression.
at Npgsql.NpgsqlConnector.CheckErrors() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:line 359
at Npgsql.NpgsqlConnector.CheckErrorsAndNotifications() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:line 410
at Npgsql.NpgsqlCommand.ExecuteCommand() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlCommand.cs:line 1479
at Npgsql.NpgsqlCommand.ExecuteNonQuery() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlCommand.cs:line 460
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)

2007-06-26 18:12:38,250 [13] NHibernate.Util.ADOExceptionReporter.LogExceptions(:0) - ERROR: 42804: column "Price" is of type money but expression is of type numeric

2007-06-26 18:12:38,250 [13] NHibernate.Impl.SessionImpl.Execute(:0) - could not synchronize database state with session

NHibernate.ADOException: could not insert: [WebSoft.Core.Business.TReport#10][SQL: INSERT INTO "TReport" ("CCounty", "EscrowNumber", "Price", "ReportFile", "Date", "CCaliforniaTaxData", "CAin", "ImageFile", "Processed", "CReport") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)] ---> Npgsql.NpgsqlException:
column "Price" is of type money but expression is of type numeric
Severity: ERROR
Code: 42804
Hint: You will need to rewrite or cast the expression.
at Npgsql.NpgsqlConnector.CheckErrors() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:line 359
at Npgsql.NpgsqlConnector.CheckErrorsAndNotifications() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlConnector.cs:line 410
at Npgsql.NpgsqlCommand.ExecuteCommand() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlCommand.cs:line 1479
at Npgsql.NpgsqlCommand.ExecuteNonQuery() in c:\Desenvolvimento\NpgsqlBuildOficial2.0\src\Npgsql\NpgsqlCommand.cs:line 460
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledInsertion.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()

I'm not sure it this is a npgsql or postgresql or nhibernate bug.

Thanks for all your help

How to profile [ reply ]
By: Andrus Moor on 2007-06-22 15:50
[forum:1002461]
How to force driver to write all command sent to server and their durations to text file ?

I tried
NpgsqlEventLog.Level = LogLevel.Debug;

but result file has too many unnessecary information.

MS SQL net data provider has profiling capabilities.

How to profile with PostgreSQL in client side ?

problem with disconnections [ reply ]
By: Alejandro Gasca on 2007-06-12 21:00
[forum:1002450]
hi.

I have this problem: if my connection broke, o get momentary broken, npgsq trhow an "broken connection" error...

the problem is that (the pool?) open fine the connection, even if a desconection occurs, but when i execute a command the "broken connection" raises... So: the connection is "open" but any operation to the database raises an error.

¿how can i diagnose the connection broken state?
¿how can i "reconnect" the pool?

thanks in advance,

Alejandro

Npgsql2 binaries for download [ reply ]
By: Day Book on 2007-06-06 11:56
[forum:1002425]
Hi,
I'm evaluating PostgreSQL for use in my website. I'd like to try out Npgsql2, since I need support for ADO.Net 2.0 generic coding.

Is there a web location I can download the binaries from?

Tried to checkout the files from CVS, but just get these:
cvs checkout: Updating npgsql
cvs checkout: Updating npgsql/CVS.adm
U npgsql/CVS.adm/MstData.stp
U npgsql/CVS.adm/UsgData.stp
U npgsql/CVS.adm/prjprm.stp
Did I miss out something here? This is the first time I'm using CVS.

Also, when do we expect to have Npgsql2 ready for production use?

IOException when using Sync Notifications [ reply ]
By: Scott Scecina on 2007-06-06 02:41
[forum:1002424]
I'm using syncronous notifications in a windows service that runs 24x7. However, the postgres server is running on a server that (for other reasons than postgres) is rebooted nightly. As a result the connection used for the Poll() is dropped by the server and the notification monitoring thread throws an unhandled System.IO.IOException exception, which I cannot figure out a way to handle... Here's the exact Exception:

System.IO.IOException was unhandled
Message="Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host."
Source="System"
StackTrace:
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.ReadByte()
at Npgsql.NpgsqlState.ProcessBackendResponses_Ver_3(NpgsqlConnector context)
at Npgsql.NpgsqlState.ProcessBackendResponses(NpgsqlConnector context)
at Npgsql.NpgsqlConnector.NpgsqlContextHolder.ProcessServerMessages()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()


As a result of this the windows service fails, and there's an obscure .NET error msg written to the application log.

I've set the service to restart upon failure, but I think this is really ugly.

Is there any way to make the notification monitoring thread attempt to re-establish the connection? or have it fire an event that could be monitored by the code using notifications?

I'm happy to try to implement the solution in the source code, but would like some input prior to heading down that path. I'm hoping I don't need to :)

getting postgres backend server pid [ reply ]
By: Arman Fah on 2007-06-04 13:50
[forum:1002418]
Hello,

My application handles the NotificationEvent sent by the postgres backend server, when a user inserts a record in my table. I want to implement that, the application ignore those messages which was sent by itself. I get in the handlers eventargs parameter, which contains a pid value, and I thinked I could compare this pid value with my application's pid, but I don't know how to get this from my code?

VS 2005 designer workaround [ reply ]
By: Tom Hirt on 2007-06-01 17:33
[forum:1002400]
I'm new to VS2005 and tried to use the same DSN ODCB that I've been using for my Access application. But after adding the new connection, no tables are listing.

So I read as a fix to use Npgsql (.net). Directions call to place the dll in the project folder. But I really don't see an option for .net Postgress drives when adding a connection. I see the ODBC and OLE DB (which I can't get to work), but no native postress .net.

What I'm I doing wrong or missing to use this? Also, I'm concerned about the following thread that states there is no VS2005 designer integration.

http://pgfoundry.org/forum/forum.php?thread_id=1233&forum_id=519

As someone new to VS2005, how do I define my connection to Postgress?

Multiple Queries [ reply ]
By: David Burnett on 2007-06-02 13:10
[forum:1002408]
Can anyone show me how to run a long list of SQL statements and get all select statements returned?

example:

Update this;
delete something from somewhere;
select count(something) from somewhere;
update something else;
create TempTable with something in it;
select something from the temp table;

I can only get a pure select query to work, without other stuff too.

example: select * from users;

I'm using this type of logic right now:

dbconn = New Npgsql.NpgsqlConnection(sConn)
dbcommand = New Npgsql.NpgsqlCommand(sqlx, dbconn)
dbadapter = New pgsql.NpgsqlDataAdapter(dbcommand)
dbadapter.Fill(ds)

(( ds is a dataset ))

In SQL Server, I have no trouble using this approach to do whatever I need and pull back all the select statements at the same time...

Well, any help is greatly appreciated.

Thanks,

David

Unable to retrieve schema information... [ reply ]
By: bala panch on 2007-05-24 12:05
[forum:1002386]
Hi,

I am getting an error "Unable to retrieve schema information from the database table." when i am trying to fill the TableMappings for an dataadapter. I am using VS 2005 and am able to drop NpgsqlDataAdapter, NpgsqlCommand, NpgsqlCOmmandBuilder and NpgsqlCOnnection from the Data tab of VS 2005.

Thanx in advance.

how to properly termitate the connection [ reply ]
By: Alejandro Gasca on 2007-05-26 05:17
[forum:1002387]
Hi.

When my application ends, the postgres (8.2.4) log says:

SSL SYSCALL error: Connection reset by peer
could not receive data from client: Connection reset by peer
unexpected EOF on client connection

how can i gracefully terminate the connection?

Thanks,

Alejandro

SSL-Connection needs UserProfile on Windows? [ reply ]
By: Erwin Thaler on 2007-05-15 12:33
[forum:1002366]
NPGSQL, version 1.0.0.0, has a problem when running in ASP.NET (MS.NET 1.1) on a Windows 2003 Server-System and using SSL-Connection.

If we use Internet Information Service on Windows 2003 Server, than we can use application-pools for our web-applications.
In an application-pool we can set the useraccount for the process. Defaultuser is NetworkService.

If we try to use another Process-User than NetworkService, for example a local user "xxx", NPGSQL throws the following error:

[IOException: The authentication or decryption has failed.]
Mono.Security.Protocol.Tls.SslClientStream.NegotiateHandshake() +417
Mono.Security.Protocol.Tls.SslClientStream.BeginWrite(Byte[] buffer,
Int32 offset, Int32 count, AsyncCallback callback, Object state) +151

But when we start notepad.exe under this local user "xxx", the web application succeeds!!!

We think that the reason is that "notepad.exe" loads the user profile of user "xxx" and NPGSQL (or Mono.Security.dll) needs a loaded UserProfile.

This is a big problem, because in web applications we cannot load the user profile for any impersonated user and ASP.NET dont load user profile automatical because of performance.

NPGSQL 0.7.1 succeeds under the same conditions!

Is there a way to use NPGSQL with SSL-Connections in web applications on a Windows 2003 Server?

dataTable UpdateMethode [ reply ]
By: chris mo on 2007-05-14 13:13
[forum:1002364]
Hi,

ich habe eine DB PostgreSQL und will auf diese von C# aus zugreifen. Funktioniert alles gut. Die ganze Programmlogik sollte nur in C# passieren - sprich:
ich hole mir eine Tabelle (DataTable Objekt) arbeite mit ihr in C# und schreib zum Schluss die Veränderungen wieder in die DB.

Dazu habe ich die Methoden Query und Update geschrieben:
C#-Code:
public DataTable Query(string Query)
{
data = new DataTable();
if (conn != null)
{
da = new NpgsqlDataAdapter(Query,
conn);
da.Fill(data);
}
return data;
}

public void Update(DataTable incdata)
{
DataTable changes =
incdata.GetChanges();
try
{
da.Update(changes);
data.AcceptChanges();
}
catch
{
throw new Exception("Es wurden
keine Änderungen vorgenommen!");
}
}


Die Methode Query funktioniert. Bei der Update krieg aber immer die Exception, da die Veränderung ungültig wäre.
Bsp 1:
C#-Code:
public void AddSet(npgsql npgsql)
{
DataTable daTable = new DataTable();
daTable = npgsql.Query("select * from
table");
DataRow row = daTable.NewRow();
row ["columnl"] = 11;
daTable.Rows.Add(row);
npgsql.Update(daTable);
}


Wenn ich das ausführlich mache funktioniert es.
mit Beispiel 2:
C#-Code:
public void AddSetAusf()
{
NpgsqlConnection conn = new
NpgsqlConnection (" ... ");
conn.Open();
DataTable daTable = new DataTable();
NpgsqlDataAdapter da = new
NpgsqlDataAdapter ("select zahl from
Table", conn);
da.InsertCommand = new
NpgsqlCommand("insert into
Table(column)" + " values (:11)",
conn);
// den Insert-Befehl zusammenbauen
da.InsertCommand = new
NpgsqlCommand("insert into
Table(column) " + "values (:11)",
conn);
da.InsertCommand.Parameters.Add(new
NpgsqlParameter("11", DbType.Int16));

// die Parameter zusammmensetzen

da.InsertCommand.Parameters[0].Direction
= ParameterDirection.Input;

da.InsertCommand.Parameters[0].SourceColumn
= "column";

da.Fill(daTable);
DataRow row = daTable.NewRow();
row ["column"] = 11;
daTable.Rows.Add(row);

DataTable daTable2 =
daTable.GetChanges();
da.Update(daTable2);
daTable.AcceptChanges();
}


Ich würde aber gerne in C# ohne die spezifischen Tabellenparameter arbeiten (falls in der Tabelle mal etwas verändert wird...). Gibt es ein einfache Lösung die "DataTable Changes" in die DB einzufügen (Bsp. 1) und wie müsste ich die Methose Update umschreiben,
oder muss ich dass immer so ausführlich machen (Bsp.2) ?????

Danke für die Hilfe!
grüsse!
chritian

Newer Messages  Older Messages
Powered By FusionForge