Perl+SQL

[Maheshwaran]

Hi,

I am getting following error when I try to add new column if the column not present.

can you please point me the error?

my $driver = "SQLite";
my $database = "tcc.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })   or die $DBI::errstr;

my $stmt = qq(CREATE TABLE ddc
        (reg_name TEXT PRIMARY KEY UNIQUE NOT NULL
        ););

my $p1 = "Col1";
$rv = $dbh->do(" IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ddc AND COLUMN_NAME = $p) BEGIN  ALTER TABLE ddc ADD $p END") or die $DBI::err;
 

DBD::SQLite::db do failed: near "IF": syntax error

[justsomeguy]

SQLIte does not have if statements.  It also does not have an information_schema database.  It seems like you found something that works in MySQL and you're assuming that it's also going to work in any other database, and that's not true.

It looks like you need to use the table_info pragma to get information about a table, which will return one record per column in the table.  Look through those results to see if the column exists and then alter the table if not.

https://www.sqlite.org/pragma.html#pragma_table_info

[Maheshwaran]

Hi ,

Thanks for reply.

Is there way to add new column in table if the column is not present using perl?

Thanks,

Maheshwaran V

[justsomeguy]

Just like I said.  Get the list of columns and figure out if it's already there or not, and add it if it's not.

[Maheshwaran]

How  about the performance?

[Maheshwaran]

Hi ,

Do you have sample example? I tried and I am not able to get sample example?

SQLIte does not have if statements.  It also does not have an information_schema database.  It seems like you found something that works in MySQL and you're assuming that it's also going to work in any other database, and that's not true.

It looks like you need to use the table_info pragma to get information about a table, which will return one record per column in the table.  Look through those results to see if the column exists and then alter the table if not.

https://www.sqlite.org/pragma.html#pragma_table_info

[justsomeguy]

I don't have an example of running a SQLite pragma, but if you do some research I'm sure you'll find examples.  It should return a result set of columns that you can use to determine if the column you want is already part of the table.