[SOLVED] C sqlite program gives unable to open database file
ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
C sqlite program gives unable to open database file
Hi
I wrote this simple sqlite C program for just some tests it works fine but when it reaches more that 1000 in for statement it gives me:
Quote:
"unable to open database file"
I am pretty sure that no other process or thread opens or locked it but I dont know how to solve my problem because I can not find my error.
this is my source:
Code:
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <time.h>
#define SQLITE_MAIN_BASE "/var/"
#define SQLITE_DEVLOG_BASE "./"
#define Create_All_Database_Error 1
void addlog(const char * logtext,const char *logpath)
{
struct tm *timeinfo;
time_t rawtime;
if (logpath != NULL )
{
if (strlen(logpath) > 4)
{
FILE *Lfile;
Lfile = fopen(logpath,"a");
if (Lfile)
{
char cur_dtime[50];
time ( &rawtime );
timeinfo = localtime (&rawtime);
strcpy(cur_dtime,asctime(timeinfo));
cur_dtime[strlen(cur_dtime)-1] = '\0';
fputs(cur_dtime,Lfile);
fputs(" :: ",Lfile);
fputs(logtext,Lfile);
fputs("\n",Lfile);
fclose(Lfile);
}
else
{
fprintf(stderr,"can not open log file %s\n",logpath);
}
}
}
}
int MySqlite_Exec(const char *dbname,const char *query,sqlite3_stmt **retStmt,const char *queryTail2,const char *logpath,int logfd,int mode)
{
sqlite3 *db;
char logmessage[1500];
char dbfilepath[150];
int rc=0;
sprintf(dbfilepath,"%s%s",SQLITE_DEVLOG_BASE,dbname);
fprintf(stdout,"%s\n",query);
fprintf(stdout,"%s\n",dbfilepath);
rc = sqlite3_open(dbfilepath, &db);
// if( rc )
while(sqlite3_open(dbfilepath, &db))
{
sprintf(logmessage,"1Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
// addlog(logmessage,logpath);
// printf("1\n");
sleep(10);
sqlite3_close(db);
// return sqlite3_errcode(db);
return 0;
}
printf("10\n");
if( sqlite3_prepare_v2(db, query, strlen(query)+1, retStmt,NULL) != SQLITE_OK )
{
sprintf(logmessage,"2Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
// addlog(logmessage,logpath);
while(sqlite3_prepare_v2(db, query, strlen(query)+1, retStmt,NULL) == SQLITE_BUSY)
{
sprintf(logmessage,"2Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
// addlog(logmessage,logpath);
sleep(1);
}
}
printf("12\n");
if (mode==0)
{
printf("12\n");
if (sqlite3_step(*retStmt) != SQLITE_DONE)
{
sprintf(logmessage,"3Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
}
sqlite3_finalize(*retStmt);
printf("2closeeeeeee\n");
rc=sqlite3_close(db);
while(rc != SQLITE_OK)
{
printf("yet closing\n");
if (rc == SQLITE_BUSY)
{
printf("it is busy\n");
}
sleep(1);
rc=sqlite3_close(db);
}
}
return 0;
}
int Create_SqltDB(void)
{
char hostip[20],
LOG_FILE[100],
query[1000],
dbfilepath[100],
logmessage[1500];
int result=0;
const char *queryTail;
sqlite3_stmt *retStmt;
sprintf(LOG_FILE,"/var/log/Emain.log");
sprintf(query,"create table if not exists lastuptime(row integer primary key not NULL,microupdatetime double default 0 not NULL,time double default 0 not NULL);");
if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,0))
{
return Create_All_Database_Error;
}
int i=0;
for (i=0; i<1000000;i++)
{
sprintf(query,"insert into lastuptime(microupdatetime,time) values (%i,%i);",i,i);
if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,0))
{
return Create_All_Database_Error;
}
}
sprintf(query,"select * from lastuptime;");
if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,1))
{
return Create_All_Database_Error;
}
do
{
result = sqlite3_step (retStmt) ;
if (result == SQLITE_ROW) /* can read data */
{
printf(" %d \t|\t %f \t|\t '%f' \n",\
sqlite3_column_int(retStmt,0),\
sqlite3_column_double(retStmt,1),\
sqlite3_column_double(retStmt,2)) ;
}
else
{
printf("no data\n");
}
} while (result == SQLITE_ROW) ;
}
int main()
{
Create_SqltDB();
return 0;
}
and this is my out put:
Quote:
insert into lastuptime(microupdatetime,time) values (1011,1011);
./lastuptime
10
12
12
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1012,1012);
./lastuptime
10
12
12
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1013,1013);
./lastuptime
10
12
12
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1014,1014);
./lastuptime
10
12
12
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1015,1015);
./lastuptime
10
12
12
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1016,1016);
./lastuptime
10
12
12
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1017,1017);
./lastuptime
10
12
12
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1018,1018);
./lastuptime
10
12
12
3Error on "insert into lastuptime(microupdatetime,time) values (1018,1018);" : 14 unable to open database file
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1019,1019);
./lastuptime
1Error on "insert into lastuptime(microupdatetime,time) values (1019,1019);" : 14 unable to open database file
insert into lastuptime(microupdatetime,time) values (1020,1020);
./lastuptime
1Error on "insert into lastuptime(microupdatetime,time) values (1020,1020);" : 14 unable to open database file
insert into lastuptime(microupdatetime,time) values (1021,1021);
./lastuptime
1Error on "insert into lastuptime(microupdatetime,time) values (1021,1021);" : 14 unable to open database file
..
..
..
the error continues none stop.
I have to mention that my hardware is an embedded arm named cubieboard with linaro ubuntu on it
what is my problem how can I solve it.
Your program keeps opening the database file, but never closes it. At around 1000 open files, there are no more file handles available for your process.
I think you were right as I checked with lsof command I am openning db file more than 1000 times but why?
I changed my db file like this but I had no success:
Code:
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <time.h>
#define SQLITE_MAIN_BASE "/var/"
#define SQLITE_DEVLOG_BASE "./"
#define Create_All_Database_Error 1
void addlog(const char * logtext,const char *logpath)
{
struct tm *timeinfo;
time_t rawtime;
if (logpath != NULL )
{
if (strlen(logpath) > 4)
{
FILE *Lfile;
Lfile = fopen(logpath,"a");
if (Lfile)
{
char cur_dtime[50];
time ( &rawtime );
timeinfo = localtime (&rawtime);
strcpy(cur_dtime,asctime(timeinfo));
cur_dtime[strlen(cur_dtime)-1] = '\0';
fputs(cur_dtime,Lfile);
fputs(" :: ",Lfile);
fputs(logtext,Lfile);
fputs("\n",Lfile);
fclose(Lfile);
}
else
{
fprintf(stderr,"can not open log file %s\n",logpath);
}
}
}
}
MySqlite_close(sqlite3 *db)
{
int i=0,
rc=0;
rc=sqlite3_close(db);
while(rc != SQLITE_OK)
{
printf("yet closing\n");
if (rc == SQLITE_BUSY)
{
printf("it is busy\n");
i++;
if ( i > 10 )
{
return rc;
}
}
sleep(1);
rc=sqlite3_close(db);
}
printf("2closeeeeeee\n\n");
return 0;
}
int MySqlite_Exec(const char *dbname,const char *query,sqlite3_stmt **retStmt,const char *queryTail2,const char *logpath,int logfd,int mode)
{
sqlite3 *db;
char logmessage[1500];
char dbfilepath[150];
int rc=0;
retStmt=NULL;
sprintf(dbfilepath,"%s%s",SQLITE_DEVLOG_BASE,dbname);
fprintf(stdout,"%s\n",query);
// fprintf(stdout,"%s\n",dbfilepath);
rc = sqlite3_open(dbfilepath, &db);
// if( rc )
while(sqlite3_open(dbfilepath, &db))
{
sprintf(logmessage,"1Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
// addlog(logmessage,logpath);
// printf("1\n");
sleep(10);
MySqlite_close(db);
// return sqlite3_errcode(db);
return 0;
}
printf("10\n");
if( sqlite3_prepare_v2(db, query, strlen(query)+1, retStmt,NULL) != SQLITE_OK )
{
sprintf(logmessage,"2Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
// addlog(logmessage,logpath);
while(sqlite3_prepare_v2(db, query, strlen(query)+1, retStmt,NULL) == SQLITE_BUSY)
{
sprintf(logmessage,"2Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
// addlog(logmessage,logpath);
sleep(1);
}
}
printf("12\n");
if (mode==0)
{
printf("222\n");
if (sqlite3_step(*retStmt) != SQLITE_DONE)
{
sprintf(logmessage,"3Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
}
if ( *retStmt != NULL )
{
printf("retStmt is not NULL\n");
sqlite3_step(*retStmt);
while(sqlite3_finalize(*retStmt)!=SQLITE_OK)
{
sprintf(logmessage,"20Error on : %u %s ",sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
printf("finilized NOT ok\n");
sleep(1);
}
printf("finilized ok\n");
// sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}
else
{
printf("retStmt is NULL\n");
}
MySqlite_close(db);
}
return 0;
}
int Create_SqltDB(void)
{
char hostip[20],
LOG_FILE[100],
query[1000],
dbfilepath[100],
logmessage[1500];
int result=0;
const char *queryTail;
sqlite3_stmt *retStmt;
sprintf(LOG_FILE,"/var/log/Emain.log");
sprintf(query,"create table if not exists lastuptime(row integer primary key not NULL,microupdatetime double default 0 not NULL,time double default 0 not NULL);");
if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,0))
{
return Create_All_Database_Error;
}
int i=0;
for (i=0; i<1000000;i++)
{
sprintf(query,"insert into lastuptime(microupdatetime,time) values (%i,%i);",i,i);
if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,0))
{
return Create_All_Database_Error;
}
}
sprintf(query,"select * from lastuptime;");
if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,1))
{
return Create_All_Database_Error;
}
do
{
result = sqlite3_step (retStmt) ;
if (result == SQLITE_ROW) /* can read data */
{
printf(" %d \t|\t %f \t|\t '%f' \n",\
sqlite3_column_int(retStmt,0),\
sqlite3_column_double(retStmt,1),\
sqlite3_column_double(retStmt,2)) ;
}
else
{
printf("no data\n");
}
} while (result == SQLITE_ROW) ;
}
int main()
{
Create_SqltDB();
return 0;
}
I think my log shoes that everything ok but where is my mistake:
Quote:
create table if not exists lastuptime(row integer primary key not NULL,microupdatetime double default 0 not NULL,time double default 0 not NULL);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (0,0);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1,1);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (2,2);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (3,3);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (4,4);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (5,5);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (6,6);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (7,7);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (8,8);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (9,9);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (10,10);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (11,11);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (12,12);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
.
.
.
.
.
insert into lastuptime(microupdatetime,time) values (1017,1017);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
insert into lastuptime(microupdatetime,time) values (1018,1018);
10
12
222
3Error on "insert into lastuptime(microupdatetime,time) values (1018,1018);" : 14 unable to open database file
retStmt is not NULL
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok
I found that I have memory leak too what happened to my code how can I solve it?
MySqlite_Exec() is being called from within a loop in Create_SqltDB(). Unless there is something within the sqlite3 library that internally prevents it, the sqlite3 struct could have an FD ( file descriptor ) associated with it, that is being over written by the second sqlite3_open() call.
So after MySqlite_Exec() is called 1,000 times from within the loop, you could have 1,000 *extra* FD's that aren't actively being used.
Code:
int MySqlite_Exec(const char *dbname,const char *query,sqlite3_stmt **retStmt,const char *queryTail2,const char *logpath,int logfd,int mode)
{
sqlite3 *db;
char logmessage[1500];
char dbfilepath[150];
int rc=0;
retStmt=NULL;
sprintf(dbfilepath,"%s%s",SQLITE_DEVLOG_BASE,dbname);
fprintf(stdout,"%s\n",query);
// fprintf(stdout,"%s\n",dbfilepath);
rc = sqlite3_open(dbfilepath, &db); // <=== SHOULD THIS HAVE BEEN COMMENTED OUT WHEN THE NEXT STATEMENT WAS COMMENTED OUT?
// if( rc )
while(sqlite3_open(dbfilepath, &db))
{
sprintf(logmessage,"1Error on \"%s\" : %u %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
printf("%s\n",logmessage);
// addlog(logmessage,logpath);
// printf("1\n");
sleep(10);
MySqlite_close(db);
// return sqlite3_errcode(db);
return 0;
}
O.K., I think that you may be making things too hard for yourself.
You should open your data base only once, not every time you want to execute a SQL statement.
The sqlite3_exec function makes running heterogeneous SQL statements fairly simple.
Callback functions should be used to process statement results.
Here's a program wrote several years ago as an example. (Note that this program uses a ephemerial data base.)
Code:
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqlite3.h>
#define debug 0
extern FILE * stdin;
extern FILE * stdout;
extern FILE * stderr;
/**********************************************/
/* */
/* SQLite error report helper function */
/* */
/**********************************************/
int check_code( // Return 0 is OK, 1 otherwise
sqlite3 * db, // Data base information pointer
sqlite3_stmt *stmt, // Statement being checked
const int rc, // Value to check
const char * text, // Text to print if check fails
const int target) // SQLite expected return code
{
if (rc != target) {
fprintf(stderr, "%s: %s\n", text, sqlite3_errmsg(db));
if (stmt) sqlite3_finalize(stmt);
return 1;
}
return 0;
}
/******************************************************/
/* */
/* Call-back function used by sqlite3_exec() */
/* */
/******************************************************/
static int process_sql( // Return 0 on success
sqlite3 *db, // Data base being processed
int argc, // Number of row values returned by query
char **argv, // Data values returned
char **azColName) // Name of the data columns returned
{
if (argc < 0) return 1;
if (partition(argv[0], db)) {
// If we get here we had a data base error. Abort.
sqlite3_close(db);
exit (1);
}
// Partition the root word
if (partition(argv[0], db)) {
// If we get here we had a data base error. Abort.
sqlite3_close(db);
exit (1);
}
return 0;
}
int main(int argc, char **argv)
{
char word[256]; // Current word holder
char msg[256]; // Potential error message holder
int i;
register int j,k,l; // Temporary integers (loop index, etc.)
// SQLite stuff
sqlite3 *db;
sqlite3_stmt *stmt = 0;
sqlite3_stmt *root = 0;
int rc;
char create[]= "CREATE TABLE INPUT_FILE (RECORD_NUMBER PRIMARY KEY AUTO,\
FIELD_1 NUMBER,\
FIELD_2 NUMBER,\
FIELD_3 NUMBER,\
FIELD_4 NUMBER,\
FIELD_5 NUMBER,\
FIELD_6 NUMBER,\
FIELD_7 NUMBER,\
FIELD_8 NUMBER)";
char insert[]= "INSERT INTO INPUT_FILE VALUES (";
char roots[]= "CREATE TABLE ROOTS (ROOT TEXT PRIMARY KEY ON CONFLICT IGNORE)";
char getroot[]="SELECT ROOT FROM ROOTS ORDER BY ROOT";
char command[256]; // Command string holder
char *errmsg = (char *) NULL;
// Create a temporary data base to hold the strings found
rc = sqlite3_open_v2(NULL, &db, 0, errmsg);
sprintf(msg, "%s: Could not open an ephemeral SQLite data base", argv[0]);
if (check_code(db, stmt, rc, msg, SQLITE_OK)) {
close(db);
exit(1);
}
// Create the table to store the root "words"
rc = sqlite3_exec(db, roots, NULL, NULL, &errmsg);
sprintf(msg, "%s: Could not create table ROOTS", argv[0]);
if (check_code(db, stmt, rc, msg, SQLITE_OK)) {
sqlite3_close(db);
exit(1);
}
// Create the table to store the "words"
rc = sqlite3_exec(db, create, NULL, NULL, &errmsg);
sprintf(msg, "%s: Could not create table WORDS", argv[0]);
if (check_code(db, stmt, rc, msg, SQLITE_OK)) {
sqlite3_close(db);
exit(1);
}
// End SQLite stuff
// For each input string . ' '
while (fgets(word, 256, stdin)) {
// Insert all permutations of "word' into the temporary data base
i = 1;
while (comb(word, &i) >= 0) {
sqlite3_snprintf(255, command, "INSERT INTO WORDS (WORD) VALUES(%Q)", word);
rc = sqlite3_exec(db, command, NULL, NULL, &errmsg);
sprintf(msg, "%s: Problem inserting WORD (\"%s\")", argv[0], word);
if (check_code(db, stmt, rc, msg, SQLITE_OK)) {
sqlite3_free(errmsg);
sqlite3_close(db);
exit (1);
}
}
}
// O.K., now all the unique, permuted strings from the input are ready to be partitioned. Do it.
//
// Note: The processing is done in the call-back routine, "process_sql"
//
rc = sqlite3_exec(db, select, (sqlite3_callback) process_sql, db, &errmsg);
sprintf(msg, "%s: Problem creating WORDS", argv[0]);
if (check_code(db, stmt, rc, msg, SQLITE_OK)) {
sqlite3_free(errmsg);
sqlite3_close(db);
exit(1);
}
// Write the "words" stored in the data base to the output file
rc = sqlite3_prepare_v2(db, select, -1, &stmt, 0);
if (rc != SQLITE_OK) {
fprintf(stderr,"%s: Error preparing %s: %s\n", argv[0], select, sqlite3_errmsg(db));
sqlite3_finalize(stmt);
exit(1);
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
strncpy(word, (char *) sqlite3_column_text(stmt, 0), 255);
fprintf(stdout,"%s\n", word);
}
sprintf(msg, "%s: Error retrieving data from WORDS", argv[0]);
check_code( db, stmt, rc, msg, SQLITE_DONE);
sqlite3_finalize(stmt);
sqlite3_close(db);
exit ((rc==SQLITE_DONE) ? 0 : 1);
}
Note: The RECORD_NUMBER I define in the table I created in that code, and your "row" number, are not really needed since sqlite will automatically create a rowid NUMBER PRIMARY KEY AUTO in any table created without a primary key.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.