C++Builder table exist

On 13/03/2013 10:59, Charl Roux wrote:

Hi,

I have migrated from MySQL to PostgreSQL. I am running on WindowsXP SP3,
C++Builder6, PostgreSQL9.2, pgExpress4.6(database driver)
I have the following scenarion. If my applications starts up, it will
test if a certain table exists within the database. If not, it will
create the table, else it will not re-create the table. This worked fine
when I used MySQL, but with PostgreSQL not. The first time that I run
the application, I do not get an error and the table is created. The
second time I run the application, the application gives an error that
the table already exists. This means that my method of testing if the
table does exist, does not work with PostgreSQL, with the following
message: ERROR: relation "querybackup" already exists
My code is as follows:

Well, if the table already exists and you try to create it, you're
naturally going to get an error; so you'll need to check for its
existence first. You probably want to do:

create table if not exists querybackup ....

void __fastcall TfrmMain::FormCreate(TObject *Sender)
{
int errorCode;
TStringList *tableList = new TStringList;
frmDataModule->eyeConnection->GetTableNames(tableList);

// create queryBackup table if does not exist
AnsiString SQL = "CREATE TABLE queryBackup( queryName VARCHAR(30)
PRIMARY KEY,query VARCHAR(10000))";

You could just use the "text" type for the "query" column, and then you
don't have to worry about the length of data going into it; the downside
is that in Delphi/C++ Builder I think it gets mapped to a memo type
rather than a string.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 13/03/2013 11:46, Charl Roux wrote:

Thanks for the response.

1.
I have used the following line to determine if the table does exist in
the database:
if( tableList->IndexOf("queryBackup") < 0 )
errorCode = frmDataModule->eyeConnection->ExecuteDirect(SQL);

Please don't top-post: it makes the discussion hard to follow. Also,
please reply to the list rather than directly to me.

I know Delphi rather than C++ Builder, so I may be missing something,
but let's look at your code anyway:

void __fastcall TfrmMain::FormCreate(TObject *Sender)
{
int errorCode;
TStringList *tableList = new TStringList;
frmDataModule->eyeConnection->GetTableNames(tableList);

// create queryBackup table if does not exist
AnsiString SQL = "CREATE TABLE queryBackup( queryName VARCHAR(30)
PRIMARY KEY,query VARCHAR(10000))";
if( tableList->IndexOf("queryBackup") < 0 )
errorCode = frmDataModule->eyeConnection->ExecuteDirect(SQL);
}

I'm guessing that the GetTableNames method is supposed to populate the
TStringList with a list of available tables - is that right? If so, have
you established that tableList actually contains anything after the call
to GetTableNames?

Another point is that PostgreSQL identifiers are folded to lower-case
automatically - so maybe IndexOf("querybackup") will return something
different to IndexOf("queryBackup").

This worked perfectly with MySQL, now with PostgreSQL it is as if the line
if( tableList->IndexOf("queryBackup") < 0 )
is ignored or not understood by C++Builder

Try the "...if not exists..." qualifier I mentioned in my last email;
using that, you can just issue the "create table" command without having
to check in your code whether the table exists.

2.
I am using VARCHAR(30), to use as little space as possible, and allowing
up to 30 characters. With MySQL the alternative was CHAR(30) which uses
a constant amount of space. So with TEXT type, will it only use the
amount of space required, according to the amount of characters the user
entered?

Yes. In fact, there's no real reason, space- or performance-wise, to use
varchar(n) unless you really need the limit on the length of the string.
You can read all about it here:

http://www.postgresql.org/docs/9.2/static/datatype-character.html

As I said before, the only thing to watch (in my Delphi experience
anyway) is that if you're binding DB columns to data-aware controls,
TEXT gets mapped to a memo type rather than to a string.

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Ray,

Thanks for all the help. It works perfectly.

void __fastcall TfrmMain::FormCreate(TObject *Sender)
{
unsigned int errorCode;

AnsiString SQL = "CREATE TABLE IF NOT EXISTS queryBackup( queryName text PRIMARY KEY, query text )";
errorCode = frmDataModule->eyeConnection->ExecuteDirect(SQL);
}

Charl

On 13/03/2013 13:19, Charl Roux wrote:

Hi Ray,

Thanks for all the help. It works perfectly.

void __fastcall TfrmMain::FormCreate(TObject *Sender)
{
unsigned int errorCode;

AnsiString SQL = "CREATE TABLE IF NOT EXISTS queryBackup( queryName
text PRIMARY KEY, query text )";
errorCode = frmDataModule->eyeConnection->ExecuteDirect(SQL);
}

Hi Charl,

That's great - glad it works. That seems like the simplest solution.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2013-03-13, Charl Roux <charl.roux@hotmail.com> wrote:

--_51d77859-0e03-4afa-bde6-853bee9c0a11_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

appologies for the formatting, gmane did something to your email that
SLRN didn't like.

void __fastcall TfrmMain::FormCreate(TObject *Sender)
{
int errorCode;
TStringList *tableList = new TStringList;
frmDataModule->eyeConnection->GetTableNames(tableList);

// create queryBackup table if does not exist
AnsiString SQL = "CREATE TABLE queryBackup( queryName VARCHAR(30) PRIMARY KEY ,query VARCHAR(10000))";
if( tableList->IndexOf("queryBackup") < 0 )
errorCode = frmDataModule->eyeConnection->ExecuteDirect(SQL);
}

ERROR: relation "querybackup" already exists.

The problem is you're checking for queryBackup and then creating
querybackup when it doesn't exist. (note: capitalisation)

http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html

This case-folding is one of the few places where postgres deliberately
breaks the sql standard (AIUI standard wants case folded upwards).

--
⚂⚃ 100% natural

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks. Case-folding was my problem.
Is there any way of getting PostgreSQL to work according to the SQL standard (The folding of
unquoted names to lower case in PostgreSQL is incompatible with the SQL
standard, which says that unquoted names should be folded to
upper case.), so there is no need for me to add quotes to all names?

Charl Roux wrote:

Is there any way of getting PostgreSQL to work according to the SQL standard (The folding of unquoted
names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted
names should be folded to upper case.), so there is no need for me to add quotes to all names?

No, PostgreSQL will always ford to lowercase.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/18/2013 03:25 AM, Charl Roux wrote:

Thanks. Case-folding was my problem.
Is there any way of getting PostgreSQL to work according to the SQL
standard (The folding of unquoted names to lower case in PostgreSQL is
incompatible with the SQL standard, which says that unquoted names
should be folded to upper case.), so there is no need for me to add
quotes to all names?

As far I know the only difference would be whether the table name you
get in the error comes back as lower case or upper case. In your
situation where you are using a quoted mixed case both folding styles
would lead to an error. In that aspect the SQL standard and Postgres
follow the same rule, if the name is quoted on creation the case it was
quoted in must be maintained on subsequent use. If you do not want to
quote identifiers on use then do not quote then on creation:

test=> create TABLE case_test("MixedCaseQuoted" varchar,
MixedCaseUnQuoted varchar);
CREATE TABLE
test=> \d+ case_test
Table "utility.case_test"
Column | Type | Modifiers | Storage |
Description
-------------------+-------------------+-----------+----------+-------------
MixedCaseQuoted | character varying | | extended |
mixedcaseunquoted | character varying | | extended |
Has OIDs: no

test=> SELECT mixedcasequoted from case_test ;
ERROR: column "mixedcasequoted" does not exist
LINE 1: SELECT mixedcasequoted from case_test ;
^
test=> SELECT mixedcaseunquoted from case_test ;
mixedcaseunquoted
-------------------
(0 rows)

test=> SELECT "MixedCaseQuoted" from case_test ;
MixedCaseQuoted

-----------------

(0 rows)

test=> SELECT MixedCaseUnQuoted from case_test ;
mixedcaseunquoted

-------------------

(0 rows)

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general