SCM

[#1010716] using dblinq / npgsql, problems updating rows where primary key is UUID

View Trackers | Bugs | Download .csv | Monitor

Date:
2009-10-12 08:54
Priority:
3
State:
Open
Submitted by:
c white (vaevictus)
Assigned to:
Nobody (None)
Npgsql Version:
2.0
Category:
Group:
Resolution:
None
Summary:
using dblinq / npgsql, problems updating rows where primary key is UUID

Detailed description
Unfortunately I do not know whether this problem is due to NPGSQL or the implementation in the DBlinq project, so I have posted this bug to both support groups.

http://code.google.com/p/dblinq2007/issues/detail?id=152

Anyway, when an update command is performed on a table that has a UUID as a primary key, the parameterised query passes the UUID as an int (therefore does not surround the UUID with quotes). This of course results in an error.

I know that using UUID as primary key is not very good practice but this is what the client wants.

Hope you can help, or at least let me know whether or not dblinq is to blame.

Regards,
Craig

Followup

Message
Date: 2009-12-02 19:44
Sender: Josh Cooley

I would suspect a problem with dblinq unless there's a particular pattern used in dblinq that we should be supporting. Here's a unit test that shows inserting with parameters. We don't detect the type of query, so update should perform the same.

[Test]
public void TestUUIDDataType()
{

string createTable =
@"DROP TABLE if exists public.person;
CREATE TABLE public.person (
person_id serial PRIMARY KEY NOT NULL,
person_uuid uuid NOT NULL
) WITH(OIDS=FALSE);";
NpgsqlCommand command = new NpgsqlCommand(createTable, TheConnection);
command.ExecuteNonQuery();

string insertSql = "INSERT INTO person (person_uuid) VALUES (:param1);";
NpgsqlParameter uuidDbParam = new NpgsqlParameter(":param1", NpgsqlDbType.Uuid); // DbType.Guid works just as well
uuidDbParam.Value = Guid.NewGuid();

command = new NpgsqlCommand(insertSql, TheConnection);
command.Parameters.Add(uuidDbParam);
command.ExecuteNonQuery();

command = new NpgsqlCommand("SELECT person_uuid::uuid FROM person LIMIT 1", TheConnection);


object result = command.ExecuteScalar();
Assert.AreEqual(typeof(Guid), result.GetType());
}

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge