CODEWITHSUNDEEP
sqlite
Duraisamy
Duraisamy

Reputation: 27

multiple insert using sqlite query

while try into multiple insert value in sqlit3 ,It showing 21 error code.

sample query:

 insert into Assembly_master ('ASSEMBLY_MASTER_ID','ASSEMBLY_MASTER_KID','ASSEMBLY_MASTER_CODE','ASSEMBLY_MASTER_NAME','DISTRICT_MASTER_CODE','ASSEMBLY_MASTER_HINDI','DISTRICT_MASTER_KID') values ('1','1','76','HH','194',' ','1'),('2','2','101','ANGARA','1008','','545')

Upvotes: 0

Views: 89

Answers (2)

PositiveLogic
PositiveLogic

Reputation: 316

The error is caused by the sixth value of your second insert (,'',) <- Consecutive single quotes (doubled up like that) is SQLite's way of escaping a single quote (quoting from SQL As Understood By SQLite -> HERE):

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal.

This leaves a single quote character (not surrounded by single quotes) as the placeholder to be handled by SQLite, instead of the expected string constant ...and thus... the Error code 21 ( SQLITE_MISUSE ) is generated when binding is attempted.

Perhaps you intended the space character ' ' or NULL?

EDIT: Despite this answer being ~obviously wrong~ , the info within this answer is an SQLite "gotcha" that likely does not get much mention, so I hope it helps anyone who lands here via related search terms.

Upvotes: 0

CL.
CL.

Reputation: 180060

Your INSERT statement is correct, but only in SQLite 3.7.11 or later.

If you (might) have an earlier version, you should just use multiple INSERT statements.

Upvotes: 1

Related Questions