I've pasted three SQLite files into my Windows' folder D:/Server/sqlite of my local Apache-based server, opened sqlite3 and created the file test.sqlite by the command .open --new test.sqlite.

Now I'm trying to create a table in the database and insert testing data into the table by the following PHP code

$pdo = new PDO('sqlite:D:\Server\sqlite\test.sqlite');

$pdo->exec('CREATE TABLE IF NOT EXISTS `tablename` (`id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) NOT NULL)');

$insert = $pdo->prepare("INSERT INTO tablename (id, name) VALUES (1, 'Test')");
$insert->execute();
$pdo->lastInsertId();

But I get the error

Fatal error: Uncaught Error: Call to a member function execute() on bool in

and I see that the file test.sqlite is not updated and it's still empty

What's wrong?

P.S. With MySQL this code works fine

CodePudding user response：

The CREATE TABLE statement is invalid, so the table is not created, which means that the INSERT statement fails.

The correct syntax to define an integer autoincremented column in SQLite is:

id INTEGER PRIMARY KEY 

or:

id INTEGER PRIMARY KEY AUTOINCREMENT

The differences with your code are:

• the use of the data type INTEGER (anything else will throw an error when used in conjunction with the keyword AUTOINCREMENT or if that is missing it will not create an autoincremented column)
• the use of the keyword AUTOINCREMENT (and not AUTO_INCREMENT which works in MySql) after the keywords PRIMARY KEY

See the demo.