Summary
Activity
Forums
Tracker
Lists
Tasks
Docs
Surveys
News
SCM
Files

Forum: help

Monitor Forum | Start New Thread Start New Thread
Help connecting to database from asp.net [ reply ]
By: Ryan Reif on 2010-08-17 15:23		[forum:1006365]
Hi all,

I am trying to connect to use the Nglsql and am having issues. I have added a reference to the compiled 4.0 version Npgsql.dll (version 2.0.10.0) in the web app and in my code behind I am importing the namespaces:

Imports System
Imports System.Data
Imports System.Nglsql

Intellisense works great. I keep on getting missing source files when I try to debug the program. It is looking for files like Locating source for 'C:\projects\Npgsql2\src\Npgsql\NpgsqlConnection.cs'. Checksum: MD5 {23 b5 2d 52 c 39 e 34 1a 28 da 13 9e 41 7d f1}

In the below code, it never gets past the NpgsqlConnection.


Dim sql As String
sql = "SELECT ""SMSs"" FROM ""SMSs"";"

Dim SQLConnect As String
SQLConnect = "Server=123.123.123.123;Port=5432;Database=watermissions;Userid=username;Password=pass;Pooling=true;MinPoolSize=1;MaxPoolSize=20;ConnectionLifeTime=15"
Dim conn As NpgsqlConnection = New NpgsqlConnection(SQLConnect)

conn.Open()
...

Should it do this with the binary version? Could someone please help me resolve this issue? I have searched online a bit but I haven't been able to find anything addressing this.

EF 4 ctp 4 support [ reply ]
By: Raúl Andrés Duque Murillo on 2010-08-17 04:47		[forum:1006363]
The DatabaseExists() method is not yet supported ... any plan to support it?

Insert, delete, update DB from Visual C# [ reply ]
By: Pham Son on 2010-08-13 17:16		[forum:1006354]
I use PostgresSQL.
Nơ, I want to insert, delete, update database from Visual C#. But, I don't know.
I connected successful PostgresSQL & Visual C#
Can you help me???
Thanks.

SET SSL_RENEGOTIATION_LIMIT [ reply ]
By: J Verheyden on 2010-08-10 06:53		[forum:1006324]
In version 2.0.10 the NpgsqlConnection will automaticly send the SET SSL_RENEGOTIATION_LIMIT to 0 after connect (i have SSL on preferred). (Correct?) In my case I'm running a production and test server with postgresql 8.4.1. So the set call will return an error and Npgsql will throw an error on connection.open() which is unacceptable in our situation, will this be fixed in later releases or is there a patch or will I have to recompile the dll myself to disable this set call? Please try to keep backwards compatibility.

npgsql and date types [ reply ]
By: horvath imre on 2010-04-20 16:21		[forum:1005970]
hi,

please advise me on this issue.

I use npgsql to connect to the postgresql server. My application is written in vb.net.

In one table I store date fields too, no problem. If I open up the table, I see the date values like 2010-04-20, that's ok. Originally, I send the date values in this format: 2010.04.20 to the server, so this is ok too.

BUT, when I read the dates via npgsql.dataadapter, I get date values like 2010.04.20 0:00:00

And this isn't ok. I tried to remove the nulls, f.e. Format(date value,"yyyy.mm.dd"), no succes. I get an exception (whitout stopping the app): A first chance exception of type 'System.ArgumentException' occurred in Microsoft.VisualBasic.dll

And the other rows don't show up too in the datagridview.

Why this formatting trouble? How can I solve it?

OrderByDescending(s => s.Date).FirstOrDefault [ reply ]
By: the veeroo on 2010-07-26 09:17		[forum:1006230]
Hello
I have a problem with my Linq-to-entities query with npgsql provider.
I have 3 tables: Order, OrderStatus and OrderStatusDict (dictionary). OrderStatus is some kind of changeLog - every order's status change is added to the OrderStatus table with its actual date and new status. I need to get all orders, which status.OrderStatusDict.OrderStatusName == "Pending", so im doing this this way:

var lst = (from o in db.Order
where o.OrderStatus
.OrderByDescending(s => s.Date)
.FirstOrDefault()
.OrdereStatusDict.OrderStatusName == "Pending"
select o).ToList();
And I get an exception:
An error occurred while preparing the command definition. See the inner exception for details.

Data:{System.Collections.ListDictionaryInternal}
Inner exception: {"The method or operation is not implemented."}

And it looks that OrderByDescending kills my query, cause if I comment
.OrderByDescending(s => s.Date)
Everything works fine, but I get the oldest OrderStatus from db :/

Can u help me? Is this a provider's cause or EF problem? Do you have any ideas I could gain this other way?

Nullpointer in NpgsqlDatareader [ reply ]
By: Stefan Nicodem on 2010-07-01 07:33		[forum:1006183]
After rewriting the database access code of my application I came across this error while executing a COUNT query with ExecuteScalar().

Tha application is multithreaded (about 11 to 25 threads at a time) and each thread does alot of queries (about 50 to over 1000). It are mostly insert statements and all of those seem to go fine.

I also read here about the problem with the ReadInt32() and ReadInt16() buffers, so I'm using the latest version from CVS.

That's the story, now the problem:
I traced the error by debugging in visual studio express, and found out it is caused by calling _connection.EmergencyClose() in NpgsqlData

Passing row as parameter [ reply ]
By: Matt . on 2010-07-05 20:36		[forum:1006203]
I'm not sure the right way to ask this question, so I apologize for anything that's confusing. Currently I'm not using the Entity Framework (I don't know it well enough to use it).

I have a stored procedure and I'd like to pass rows as parameters. Here's a contrived example (and pseudo-code) of what I'm trying to do:

I have a table users:

CREATE TABLE users (
id integer
username text
date_added timestamp
);

Right now, I have a stored procedure that takes a single username and date and does the insert. When I need to insert multiple users, I end up having to iterate in my code:

foreach (User user in users)
{
cmd.Parameters[0].Value = user.name;
cmd.Parameters[1].Value = user.date;
cmd.ExecuteScalar();
}

where cmd is an NpgsqlCommand that calls the stored procedure add_user:

add_user(@username text, @date_added timestamp)


The problem is, when I need to add thousands of users, this is slow. I'd like to instead pass the array of users:

add_users(@users _users);

Then the stored procedure can do the processing/work.

I'm sorry if that doesn't make sense, but that's the general idea of what I'm going for. Any suggestions would be greatly appreciated.

Entity framework: how to use views/functions? [ reply ]
By: anton Hughes on 2010-07-05 12:35		[forum:1006199]
Hi

I'm wanting to use EF in my current project, but I see that it cannot work with geometry types, so I thought I would encapsulate the logic in a view or custom function.

However, the views and functions do not get generated into the c# classes. I have one view and one custom function and I cannot see either of these referenced in the generated c# code.

What do I need to do to call a view or function from EF?

Thanks

Unable to read large bytea field data [ reply ]
By: Casper Jasmine on 2010-07-08 12:51		[forum:1006209]
Good day to all.

I am facing getting NotSupportedException while reading large bytea field data. I am new to the forum, if this issue is already discussed, kindly point me there.

Details:
OS: Windows XP sp3
RAM: 3GB
Postgres: 8.4 (localhost)
Npgsql: 2.0.6

I am able to insert large data using prepared statement

<code>
using (NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgre;Password=postgre;Database=test;"))
{
conn.Open();
using(NpgsqlCommand command = new NpgsqlCommand("insert into screenshotstore (screenshot, filesize) values (:screenshot, :filesize)", conn))
{
command.Parameters.Add(new NpgsqlParameter("screenshot", NpgsqlDbType.Bytea));
command.Parameters.Add(new NpgsqlParameter("filesize", NpgsqlDbType.Integer));
command.Prepare();
FileStream fs = new FileStream(@"c:\test.bmp", FileMode.Open); // 30MB file
int iFileSize = Convert.ToInt32(fs.Length);
byte[] bytData = new byte[iFileSize];
fs.Read(bytData, 0, iFileSize);
fs.Close();
command.Parameters[0].Value = bytData;
command.Parameters[1].Value = iFileSize;
command.ExecuteNonQuery();
}
conn.Close();
}
}
</code>

For reading bytea field data, I have used DataAdapter with no luck. Later I tried with DataReader with no luck and ExecuteScalar with no luck. Following is the code I am using to read large bytea

<code>
using (NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgre;Password=postgre;Database=test;"))
{
conn.Open();
using (NpgsqlCommand command = new NpgsqlCommand("select screenshot from screenshotstore where id = 12", conn))
{
Byte[] rawData = (Byte[])command.ExecuteScalar();
int fileSize = rawData.Length;
FileStream fs = new FileStream(@"c:\test1.bmp", FileMode.OpenOrCreate, FileAccess.ReadWrite);
fs.Write(rawData, 0, fileSize);
fs.Close();
fs.Dispose();
}
conn.Close();
}
</code>

A NotSupportedException is raised with message <b>Backend sent unrecognized response type: 0</b>. Please tell me how to read large bytea field data.

Regards,
Casper

Backend sent unrecognized response type: \0 [ reply ]
By: Washington Junior on 2010-06-30 23:48		[forum:1006179]
Hi, guys

I'm having a issue with npgsql ("Backend sent unrecognized response type: \0"), but only on Windows 7 system. All returned records from a query are transformed in objects by my own relational-object class (called GSPersistence).
Having said that, here is what happens: I'm not using multiple threads, but I fill a DataGridView with my objects that have some properties which may access others objects. I think that the problem is that when DGV is showing the objects, properties that access others objects (which are created with the return of a query from PostgreSQL) may be causing something like "multiple threads". Imagine showing 20 objects that have at least 3 properties that need a query to be filled and you'll have 60 queries running.
On GSPersistence I'm using npgsql 1.0, but I already tried to switch to version 2.0 and the same problem happens (and even often).
And this issue doesn't happen on Windows XP SP2 or Windows 98 SE, only on Windows 7 (haven't tried on Vista). My software is running on 20 machines, and the problem happens only on the 3 machines with Win7 (there were 4, but one of them I moved to WinXP). All others operations works perfectly, except for showing data on a DataGridView, which sometimes works and sometimes doesn't.

Does anybody have a tip of what should I do? (Besides moving all machines to WinXP... :D)

Thanks to all.

Junior

Connection Timeout Exception [ reply ]
By: Tindo Ras on 2010-06-24 09:26		[forum:1006167]
Hi Guys

Ive been using postgres for about two years now. For a while ive been tormented by the above mentioned exception.

Now this exception hangs the whole application, and sometimes the OS.

In client server environment with about 12 client machines each machines hangs 2 to 3 times a day, all at diffrent times.

All machines connect thru the super user postgres.

What can i do to stop the application from hanging?

Anyone with a similar set up can assit with thier implementation

Thanx in advance

Search backwards from last record [ reply ]
By: Richard Ford on 2010-06-07 12:07		[forum:1006126]
Hello everyone!

I have only recently downloaded npgsql, having previously been a complete database newbie, but have got to grips with it pretty well. The one thing that remains a problem for me is ordering my search.

I am making a very large database to search, but when I do my searches it is generally the most recent records that I am interested in, so I really need to search from the end of the table(s). I also need to limit my searches to the last x results.

Now I have no problem with LIMIT 100 for limiting my results, it's the other issue that could be a problem.

I have seen ORDER BY column_name DESC, the thing is my table will be in perfectly correct order already so it seems to me that using ORDER BY DESC might be a time consuming overhead that I could well do without, as this will have to turn the complete table upside down, and timing will be an important factor in my application.

If I could just search backwards from the last record then that is all I need to do. Is this possible please?

Many thanks

Preparing array bug [ reply ]
By: xMoMx on 2010-06-13 10:37		[forum:1006146]
Just wondering if anyone is looking into this?

http://pgfoundry.org/forum/message.php?msg_id=1006145

Array and prepared statement [ reply ]
By: Raoul Cadei on 2010-03-19 16:45		[forum:1005873]
Hello,

I'm presently having troubles trying to insert a Double[] and an Int64[] into a table.

Table fields are declered in the following way:

temp_values DOUBLE PRECISION ARRAY [8192] NOT NULL,
time_values BIGINT ARRAY [8192] NOT NULL,

and the code that is trying to do the insert is the following:

............
command3.Parameters.Add(new NpgsqlParameter("PARAM6", NpgsqlDbType.Array | NpgsqlDbType.Double));
command3.Parameters.Add(new NpgsqlParameter("PARAM7", NpgsqlDbType.Array | NpgsqlDbType.Bigint));
............
command3.Prepare();

............

command3.Parameters[6].Value = new Double[] {1.2d, 1.3d};
command3.Parameters[7].Value = new Int64[] { 1, 3 };

............


When the line

command3.ExecuteNonQuery();

is executed, application stop working and I can see in PostgreSQL logs the message:

ERROR: array value must start with "{" or dimension information

Does any good soul have a working example or (better) some hint or correction to my work?
This thing is driving me crazy!


Many thanks in advance, regards.
Raoul

Using variables in sql insert commands? [ reply ]
By: Arya Farzan on 2010-06-17 07:50		[forum:1006154]
Hello

I'm new to Npgsql and I was reading the help guide, I did find static insert commands, but did not find examples which worked with variables. How can I do that with Npgsql?


Best Regards!

Npgsql 2.0.9 - incorrect generated sql query [ reply ]
By: Leszek ************** on 2010-06-15 08:46		[forum:1006148]
Hi, it seems to be a problem while Npgsql 2.0.9 generates sql statement from the following LINQ query:

db.Users.Where(u => (((u.StatusId == (int)UserStatus.Active) && u.DisplayNameInResults && u.UserDetailsEF.Locality.ToLower().Contains(locality.ToLower())))).OrderBy(u => u.UserDetailsEF.Locality).ThenBy(u => u.Depth).ThenBy(u => u.Name).ThenBy(u => u.Nick)

It produces sql query (see below) where we get an errror (syntax error at or near "FROM"):

SELECT "GroupBy1"."A1" AS "C1" FROM (SELECT CAST (Count(1) AS int4) AS "A1" FROM (SELECT FROM "public"."users" AS "Extent1" LEFT OUTER JOIN "public"."user_details" AS "Extent2" ON "Extent1"."user_id"="Extent2"."user_id" LEFT OUTER JOIN "public"."user_details" AS "Extent3" ON "Extent2"."user_id"="Extent3"."user_id" WHERE ((5="Extent1"."status_id") AND ("Extent1"."display_name_in_results"=TRUE)) AND ((position(lower('city_name') in lower("Extent3"."locality"))) >0)) AS "Filter1" LEFT OUTER JOIN "public"."user_details" AS "Extent4" ON "Filter1"."user_id"="Extent4"."user_id") AS "GroupBy1"

Do you know why it happens?

Npgsql 2.0.9 Issues with EF 4 [ reply ]
By: Charlie Biggs on 2010-04-16 16:01		[forum:1005944]
When I use Npgsql 2.0.9 with a current application I have had running for a long time using Npgsql 2.0.8, I am get GetOrdinal errors in my LINQ to Entities statements. I tried to with both flavors of Npgsql 2.0.9 (Npgsql2.0.9-bin-ms.net4.0.zip and Npgsql2.0.9-bin-ms.net3.5sp1.zip). What is the different between the two packages? Is one for EF 4 (.NET 4) and the other one is for EF 1 (.NET 2)? I had to go back to using Npgsql2.0.8-bin.ms.net3.5sp1.zip order of my application not to get the GetOrdinal when running the application.

Parameters with a CIText column [ reply ]
By: Allen Vachon on 2010-05-13 13:19		[forum:1006057]
Hi,

I'm trying to use a CIText column type (contrib) and would like it to work without casting or as code sample 1.

Is it possible?

Thanks

Code sample 1: Simple and ok
NpgsqlCommand mTest = new NpgsqlCommand("select 'allo' = 'Allo'::citext", MyConnection);
bool mRes1 = (bool)mTest.ExecuteScalar();

Code sample 2: Parameters and not working
NpgsqlCommand mTest2 = new NpgsqlCommand("select :Allo = 'Allo'::citext", MyConnection);
mTest2.Parameters.Add(new NpgsqlParameter("Allo", DbType.String));
mTest2.Parameters[0].Value = "allo";
bool mRes2 = (bool)mTest2.ExecuteScalar();

Cast-Exception with NpgsqlTimeStamp [ reply ]
By: Bernd Pinter on 2010-04-27 13:01		[forum:1006004]
Hey!
We run into a cast-exception with Npgsql >= 2.0.8. It'll happen when we try to copy (assign) the content of a NpgsqlTimeStamp into a DateTime class-instance. This bug new in 2.0.8 (and also happens in 2.0.9) but it didn't occur in 2.0.5.

We found this Timestamp-Bug deep inside within a library of our company. However, i build a little test-program (copy out all necessary code-fragments into a single file) to verify the bug.

Plattform: C# with MS .Net 3.5SP1, VisualStudio 2008


I don't know if this forum is the right place for this message and i also don't know where to post my test-program. Please help! Thx!

best,
Bernd Pinter

Does Crystal Reports need Npgsql commands? [ reply ]
By: Ricardo Martin on 2010-05-07 11:07		[forum:1006041]
Hi there

After working for several weeks with Npgsql, I understand why there are quite a few very good comments about how well Npgsql works!

I have an issue, but not sure if really something I'm doing wrong or not. I have a Windows app in VS2008 (Net3.5) with a Crystal Report created using the wizard and connecting to PG using ODBC DSN. Eventually the report was boundto the form.

Report does show real data and everythign as expected in preview mode; however, when running the app and choosing the option containing the form with CR control, it does not connect to database.

It prompts for password and 'Logon Failed' error message comes up. Something strange I noticed, is that DSN name is in the server name box and the database name box is blank.

My question is: do I need to use the Npgsql commands (connection, datareader, etc) to have Crystal Reports work correctly?

Thanks, Ricardo

Unicode and Ascii Issues [ reply ]
By: Kengie Ho on 2010-05-05 10:27		[forum:1006029]
Hi,

I am having some issues with the extract some chinese characters stored in the postgresql as an SQL_ASCII encoding. This was done due to historical issues. Now I am using the Npgsql .net 2.0.9 driver to access the data from the database.

When the database is using Encoding as UTF-8, I can get my chinese characters viewing correctly. When the database is using Encoding as SQL_ASCII, the chinese characters in the database comes how as '�L�w�g'. I have tried sending the encoding to utf8, unicode and ASCII but I have always got the same results. I have even tried using the SQL statment 'SET CLIENT_ENCODING' but it still doesn't work.

A little more detail info is that when I get the characters from the Unicode database and using the encoding class of .Net. My byte count for the text is 10bytes(extra byte for LF/NL i think) and if I type the actually chinese characters in the Form and using the encoding class, I am getting 9bytes. But if I am extracting the chinese characters from ASCII database, I am getting a reading of 12bytes from the encoding class. The chinese char are 林安妮. If 3bytes for one word, then it should only be only be 9bytes, which is correct if I getbytes directly from 林安妮. Somehow the ASCII database is returning some extra bytes (i think). I think it might be some issues with the driver as connecting to unicode database and getting the bytes from 林安妮 returns the same amount of bytes.

Please help. Thanks.

FillSchema Npgsql 1.0.1 [ reply ]
By: Kengie Ho on 2010-05-06 03:14		[forum:1006032]
Hi,

It seems that the FillSchema isn't working in Npgsql 1.0.1 along with the DBType Array.

Can these be roll forward in Npgsql 1.0.1?

Link in regards to FillSchema:
http://pgfoundry.org/forum/message.php?msg_id=1002744

Basically I need ASCII support and parameter array passing. But ASCII support is in 1.0.1 and parameter array support is in 2.0.9. These feature get merged or can npgsql support parameter array in 1.0.1? Thanks.

using arrays (like Int32[]) with a datatable [ reply ]
By: David Jaspers on 2009-11-30 13:47		[forum:1005598]
Sample postgres database table:
column a : type integer (.net Int32)
column b : type integer[] (.net Int32[])

If I fill the datatable with with the dataset Fill command all is fine.
I can read the data and get the column b array values.

Prior to npgsql2 this was returned as an string with values like {1,3,4}.
Now this is returned as Int32[].
That's Oke

But Update gives an exception!
sample:
ds.Table[0]["b"] = new Int32[] {1,3,4};
ds.Update();

invalidcastexception:
"Cannot convert type System.Int32[] to type System.IConvertible."


I am migrating from the old npgsql code to npgsql2.07.
Prior to npgsql2 an array was an string columntype and the values needs to be set as:
ds.Table[0]["b"] = "{1,3,4}";
ds.Update();

How can I do this in npgsql2?


Or is this a problem with the NPGSQL CommandBuilder not supporting arrays?

multi-dimension array as parameter [ reply ]
By: Kengie Ho on 2010-04-23 11:03		[forum:1005980]
Hi All,

I am trying to call a function with several arrays are parameters. Is this possible with Npgsql .Net driver?

I am getting an error of "ERROR: 42601: syntax error at or near "'''{{"ABC",1,10,"O",1,"ABC","ABC","ABC",0,""}}'''""

The generated SQL from the provider is:
select * from user.save(
(array'''{{"ADMIN",1,10,"O",1,"ABC","ABC","ABC",0,""}}''')::varchar[])

If i try to run the generated SQL. I will get an error in PGAdmin. As you can see the provider will generate a 'array' word in the SQL and there will also be extra quotes in the SQL. If i remove the array word and the extra quotes. The statment can run fine.

Here is the VB code for value of the Parameter.
TestCmd.Parameters(0).NpgsqlDbType = NpgsqlDbType.Array Or NpgsqlDbType.Varchar
TestCmd.Parameters(0).Value = "'{{" & Chr(34) & "ADMIN" & Chr(34) &",1,10," & Chr(34) & "O" & Chr(34) &",1," & Chr(34) &"ABC" & Chr(34) &"," & Chr(34) & "ABC" & Chr(34) &"," & Chr(34) & "ABC" & Chr(34) &",0," & Chr(34) & "" & Chr(34) & "}}'"

There are no extra single quotes in the VB code and the Chr(34) is to generate the doubles quotes.

What is wrong with my code? Thanks for your help.

Regards,
Kengie

No return value from function [ reply ]
By: Kengie Ho on 2010-04-23 11:07		[forum:1005981]
Hi All,

I am getting no result value from this very simple sub in VB.Net

Private Sub TestSPPostgreSQL4()
Dim TestCon As New NpgsqlConnection(ConnectionString)

TestCon.Open()

Dim TestCmd = TestCon.CreateCommand

TestCmd.CommandType = CommandType.StoredProcedure
TestCmd.CommandText = "user.test"

Dim RetValParam As New NpgsqlParameter
TestCmd.Parameters.Add(RetValParam)
NpgsqlDbType = NpgsqlDbType.Varchar
TestCmd.Parameters(0).Size = 20
TestCmd.Parameters(0).Direction = ParameterDirection.ReturnValue

Debug.WriteLine(TestCmd.CommandText)

Dim rdr As NpgsqlDataReader = TestCmd.ExecuteReader

Debug.WriteLine("Return Value is " & TestCmd.Parameters(0).Value)

TestCon.Close()
End Sub

The function on the PostgreSQL is:
CREATE OR REPLACE FUNCTION ctsuser.test()
RETURNS text AS
$BODY$
DECLARE
ret varchar;
begin
return 'OK';
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Is this a bug or is there some issues with my code? I have already followed the User's Manual instructions.

By the way I am using VS 2008 and Npgsql 2.0.9

Thanks.

Regards,
Kengie

EF4 / VS 2010 compatibility [ reply ]
By: hapiec peicn on 2010-03-26 10:28		[forum:1005902]
Hello,

Is there any news abount EF4 compatibility? Lack of it prevents any use of npgsql with Entity Framework with visual studio 2010.

Sample of EF [ reply ]
By: Fernando Siguenza on 2010-04-16 16:59		[forum:1005949]
Hello friends, please where i can see any sample of EF,how i can make de ef model??

Tanks

Postgresql (Npgsql) table consturction [ reply ]
By: Mariusz Mackiewicz on 2010-04-20 18:30		[forum:1005971]
Hi, I need get table structure from Postgresql database, with Npgsql driver, in shell is command "\d table_name", and this command return "table_name" table structure. But I have problem with Npgsql:
\d - command not work with npgsql (using Visual Studio 2008, C#), when I try execute "\\d table_name" command I have "ERROR: 42601 syntax error at or near "\"". Maybe you know how I can get table information?
Thx ;)

Newer Messages  Older Messages
Powered By FusionForge