Обсуждение: C++Builder table exist
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:
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);
}
Thanks for the time.
Charl
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:
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);
}
Thanks for the time.
Charl
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
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
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
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
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?
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?
> To: pgsql-general@postgresql.org
> From: jasen@xnet.co.nz
> Subject: Re: [GENERAL] C++Builder table exist
> Date: Sat, 16 Mar 2013 06:16:33 +0000
>
> 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
> From: jasen@xnet.co.nz
> Subject: Re: [GENERAL] C++Builder table exist
> Date: Sat, 16 Mar 2013 06:16:33 +0000
>
> 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
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