Maheshwaran 0 Posted June 19, 2019 Report Share Posted June 19, 2019 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 Quote Link to post Share on other sites
justsomeguy 1,135 Posted June 19, 2019 Report Share Posted June 19, 2019 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 Quote Link to post Share on other sites
Maheshwaran 0 Posted June 20, 2019 Author Report Share Posted June 20, 2019 Hi , Thanks for reply. Is there way to add new column in table if the column is not present using perl? Thanks, Maheshwaran V Quote Link to post Share on other sites
justsomeguy 1,135 Posted June 20, 2019 Report Share Posted June 20, 2019 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. Quote Link to post Share on other sites
Maheshwaran 0 Posted June 20, 2019 Author Report Share Posted June 20, 2019 How about the performance? Quote Link to post Share on other sites
Maheshwaran 0 Posted June 20, 2019 Author Report Share Posted June 20, 2019 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 Quote Link to post Share on other sites
justsomeguy 1,135 Posted June 20, 2019 Report Share Posted June 20, 2019 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. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.