Jump to content

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...