Calling user defined function in Insert command of Sqlite

Question

I have written a user defined function to calculate checksum for a given string. I need to insert the calculated hash value into the table. I used the following method

char msg[] = "Testing of the hash"
len = strlen(msg)
char *sql = "INSERT INTO TestHash (id,CheckSum) VALUES (?,hash(msg,len));"
sqlite3_prepare_v2(db, sql, -1, &res, 0);
sqlite3_step(res)

but sqlite throws a error "failed to prepare insert sql statement:no such column:msg". What is the right way to call a UDF in insert command.

Platform:linux Language:C database:sqlite

Thanks for the help in advance

CL. · Accepted Answer · 2016-06-08 18:59:56Z

2

You are already using a parameter for the ID value. Do the same for the other two values:

const char *sql = "INSERT INTO TestHash (id,CheckSum) VALUES (?,hash(?,?));"
/* error handling omitted */
rc = sqlite3_prepare_v2(db, sql, -1, &res, NULL);
sqlite3_bind_int(res, 1, ...);
sqlite3_bind_text(res, 2, msg, len, SQLITE_TRANSIENT);
sqlite3_bind_int(res, 3, len);
rc = sqlite3_step(res);

If the ID value should be NULL (for an autoincremented value), you should not use a parameter for it.

Your hash function should not need the length parameter.

answered Jun 8, 2016 at 18:59

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vikas Over a year ago

Both your method and Shadow's method work. which is better.

Vikas Over a year ago

had problem with sqlite_binding_text ,was sending wrong column number for second parameter

Shadow · Accepted Answer · 2016-06-08 12:14:54Z

1

msg is a C program level variable. In the sql command you cannot reference a C variable, you have to insert its content into the sql statement as a string literal. The same applies to the len variable.

You basically need to generate the following string within your C program:

char *sql = "INSERT INTO TestHash (id,CheckSum) VALUES (?,hash('Testing of the hash',19));"

answered Jun 8, 2016 at 12:14

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Collectives™ on Stack Overflow

Calling user defined function in Insert command of Sqlite

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest