Checking if a table exist

------_=_NextPart_001_01C66A4A.7D2B6944
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hi All,
=20
Does anyone know of a good way to check if a table exist disregarding
whether the table has data or not?
=20
Thanks.
=20
Peter


This E-mail message is for the sole use of the intended recipient(s) and =
may contain confidential and privileged information. Any unauthorized =
review, use, disclosure or distribution is prohibited. If you are not =
the intended recipient, please contact the sender by reply E-mail, and =
destroy all copies of the original message.

------_=_NextPart_001_01C66A4A.7D2B6944--

Re: Checking if a table exist

------=_Part_5617_15990472.1146183446540
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 4/27/06, Loo, Peter # PHX wrote:

> Does anyone know of a good way to check if a table exist disregarding
> whether the table has data or not?
>

Simplest is:

my $sth =3D $dbh->prepare("SELECT * FROM $tablename");
if ($sth) { ...table exists...probably; you might need to do $sth->execute
to be sure as different DBMS differ... }
else { ...table probably doesn't exist, or it exists but you don't hav=
e
select permission on it... }

Or you can play with table_info, etc.

--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_5617_15990472.1146183446540--

Re: Checking if a table exist

On Thu, 27 Apr 2006 17:17:26 -0700, Jonathan Leffler wrote:

Hi Jonathan

> my $sth = $dbh->prepare("SELECT * FROM $tablename");
> if ($sth) { ...table exists...probably; you might need to do $sth-
> >execute to be sure as different DBMS differ... }
> else { ...table probably doesn't exist, or it exists but you
> don't have select permission on it... }

I'd suggest "Select * from $tablename where 1=2" so that if you do need to call
execute, you return nothing, if at all possible.

--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

RE: Checking if a table exist

------_=_NextPart_001_01C66A5A.DAABD328
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Thanks Jonathan. I was thinking about doing something like this:
=20
@tables =3D $dbh->tables();
=20
if ((grep/$tableName/, @tables) <=3D 0) {
...
=20
=20
________________________________

From: Jonathan Leffler [mailto:jonathan.leffler@gmail.com]=20
Sent: Thursday, April 27, 2006 5:17 PM
To: Loo, Peter # PHX
Cc: DBI users
Subject: Re: Checking if a table exist




On 4/27/06, Loo, Peter # PHX wrote:


Does anyone know of a good way to check if a table exist
disregarding
whether the table has data or not?
=09


Simplest is:

my $sth =3D $dbh->prepare("SELECT * FROM $tablename");
if ($sth) { ...table exists...probably; you might need to do
$sth->execute to be sure as different DBMS differ... }
else { ...table probably doesn't exist, or it exists but you don't
have select permission on it... }=20

Or you can play with table_info, etc.

--=20
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."=20


This E-mail message is for the sole use of the intended recipient(s) and =
may contain confidential and privileged information. Any unauthorized =
review, use, disclosure or distribution is prohibited. If you are not =
the intended recipient, please contact the sender by reply E-mail, and =
destroy all copies of the original message.

------_=_NextPart_001_01C66A5A.DAABD328--

RE: Checking if a table exist

1. Look in the data dictionary
2. Select from the table and trap the appropriate error code

-----Original Message-----
From: Loo, Peter # PHX [mailto:Peter.Loo@source.wolterskluwer.com]=20
Sent: Thursday, April 27, 2006 4:33 PM
To: List - DBI users
Subject: Checking if a table exist

Hi All,
=20
Does anyone know of a good way to check if a table exist disregarding
whether the table has data or not?
=20
Thanks.
=20
Peter


This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: Checking if a table exist

Reidy, Ron wrote:
> 1. Look in the data dictionary
> 2. Select from the table and trap the appropriate error code
>
> -----Original Message-----
> From: Loo, Peter # PHX [mailto:Peter.Loo@source.wolterskluwer.com]
> Sent: Thursday, April 27, 2006 4:33 PM
> To: List - DBI users
> Subject: Checking if a table exist
>
> Hi All,
>
> Does anyone know of a good way to check if a table exist disregarding
> whether the table has data or not?

put 'SHOW TABLES;' into a hash and
if(exists $tables{'user'}) {

....

Re: Checking if a table exist

Hi Peter,

in case You just want to be shure not to destroy an already existing
table by creating it again, You can just isse an $dbh->do("CREATE
TABLE..."). An already existing table will never desroyed and a warning
will be raised.

Greetings

Robert

---------
Loo, Peter # PHX wrote:
> Hi All,
>
> Does anyone know of a good way to check if a table exist disregarding
> whether the table has data or not?
>
> Thanks.
>
> Peter
>
>
> This E-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply E-mail, and destroy all copies of the original message.
>