Thread: 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

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

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

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

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

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