PostgreSQL bit/bool

[picrap]

While looking at DbLinq.PostgreSql parameter generation (for writes),
I got a new error with apparently and bool/bit type mismatch on
"Discontinued". I can keep a hack on it, but the SQL script probably
contains an error on it.

[gmo...@gmail.com]

Problems with the boolean 'Discontinued' field keep coming up, in
Oracle and Posgres.
Did you get it when executing createDB_Northwind_pg.sql?

Now that it came up again, I googled the difference between BIT and
BOOL.
Apparently, "BIT" stands for fixed-length bit array, and is mandated
by SQL standard.
BOOL is an addition, Oracle does not have it, AFAIK.

I assume the good mapping between SQL and C# would be:
Postgresql BIT(size 1) -> C# bool
Postgresql BIT(size X) -> C# System.Collections.BitArray or (bool[] ?)

Pascal, can you confirm which line of SQL script was failing, and
provide the exact error?
Andrus, you are our Posgres expert, could you correct the script (once
we know the error)?

Thanks guys,
Jiri

[picrap]

The "bool problem" has no typical answer:
- on Oracle, it is generally a number with a short range (number(1))
- on databases supporting bool, it is probably better to use it.
- the bit type, when supported, serves a different purpose, as you
mentionned, it is a BitArray.
I just mean that we must consider a specific solution per database,
and not use bit as an universal answer.

The error comes from the fact that NpgsqlParameter is now built using
standard DbType and not the NpgsqlDbType. Apparently, PostgreSQL uses
a specific conversion when the specific type NpgsqlDbType is set to
NpgsqlDbType.Bit.

Semantically, our "Discontinued" database field is a boolean, and not
a bit, and this is the correction I suggest.

[Andrus Moor]

> Andrus, you are our Posgres expert, could you correct the script (once
> we know the error)?

1. I looked into rev 246 createDB_Northwind_pg.sql script.

I recommend to change bit to bool .
bit is exotic datatype in PostgreSQL and is rarely used. I have never used
it.
I use bool columns without issues.

2. npgsql driver version shipped with DbLinq is probably 2.0beta2 , not
alpha.

3. PostgreSQL name is exactly PostgreSQL (note the capitalization).

4. After refactoring sln file contains solution folders. This causes
warnings
when C# Express Edition is used.

5. I have never used bulk insert (it was added too late for me to npgsql, in
Oct 2007 and is currently only in beta version) . I have no plans to use and
know very few about it.
There is no ADO .NET standard for bulk insert. This is custom implementation
which needs PostgreSQL COPY command file structure.

From user manual:

The simplest method is to provide a readable file handle to the CopyIn
operation constructor. Upon start, the copy in operation will read whole
contents of given stream and push them to the server.

See to it that you set SyncNotification=true in database connection string.
This is to catch any anomaly reports during import to prevent deadlock
between client and server network buffers.
1.. Create NpgsqCopyIn object with a stream providing the data to input
into database
2.. Call Start() to initiate copy operation. The operation is completed
immediately.
3.. If Start() throws an exception, call NpgsqlCopyIn.Cancel() to cancel
an ongoing operation and clear connection back to Ready For Query state.
Otherwise your connection may stay in copy mode, unusable for anything else.
using System;
using System.Data;
using Npgsql;

public class CopyInExample
{

public static void Main(String[] args)
{

conn = new NpgsqlConnection("Server=127.0.0.1;User
id=npgsql_tests;password=npgsql_tests;SyncNotification=true;");
conn.Open();

NpgsqlCommand command = new NpgsqlCommand("COPY myCopyTestTable FROM
STDIN", conn);
NpgsqlCopyIn cin = new NpgsqlCopyIn( command, conn,
Console.OpenStandardInput() ); // expecting input in server encoding!
try
{
cin.Start();
}
catch(Exception e)
{
try
{
cin.Cancel("Undo copy");
}
catch(NpgsqlException e2)
{
// we should get an error in response to our cancel request:
if( ! (""+e2).Contains("Undo copy") )
{
throw new Exception("Failed to cancel copy: " + e2 + "
upon failure: " + e);
}
}
throw e;
}
}
}
If you wish to provide the data from inside your application, you can use a
normal writable stream:

1.. See to it that you set SyncNotification=true in your database
connection string. This is to catch any anomaly reports during import to
prevent deadlock between client and server network buffers.
2.. Create NpgsqCopyIn object without specifying a stream
3.. Call Start() to initiate copy operation
4.. Write your data in correct format and encoding into
NpgsqlCopyIn.CopyStream
5.. During the operation the connection can not be used for anything else.
6.. Call CopyStream.Close() or NpgsqlCopyIn.End() to complete writing
7.. To cancel an ongoing operation and clear connection back to Ready For
Query state call NpgsqlCopyIn.Cancel().
8.. Upon failure call NpgsqlCopyIn.Cancel() to cancel an ongoing operation
and clear connection back to Ready For Query state. Otherwise your
connection may stay in copy mode, unusable for anything else.
using System;
using System.Data;
using Npgsql;

public class CopyInExample
{

public static void Main(String[] args)
{

conn = new NpgsqlConnection("Server=127.0.0.1;User
id=npgsql_tests;password=npgsql_tests;SyncNotification=true;");
conn.Open();

NpgsqlCommand command = new NpgsqlCommand("COPY myCopyTestTable FROM
STDIN", conn);
NpgsqlCopyIn cin = new NpgsqlCopyIn( command, conn );

Stream inStream = Console.OpenStandardInput();
Encoding inEncoding = System.Text.Encoding.ASCII;
Encoding serverEncoding = System.Text.Encoding.BigEndianUnicode; //
example assumption

try
{
cin.Start();
Stream copyInStream = cin.CopyStream;
byte[] buf = new byte[9];
int i;
while( (i = inStream.Read(buf,0,buf.Length)) > 0 )
{
buf = System.Text.Convert( inEncoding, serverEncoding, buf,
0, i );
copyInStream.Write( buf, 0, i );
}
copyInStream.Close(); // or cin.End(), if you wish
}
catch(Exception e)
{
try
{
cin.Cancel("Undo copy"); // Sends CopyFail to server
}
catch(Exception e2)
{
// we should get an error in response to our cancel request:
if( ! (""+e2).Contains("Undo copy") )
{
throw new Exception("Failed to cancel copy: " + e2 + "
upon failure: " + e);
}
}
throw e;
}
}
}

[gmo...@gmail.com]

Pascal, what range of values can you store in an Oracle "number(1)"?

And Andrus, your code example is excellent, tomorrow I will install
PostgreSQL (note I learned the correct capitalization!) on this new
laptop, and try to get Bulk Insert going.

Also, will edit the northwind create script.

Good night gentlemen,
Jiri

[picrap]

Mmmmh... booleans?