SCM

Forum: help

Monitor Forum | Start New Thread Start New Thread
Exception when Closing Connection [ reply ]
By: David Leaver on 2011-02-20 20:28
[forum:1013946]
If your connection is in the Ready state and the stream is actually broken NpgsqlReadyState.Close will throw an exception when trying to write to the stream.

Patch to fix it here:
http://dl.dropbox.com/u/29953/NpgsqlReadyState.patch

NpgsqlConnector.CancelRequest leaks Connector [ reply ]
By: David Leaver on 2011-02-17 22:59
[forum:1013938]
I've been debugging some issues we are seeing with Npgsql2 and found a small issue.

NpgsqlConnector.CancelRequest creates a connector to cancel the running request, but does not close it.

I've created a patch that corrects this (using a try/finally block as NpgsqlConnector is not IDisposable)
http://dl.dropbox.com/u/29953/NpgsqlConnector.patch

If this could be reviewed and applied to the main codebase please :-)

Cheers,
Dave.

Parameterized queries with text [ reply ]
By: Ivan V. on 2011-02-15 23:41
[forum:1013925]
Hello,

OK, I have been looking into this and I cannot get it to work. I have a simple table with two columns: token_id Text, xml_settings Xml. I am trying a simple query
SELECT xml_settings FROM settings_table WHERE token_id = :token_id

NpgsqlParameter parameter = new NpgsqlParameter("token_id", "abcd1234");

When the query is executed I get the error
ERROR: 42601: syntax error at or near ":"

I tried using the '@' instead of the ':', no luck. I am using Npgsql 2.0.10 (I already tried the latest release, still same problem) with PostgresSQL 8.3 (I also tried with version 9.+, still same error). I assume I am missing something basic since I don't think there such a bug in the release. Can anyone help?

Thanks!

How to insert & retrieve a blob with npgsql ? [ reply ]
By: jm nicolas on 2010-12-01 10:34
[forum:1006857]
Hello,

could someone provide me with a basic example of how to insert and retrieve a blob with npgsql ?

I found nothing in the examples on NPGSQL page, nothing relevant on google and nothing on this forum !

I managed to insert a file as a byte array in my database (column type = bytea), but I can't retrieve it (with GetBytes ?).
I don't even know if it's the right thing to do.

Thanks for your help :-)

code question [ reply ]
By: Stephen Rich on 2010-12-22 20:35
[forum:1007047]
I am trying to create a base class for database access in application.

It will be used for all classes that CRUD to my postgresql server.

I wanted to create an executecommand method.

internal void executecommand(Npgsql.NpgsqlParameterCollection theparams, string thequery)


This method is basically passed a query and a set of parameters. Basically so I do not have to write all of that extra code for opening and closing the same connection over and over. I understand there will be performance issues but I am not concerned with that now.

When I try to declare a set of parameters to pass to the method.

Npgsql.NpgsqlParameterCollection theparams = new Npgsql.NpgsqlParameterCollection;

I get this error
'Npgsql.NpgsqlParameterCollection' has no constructors defined

Will I be able to do this? is this even good practice. What would you do?




this is the base class I defined


using System;
using System.Collections.Generic;
using System.Text;

namespace appName
{
public abstract class clsBaseDB
{

internal string theconn_string;

public abstract void save();

public abstract void clear();

public void setconn(string conn_string)
{
theconn_string = conn_string;
}
internal void executecommand(Npgsql.NpgsqlParameterCollection theparams, string thequery)
{
Npgsql.NpgsqlConnection thecn = new Npgsql.NpgsqlConnection();
thecn.ConnectionString = theconn_string;
thecn.Open();

Npgsql.NpgsqlCommand dbcmd = thecn.CreateCommand();
dbcmd.Parameters.Add(theparams);
dbcmd.ExecuteNonQuery();
dbcmd.Dispose();
thecn.Close();
thecn.Dispose();


}
}
}

EF error in connection string [ reply ]
By: Navid B on 2011-02-12 08:49
[forum:1013918]
I have 3layer project
1) data layer (contain the edmx file)
2) bussiness layer
3) intrface

in app.conf i add this line

<add name="VContext" connectionString="provider=Npgsql;metadata=res://*/V.csdl|res://*/V.msl|res://*/V.ssdl;Provider Connection String='Server=127.0.0.1;Port=5432;Database=v;UserId=postgres;Password=123456'"
providerName="System.Data.EntityClient" />

but show this error :
The specified store provider cannot be found in the configuration, or is not valid.

please help me:(

Not present in GAC after correct install [ reply ]
By: Gianluca C. on 2011-02-10 19:05
[forum:1013910]
Hi there!

I downloaded the last version of Npgsql (2.0.11) and I installed both Mono.Security.dll and Npgsql.dll in the GAC by using the command gacutil -i <name>.

At the command prompt for both I can see:

Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.1
Copyright (c) Microsoft Corporation. All rights reserved.

Assembly successfully added to the cache

So, I thought to have successfully completed the task. However when I check in the Object Browser (All components is selected), or even if I click "Add Reference..." in a new project, I cannot find the freshly installed DLLs in the .NET tab.

What did I do wrong?

Thank you in advance for all your help,

Cheers,
Gianluca.

entity framework ; inheritance issue [ reply ]
By: glob bulle on 2011-01-26 15:30
[forum:1007436]
I'm using Npgsql 2.0.11.0 to connect a postgresql (9.0) database to ef (targeting .net framework 3.5 sp1).

My model contains some table-per-type inheritance and it cause an error when I try to update a child entity. Even if changes target only child field, ef regardless generates a query to update the parent and *this* query has a syntax error inside.

For example, say we have a parent table with id and a child with id and child_field (id in child table is a foreign key relative to parent id). In ef, we've got inheritance between a Parent entity and a Child entity.

Imagine we have a Child object, named toUpdate in our context, with id=1.
A code like

toUpdate.child_field = newValue; SaveChanges();

generate queries :

UPDATE child SET child_field=newValue WHERE id=1;
UPDATE parent WHERE id=1;

The second query cause a syntax error because the set clause lacks...

I don't know if it's due to my entity model or to npgsql implementation. Either way, it's a pretty bug for my application and even google don't help me, so you're my only hope :)

I've tried to be clear and I apologize for any imprecisions (and for my english...)
Thanks


upgrade from 2.0.8 to 2.0.11 - datetime field [ reply ]
By: Nick H on 2011-01-02 20:54
[forum:1007157]
after upgrading to 2.0.11 i am now seeing lots of errors "Specified cast is not valid.", the problem seems to datetime fields.

I have spent a lot of time investigating this problem and i am out of ideas now.
I have read about the breaking change in 2.0.10 and i am not sure if that is related, but i dont fully understand.
I am continuign to use 2.0.8 at the moment but would like to keep current and more importantly understand the subtlety of what is going on.
I have tried to create the simplest, self-contained scenario to demonstrate the problem.

running against postgresql 9.0.2 but have tried against various versions and there doesnt seem to be any difference.


DDL to create database structure
****************************
CREATE SEQUENCE table1_pk_seq;

CREATE TABLE table1
( pk integer NOT NULL,
ts timestamp with time zone,
CONSTRAINT table1_pkey PRIMARY KEY (pk));
****************************

'Form class - apologies for VB
****************************
Public Class Form2

Dim connstring As String = String.Format("HOST={0};DATABASE={1};USER ID={2};PASSWORD={3};COMPATIBLE={4};UseExtendedTypes=True;", "localhost", "testdb", "postgres", "whatever", "2.0.8.0")
Dim conn As New NpgsqlConnection(connstring)

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

'create the datatable
Dim dt As New DataTable("table1")
dt.Columns.Add("pk", GetType(Int32))
dt.Columns.Add("ts", GetType(DateTime))

'configure the dataadapter
Dim selectcommand As New NpgsqlCommand("select pk,ts from table1", conn)
Dim da As New NpgsqlDataAdapter(selectcommand)
Dim cmdbuilder As New NpgsqlCommandBuilder(da)
da.InsertCommand = cmdbuilder.GetInsertCommand
da.UpdateCommand = cmdbuilder.GetUpdateCommand
da.DeleteCommand = cmdbuilder.GetDeleteCommand

'add some data
For i As Int32 = 1 To 5
dt.Rows.Add({pk(), Now})
Next
da.Update(dt)
dt.AcceptChanges()

'now try and update the data
For Each dr As DataRow In dt.Rows
dr.Item("ts") = Now.AddDays(7)
Next
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'THIS IS WHERE THE PROBLEM LIES
'IN 2.0.8 THIS WILL WORK, BUT IN 2.0.11 IT WILL PRODUCE THE ERROR 'SPECIFIED CAST IS NOT VALID'
da.Update(dt)
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
End Sub

Function pk() As Int32

If conn.State = ConnectionState.Closed Then conn.Open()
Dim cmd As New NpgsqlCommand("select nextval('table1_pk_seq');", conn)
Return CInt(cmd.ExecuteScalar)

End Function

End Class
****************************

It would take me a while to list all of the things i have tried but briefly,
setting comatability in the connection string seems to make no difference.
problem will go away if i set cmdbuilder.ConflictOption.OverwriteChanges() but i dont want to do that, this leads me to believe the problem lies with the parameters that are being set to ensure concurrency





Sequence Generator in EF 4.0 [ reply ]
By: Diane Jewett on 2011-01-06 21:23
[forum:1007265]
I am trying to add an entity with primary key already set
cred_app_comment.comment_id = 44444444;

But when I Call SaveChanges();
The sequence_generator is always run.
I throught that it was only used as the default for a null primary key?

INSERT INTO "public"."cred_app_comment"("app_id","user_id","comment","entry_dt","comment_category_id") VALUES (cast(4047
701 as int8),'TEST','Testing comment add.',TIMESTAMP '2011-01-06T16:19:39.8590407-05:00',cast(23 as int8));SELECT currva
l(pg_get_serial_sequence('"public"."cred_app_comment"', 'comment_id')) AS "comment_id"

Npgsql Connection Pool [ reply ]
By: Ssss Lllll on 2010-11-18 17:31
[forum:1006730]
I am having an issue with connection pool timeout issues while using Npgsql. I have increased min and max pooling but to no avail.

Can someone tell me if there are any tools to identify the number of connections currently active? Can this be done in Pgadmin?

Thanks,

Slicc

How to handle large strings? [ reply ]
By: José Tavares on 2010-11-17 17:46
[forum:1006715]
I have a table that represents an execution log of my program.
One of the columns of this table is the html of the log (the others are references to other tables and identification information).
The problem I'm having is that I'm getting logs of 30+ MB and when I insert a row in the DB I'm getting an OutOfMemory in the NPGSQL driver.
What is the best practice in these situations?

José Tavares

How to use .net 4.0 version [ reply ]
By: Diane Jewett on 2010-12-22 13:57
[forum:1007038]
I tried to copy the .net 4.0 version to my global assemblies dir, but it does not install and I get no messages.
I can install the 3.5 version fine, but my project is 4.0 and I am worried about compatibility.

AllowPartiallyTrustedCallers [ reply ]
By: Claudia Murialdo on 2010-12-02 12:04
[forum:1006863]
Hello,
Is it possible to get the npgsql assembly with the AllowPartiallyTrustedCallers attribute?. There are some hosts like http://ajuda.uolhost.com.br/index.php?ind=&p=resposta&res=550 which run the application in a partially trusted environment (Medium Trust level) and requires that all the assemblies have that attribute. Is it possible?
Thanks,
Claudia.

VB.net DataGridView Problem Populating [ reply ]
By: Simon Boardman-Weston on 2010-12-01 19:08
[forum:1006862]
I am new to npgsql and am having a problem loading data into a DataGridView control in Visual Studio 2010 using VB. I can get data into an untyped dataset but cannot get that to display in the DataGridView.

I am trying the 'Client 2' example in PostGreSQL book by Korry Douglas and Susan Douglas but the code just doesn't seem to work for me.

If anyone could help I would be very grateful.

Thanks

"could not accept SSPI security context" [ reply ]
By: Reto Schoening on 2010-11-18 16:27
[forum:1006729]
Hi,

I've set up pg (8.3) to use SSPI for authentication (clients and server on windows XP). I can successfully connect from clients using psql and another db client using SSPI authentication. However when trying to connect with Npgsql (2.0.11) as the same user, the following error occurs:

could not accept SSPI security context
SEVERITY: FATAL
Code: XX000

Here's the minimum sequence:

static void Main(string[] args)
{
NpgsqlConnection conn = new NpgsqlConnection("Server=<ip_address>;Port=5432;Database=<db_name>;Integrated Security=true");
conn.Open();
conn.Close();
}

This happens in three different network environments (in all three cases SSPI authentication from other applications succeeds for the same user).

Did I miss something in the connection string? Or something else that could interfere?

Thanks in advance,
Reto

DB login failure using special characters [ reply ]
By: Lars Erik Liljebäck on 2010-11-22 09:45
[forum:1006756]
I am trying to login to a postgresql 8.8.4 db using npgsql and keep getting authentication failure exceptions. The password contains one or more special characters such as §. The database containing the usernames and passwords are set to use the Latin1 encoding. Will this cause some problems since the npgsql connection information is sent from the client using the UTF8 encoding?

How to get number of affected rows [ reply ]
By: Gerald Pöttler on 2010-11-19 06:12
[forum:1006731]
I'm trying to execute a stored procedure and would like to get back a result of how many rows were affected. The code I am running is this:


var connStr = @"Server=127.0.0.1;Port=5432;User Id=postgres;Password=***;Database=Test;";
using (var conn = new NpgsqlConnection(connStr)) {
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "insert_something";
cmd.CommandType = CommandType.StoredProcedure;
NpgsqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["_id"].Value = 1;
cmd.Parameters["_val"].Value = 2;
var rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine(rowsAffected);
}
}

The problem is that the record does get inserted, but the rowsAffected value is always -1 :(
Is there something I can do to the function to make it return the number of records it affected?

Cannot cast CIDR network to address in 2.0.11 [ reply ]
By: Andrus Moor on 2010-11-13 22:00
[forum:1006673]
Using Npgsql2.0.11-bin-ms.net3.5sp1 zip file in VS2010 line in appl code

if (dataReader.GetFieldType(j) == typeof(System.Net.IPAddress))
object x = dataReader.GetValue(j).ToString();


Throws exception

System.InvalidCastException: Cannot cast CIDR network to address
at NpgsqlTypes.NpgsqlInet.op_Explicit(NpgsqlInet x) in C:\projects\Npgsql2\src\NpgsqlTypes\NpgsqlTypes.cs:line 654
at NpgsqlTypes.NpgsqlTypesHelper.<TypeInfoList>b__0(Object ipaddress) in C:\projects\Npgsql2\src\NpgsqlTypes\NpgsqlTypesHelper.cs:line 494
at NpgsqlTypes.NpgsqlBackendTypeInfo.ConvertToFrameworkType(Object providerValue) in C:\projects\Npgsql2\src\NpgsqlTypes\NpgsqlTypesHelper.cs:line 879
at Npgsql.ForwardsOnlyDataReader.GetValue(Int32 Index) in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1404

How to fix ?

Andrus.

Concurrency violation [ reply ]
By: Pieter Kruger on 2010-10-28 10:29
[forum:1006618]
Hello everyone,

I am experimenting with PostgreSQL and npgsql. When looking at the basic CRUD methods, my update and delete methods returns “Concurrency violation: the ?????Command affected 0 of the expected 1 records”. The code works fine with SQL Sever. Here is the code (sorry for the long post):

public class CTorDBpgsql
{
private NpgsqlCommandBuilder bld;
private NpgsqlConnection con;
private string conString;
private NpgsqlDataAdapter da;
private DataRow drRow;
private DataSet ds;
private int rc = 0;

public CTorDBpgsql()
{
conString = @"Server=127.0.0.1;Port=5432;User Id=postgres;Password=Dummy1;Database=Warships;";
}

public int Create(CTor Torpedo)
{
try
{
LoadDB();
drRow = ds.Tables["Torpedo"].Rows.Find(Torpedo.Name);
if (drRow == null)
{
drRow = ds.Tables["Torpedo"].NewRow();
drRow["torName"] = Torpedo.Name;
drRow["torRange"] = Torpedo.Range;
ds.Tables["Torpedo"].Rows.Add(drRow);
da.Update(ds, "Torpedo");
rc = 0;
} // end if
else
{
rc = -1;
} // end else
con.Close();
return rc;
} // end try
catch (Exception ex)
{
Console.WriteLine("Create: " + ex.Message);
return -2;
} // end catch
} // end method

public int Delete(CTor Torpedo)
{
try
{
LoadDB();
drRow = ds.Tables["Torpedo"].Rows.Find(Torpedo.Name);
if (drRow != null)
{
drRow.Delete();
da.Update(ds, "Torpedo");
} // end if
else
{
rc = -1;
} // end else

con.Close();
return rc;
} //end try
catch (Exception ex)
{
Console.WriteLine("Delete: " + ex.Message);
return -2;
} // end catch
} // end method
private void LoadDB()
{
string sSQL;
try
{
sSQL = @"SELECT ""Torpedo"".""torName"", ""Torpedo"".""torRange"" FROM ""Torpedo"";";
//sSQL = "SELECT * from Torpedo";

con = new NpgsqlConnection(conString);
con.Open();

da = new NpgsqlDataAdapter(sSQL, con);
bld = new NpgsqlCommandBuilder(da);
ds = new DataSet();

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds, "Torpedo");
} // end try
catch (Exception ex)
{

} // end catch
} // end method
} // end class

Any ideas? :-)

Detecting if a connection closed [ reply ]
By: peter meredith on 2010-11-09 20:31
[forum:1006651]
I keep a connection open to listen to notifications on the database. This works great until the database is restarted.

Is there a way to detect that the connection has been severed outside of manually sending 'select ;' to the server occasionally?

What is the max CommandTimeout? [ reply ]
By: Ben Roberts on 2010-11-02 00:13
[forum:1006636]
Hi,

I have a query that takes a long time to run, I have the commandtimeout set to 1024 at the moment and it times out. If I set this to zero will it run for an infinite length of time? Or is 1024 the maximum number of seconds a command can run for?

Thanks

Ben

Use bytea as binary [ reply ]
By: k k on 2010-10-29 12:24
[forum:1006624]
Hi,

Which is the easiest way to store a byte[] in a bytea column.

When I insert an byte[20], and I read it back I get a byte[41], so double lengths and different values. I know about how bytea works.

My question is how can I use the Npgsql to do the conversion for me.

Support for bytea in PostgreSQL 9 [ reply ]
By: Cullen Trey on 2010-09-24 07:24
[forum:1006475]
Hi,

Npgsql works like a charm with PostgreSQL 9, except that a bytea is encoded (new default in PostgreSQL 9 ) in hex. So i can't directly load the bytea in an byte[]. Who is or will be responsable for the format conversion? Npgsql or the program using Npgsql?

kind regards, Trey

network connections to postgres [ reply ]
By: David DeMeglio on 2010-10-25 02:39
[forum:1006589]
Hi all
I have built an application using c sharp, npgsql and postgres as the database. Everything works fine when the app is run on the same machine as postgres is installed on. When I try to run the app on a different box within the network I get this error
'password authentication failed for user "dbuser"'.
I read the documentation but haven't figured out what needs to be done to resolve this.
Any help would be appreciated.
thanks much

$150 reward [ reply ]
By: David DeMeglio on 2010-10-28 02:14
[forum:1006617]
ok, I'm desperate. I have a project that is complete but I can't get the network connection to function. The front end is c sharp/.net, postgres backend. The project functions fine if the front end is running on the same box as postgres. If I try to connect from another machine on the network it gives a failed connection message. I have edited the postgresql.conf and pg_hba.conf stopping and restarting the service each time. I have turned off all firewalls. It has got to be a stupid syntactical error on my part but I am late on delivery and have spent 2 days banging my head against the wall.
If someone is willing to spend sometime looking at config files and helping me to get this working I am willing to pay $150, even if it only takes 5 minutes to resolve.
thanks
Dave

Trying to make parameter passing an datasets [ reply ]
By: Arya Farzan on 2010-10-26 23:58
[forum:1006601]
Hello

I've constructed the code below, but its giving me errors, any idea what I did wrong?

public DataTable billingDataSet(String billing)
{
connect();
try
{
NpgsqlTransaction t = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM accounts where service = :value1", conn);

command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlDbType.Varchar));
command.Parameters[0].Value = billing;

command.CommandType = CommandType.StoredProcedure;

NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
da.Fill(ds);
ds.Reset();
// filling DataSet with result from NpgsqlDataAdapter
// since it C# DataSet can handle multiple tables, we will select first
dt = ds.Tables[0];
t.Commit();
closeConnection();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
closeConnection();
}

return dt;
}



Best Regards!

2.0.10 Prepare statement is still broken [ reply ]
By: xMoMx on 2010-10-25 03:25
[forum:1006590]
following up from this thread

http://pgfoundry.org/forum/new.php?forum_id=519&group_id=1000140

Preparing SmallInt array still give out an error. Not sure how other type give out too or not. Will someone with spare time help look into this?

prepare stmt with param direction out [ reply ]
By: Mark B on 2010-10-19 06:28
[forum:1006564]
I can do the prepare without an out on a function, but with an out I'm not clear how you are meant to make it work.
I have tried NPGsql 2.0.8 and 2.0.10

CREATE OR REPLACE FUNCTION dvapp.spbdaddition(IN inrunid integer, IN inxid integer, OUT outbdident integer)

cmd.CommandType = CommandType.StoredProcedure;
paramRunID = new NpgsqlParameter("@inRunID", NpgsqlDbType.Integer);
paramXValue = new NpgsqlParameter("@inXID", NpgsqlDbType.Integer);
paramBDID = new NpgsqlParameter("@outBDIdent", NpgsqlDbType.Integer);
paramBDID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramRunID);
cmd.Parameters.Add(paramXValue);
cmd.Parameters.Add(paramBDID);
cmd.prepare
..
paramRunID.NpgsqlValue = nRunID;
paramXValue.NpgsqlValue = nDirPathID;
cmd.ExecuteNonQuery();
int nBDID = Convert.ToInt32(cmd.Parameters["@outIDIdent"].NpgsqlValue);

=============================================
cmd.CommandText = "spbdaddition";
..
//cmd.Prepare();

RUNS
----------------------------------------------
cmd.CommandText = "spbdaddition(@inRunID, @inXID)"
..
cmd.Prepare();

2010-10-20 00:14:01 PDT [28095]: [238-1] ERROR: bind message supplies 3 parameters, but prepared statement "npgsqlplan5" requires 2
2010-10-20 00:14:01 PDT [28095]: [239-1] STATEMENT: select * from spbdaddition($1::int4, $2::int4)
----------------------------------------------
cmd.CommandText = "spbdaddition(@inRunID, @inXID, @outBDIdent)";
..
cmd.Prepare();

Failed! ERROR: 42703: column "outbdident" does not exist -- at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
2010-10-20 01:09:27 PDT [28556]: [7-1] ERROR: column "outbdident" does not exist at character 53
2010-10-20 01:09:27 PDT [28556]: [8-1] STATEMENT: select * from spbdaddition($1::int4, $2::int4, @outBDIdent)


Does anyone know how to do this,

Ambigous column while working with EF [ reply ]
By: Rogério Vaz on 2010-10-14 05:52
[forum:1006535]
Well,

I having a big time trouble while working with NPGSQL as my Entity Framework data provider.

I`m trying to execute this query:

pedido_venda pedido = VendasFramework.pedido_venda.Where(x => (x.cliente_id == clientePai.cliente_id || x.cliente.cliente_id_pai == clientePai.cliente_id))
.Where(y=> y.recebimento.tb_recebimento_id == 3
&& y.recebimento.data_recebimento < dataHoje
&& y.recebimento.data_pago == null)
.FirstOrDefault();


Every time I try to execute i get this EntityCommandExecutionException message:"An error occurred while executing the command definition. See the inner exception for details."

And the inner exception says to me: "ERRO: 42702: referência à coluna \"recebimento_id\" é ambígua". This means in english something like "column reference recebimento_id is ambiguous."

How could it be since I don`t write the SQL statement in Entity Framework?
I think the error happens because the table recebimento refereces itself. Is this some bug?

Anyway the sql statement wich runs on postgres is the following:"
SELECT "Filter1"."pedido_id" AS "pedido_id","Filter1"."cliente_id" AS "cliente_id","Filter1"."usuario_id" AS "usuario_id","Filter1"."recebimento_id" AS "recebimento_id","Filter1"."tb_pedido_id" AS "tb_pedido_id","Filter1"."data_pedido" AS "data_pedido","Filter1"."data_validade_consignacao" AS "data_validade_consignacao" FROM (SELECT "Extent1"."pedido_id" AS "pedido_id","Extent1"."cliente_id" AS "cliente_id","Extent1"."usuario_id" AS "usuario_id","Extent1"."recebimento_id" AS "recebimento_id","Extent1"."tb_pedido_id" AS "tb_pedido_id","Extent1"."data_pedido" AS "data_pedido","Extent1"."data_validade_consignacao" AS "data_validade_consignacao","Extent1"."recebimento_id" AS "recebimento_id" FROM "public"."pedido_venda" AS "Extent1" LEFT OUTER JOIN "public"."cliente" AS "Extent2" ON "Extent1"."cliente_id"="Extent2"."cliente_id" LEFT OUTER JOIN "public"."recebimento" AS "Extent3" ON "Extent1"."recebimento_id"="Extent3"."recebimento_id" WHERE 3= (CAST ("Extent3"."tb_recebimento_id" AS int4))) AS "Filter1" LEFT OUTER JOIN "public"."recebimento" AS "Extent4" ON "Filter1"."recebimento_id"="Extent4"."recebimento_id" WHERE ((("Filter1"."cliente_id"= (((4)))) OR ("Filter1"."cliente_id_pai"= (((4))))) AND ("Filter1"."data_recebimento"< (((E'2010-10-14 00:00:00.000000'))))) AND ("Extent4"."data_pago" IS NULL ) LIMIT 1"

Any help is appreciated.

Sorry for my english....



Why Npgsql not appear in the provider list? [ reply ]
By: Zanardi on 2010-09-14 13:33
[forum:1006444]
I used npgsql for a few months. I added 'Npgsql.dll' and 'Mono.Security.dll' to the bin directory and everything works fine in VS2008.

Why Npgsql not appear in the provider list?
I created a file .udl but in the list of OLE DB does not appear Npgsql.

screenshots:
http://img155.imageshack.us/img155/2263/immagineyrm.jpg

Thanks,
Zanardi

Error with LargeObjects [ reply ]
By: João Ramos on 2010-10-08 10:18
[forum:1006524]
I have a program that needs to send several LargeObjects to the server (minimum file is 500KB).
This is the "pseudo-code" that sends the files to the server:

using (var connBD = createConnection())
{
foreach (var curFile in files)
{
using (var transBD = connBD.BeginTransaction())
{
int newID;

//create LO and fill it with all the file's data
using (System.IO.FileStream fileStream = System.IO.File.OpenRead(curFile.path))
{
var loManager = new NpgsqlTypes.LargeObjectManager((Npgsql.NpgsqlConnection)connBD);
newID = loManager.Create(NpgsqlTypes.LargeObjectManager.READWRITE);

var bufferTemp = new byte[1024 * 1024];

var largeObject = loManager.Open(newID, NpgsqlTypes.LargeObjectManager.READWRITE);


while (true)
{
var bytesRead = fileStream.Read(bufferTemp, 0, bufferTemp.Length);

if (bytesRead > 0)
largeObject.Write(bufferTemp, 0, bytesRead);

if ((bytesRead <= 0) || (fileStream.Position >= fileStream.Length))
break;
}

largeObject.Close();
}


//call stored procedure to update table with new loid
...

transBD.Commit();
}
}
}

When calling "transBD.Commit()", the following erros occurs: "Object reference not set to an instance of an object." with the stack:
at Npgsql.NpgsqlCommand.ClearPoolAndCreateException(Exception e)
at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
at Npgsql.NpgsqlCommand.ExecuteBlind()
at Npgsql.NpgsqlTransaction.Rollback()
at Npgsql.NpgsqlTransaction.Dispose(Boolean disposing)
The error also seems random, meaning that some files are created just fine and then, with no apparent reason, the exception occurs. If I run the code again the first file is upload just fine (the same file that previously generated the exception).

Error calling ExecuteScalar geting DB time [ reply ]
By: José Tavares on 2010-09-29 14:08
[forum:1006508]
I have a method that gets the DateTime from the DB with the following code:

NpgsqlCommand cmd = ActiveConnection.CreateCommand();
cmd.CommandText = "SELECT NOW()";

object obj = cmd.ExecuteScalar();
return (DateTime)obj;

Sometimes an InvalidCastException is thrown when converting the result to DateTime.
How can this be happening? do you have any ideas on where to start scratching?

TIA
José Tavares

NpgsqlDataAdapter::Fill and bit(1) fields [ reply ]
By: r stiles on 2010-09-24 20:39
[forum:1006480]
I understand that Npgsql 2 handles bit(n) fields differently than Npgsql 1. Consequently, when calling NpgsqlDataAdapter::Fill( DataSet ) with a select statement that returns a bit(1) field, an error results with the message "Type of value has a mismatch with column". Is there a way to use the Fill method with a single DataSet parameter with queries returning values of type bit(1)?

Thanks

ProviderManifestToken conflict-ssdl vs npgsql [ reply ]
By: Krystian Paszkiewicz on 2010-05-17 21:40
[forum:1006074]
After generating entity model using edmgen2 ProviderManifestToken in ssdl is set to 8.3.7 while in npgsql it's set to 8.1.3. I get the following exception:

Exception Details: System.Data.MetadataException: Schema specified is not valid. Errors:
Npgsql.NpgsqlSchema.ssdl(2,52) : error 0169: All SSDL artifacts must target the same provider. The ProviderManifestToken '8.1.3' is different from '8.3.7' that was encountered earlier.

I'm using npgsql 2.0.9 for .net 4.0.
The code that throws the exception (look line 18):

Line 17: var entities = new CallEntities();
Line 18: return View(entities.call.ToList());

Please help.

Pattern matching and parameters: what to do? [ reply ]
By: S. M. on 2010-09-20 14:13
[forum:1006456]
Hi,

I've got a question about parameters and pattern matching with npgsql.

I've got no problem to create queries such as:

SELECT * FROM my_table WHERE cond=:par1;

with this:

_SqlCommand = New NpgsqlCommand("SELECT * FROM my_table WHERE cond=:par1;", _Connection)
MyParameter = New NpgsqlParameter("par1", NpgsqlTypes.NpgsqlDbType.Text,4)
MyParameter.Value = "abcd"
_SqlCommand.Parameters.Add(MyParameter)


This creates the query:
SELECT * FROM my_table WHERE cond='abcd';

Now, I'd like to create a query with pattern matching, to get something like this:
SELECT * FROM my_table WHERE cond LIKE '%abcd%';

I tried a lot of things, but it never works...

I tried this for example, but it doesn't work:
_SqlCommand = New NpgsqlCommand("SELECT * FROM my_table WHERE cond LIKE :par1;", _Connection)
MyParameter = New NpgsqlParameter("par1", NpgsqlTypes.NpgsqlDbType.Text,4)
MyParameter.Value = "%abcd%"
_SqlCommand.Parameters.Add(MyParameter)

(...)

Could someone give me a tip on how to build the good query with parameters so that it works with this kind of pattern matching?

Thanks a lot for your help!

npsql with .NET Compact 3,5 [ reply ]
By: Johnny L. on 2010-09-12 15:37
[forum:1006437]
When I'm adding npgsql as reference to my project & compiling it, the debugger is throwing an error...

"There is not enough space in the drive"..

So my question is. Is there a working version of npgsql for the CF?

Newer Messages  Older Messages
Powered By FusionForge