Jump to content

Perl+SQL


Maheshwaran
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...