Re: C++Builder table exist

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