BIT and BIT VARYING

[[email protected]]

Hello

We have moved over to pgDAC, and found some issues with BIT and BIT VARYING.

Look at the PostGreSQL manual 8.3:
http://www.postgresql.org/docs/8.3/inte ... e-bit.html

When creating table test, and then inserting values, this gives us an error when inserting.

CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');

If I remove the B, it works fine:
INSERT INTO test VALUES ('101', '00');

Our previous freeware componants worked well with the "B", but with pgDAC it doesn't. We can remove the "B", but I need to first determine if this is an error, or does pgDAC specifically made it that "B" is not needed.

Kind regards
Michael Posthuma

[Plash]

We could not reproduce any error. Please specify the error message that you have received.

[[email protected]]

Hi

I use the following sql:

INSERT INTO "public"."test_bit" ("ID","tst_bit") VALUES (:ID,B:tst_bit)

I give it values "111" for ID and "1" for tst_bit.

The error I get is:
'column "b$2" does not exist'

If I use:
INSERT INTO "public"."test_bit" ("ID","tst_bit") VALUES (:ID,:tst_bit)

it works fine. (without B before :tst_bit)

Cheers,
Michael

[Plash]

You should not use B before parameter names.

[[email protected]]

Hello

Yes, I agree that now I don't need to put a B before parameter names.
However, with our previous connection componants it was needed, and if you look at PostGreSQL documentation, it is also needed.
http://www.postgresql.org/docs/8.3/inte ... e-bit.html

I just want to make sure that it is intentionally like that, and that if we change our SQL statements, it will stay that way.

Cheers,
Michael

[Plash]

There are two ways of executing statements:
1. With preparation and real parameters (default in PgDAC). In this case parameter names are replaced with placeholders in SQL before executing it. For example, the following statement:

Code: Select all

SELECT * FROM table1 WHERE f1 = :param1 and f2 = :param2

is transformed to:

Code: Select all

SELECT * FROM table1 WHERE f1 = $1 and f2 = $2

In this case you cannot add B before parameters.

2. Without preparation. In this case parameter names are replaced with values before executing. For example, the following statement can be sent to the server:

Code: Select all

SELECT * FROM table1 WHERE f1 = '111' and f2 = '100'

This statement works if you add B before parameters.

You can enable executing without preparation by setting the UnpreparedExecute option of TPgQuery to True.

[[email protected]]

Hi

Is this scenario for your Oracle, MySQL and SQLServer comps as well?

Cheers,
Michael

[Plash]

Yes, you don't need to add B for any our components.