[sqlite] Crash after add column

[VasiliyF4]

Hi.
I Use C/Linux. After I try to ADD a column by run the querry "ALTER TABLE x
ADD y NUMERIC" from my application, I can't use the data base any more. If I
try to get or save any data at my DB it cause crash of the application.
Thanks
-- 
View this message in context: 
http://old.nabble.com/Crash-after-add-column-tp27809597p27809597.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie question - SQLite the best choice?

[Rich Shepard]

On Sat, 6 Mar 2010, Richard Cooke wrote:

> Our application could have up to 10,000 users via a public facing web
> site.  As a first stab at the schema, I thought I'd have one "Master User"
> database which will probably look like this:

Richard,

   If I recall correctly, SQLite does not do well with multiple, simultaneous
access. I suggest that you take a look here:
 

and look at the multiuser/network client-server tools built on top of
SQLite3.

   For our needs, we use SQLite as the embedded back end to single-user
applications and postgres or mysql for multiuser and Web-based apps.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie question - SQLite the best choice?

[Darren Duncan]

Richard Cooke wrote:
> Can I use the user_id (as a FOREIGN KEY) that resides in another 
> database to link the two DBs together?  I'm a newbie to all of theis 
> database design so I am using this project as a learning experience.  
>  From my limited knowledge of MySQL I think I could have everything in 
> one database and just have a bunch of tables.  If I use SQLite I'd have 
> a bunch of files (or smaller databases).  If there was a problem I can 
> see just losing one ueser's data and not the whole "ball" of data with 
> all the user's data.

This doesn't answer your main question exactly, but ...

A general point of database design is that whenever you have a bunch of 
entities 
where some of those entities are defined in terms of others, including some 
being constrained in terms of others, such as with a FOREIGN KEY, then it only 
makes sense to have all of those entities collected into the same single 
database.  With SQLite, that means put everything in a single file.

The main reason for this is that you can then take any single database in 
isolation from others and it is self-describing enough that you can properly 
interpret what you see in that database, including what constraints it has. 
With SQLite, where its files can be located anywhere, there is no system 
restriction keeping your related files together, so you don't want to use them 
such that no part of your database can be used if you easily don't have all the 
parts.

In your case, I would put everything, all your tables, in a single SQLite 
database file, if you use SQLite, same as you'd use a single database if you 
use 
PostgreSQL or other options instead.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SQLite in BCB 4

[a1rex]

You cannot push_back(NULL) null pointer. 
Fix is below:

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{
std::string  val;
char * ptr = 
(char*)sqlite3_column_text(statement, col);
if(ptr)
{
val = ptr;
}
else val = "";

values.push_back(val);   // JUZ NIE 
WYWALA
}
results.push_back(values);
}
else
{
break;   
}

Enjoy,
Samuel

BTW nice usage of STL + SQlite.



- Original Message 
From: Chimerian 
To: sqlite-users@sqlite.org
Sent: Sat, March 6, 2010 12:05:51 PM
Subject: [sqlite] Problem with SQLite in BCB 4

I have a problem with SQLite in BCB 4.0. I wrote my own application -
I found very useful information on 
http://www.dreamincode.net/forums/index.php?showtopic=122300

On form (Form1) I have: Memo1 and Button3.

File Unit1.cpp:


//---
#include 
#pragma hdrstop
#include 
#include "Unit1.h"
//---
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;

// DLL handle
HANDLE DLLHandle = LoadLibrary("sqlite3.dll");

// SQLite class
Database::Database(char* filename)
{
sqlite3_open = (int (__cdecl *)(const char *,
sqlite3**))GetProcAddress(DLLHandle,"sqlite3_open");

sqlite3_close = (int (__cdecl 
*)(sqlite3*))GetProcAddress(DLLHandle,"sqlite3_close");

sqlite3_prepare_v2 = (int (__cdecl *)(sqlite3*, const char*, int, 
sqlite3_stmt**, const
char**))GetProcAddress(DLLHandle,"sqlite3_prepare_v2");

sqlite3_column_count = (int (__cdecl
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_column_count");

sqlite3_step = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_step");

sqlite3_column_text = (const unsigned char (__cdecl *)(sqlite3_stmt*, 
int
iCol))GetProcAddress(DLLHandle,"sqlite3_column_text");

sqlite3_finalize = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_finalize");

database = NULL;
open(filename);
}

Database::~Database()
{
}

bool Database::open(char* filename)
{
if(sqlite3_open(filename, ) == SQLITE_OK)
{
Form1->Memo1->Lines->Add("You have access to base.");
return true;
}

return false;  
}

vector Database::query(char* query)
{
sqlite3_stmt *statement;
vector results;

if(sqlite3_prepare_v2(database, query, -1, , 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{

values.push_back((char*)sqlite3_column_text(statement, col));  
// HERE IS ERROR !
}
results.push_back(values);
}
else
{
break;  
}
}

sqlite3_finalize(statement);
}
  
return results;
}

void Database::close()
{
sqlite3_close(database);
}

//---
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{

}
//---

void __fastcall TForm1::Button3Click(TObject *Sender)
{
Database *db;
db = new Database("Database.sqlite");
db->query("CREATE TABLE a (a INTEGER, b INTEGER);");
db->query("INSERT INTO a VALUES(1, 2);");
db->query("INSERT INTO a VALUES(5, 4);");
vector result = db->query("SELECT a, b FROM a;");
for(vector::iterator it = result.begin(); it < result.end(); 
++it)
{
vector row = *it;
cout << "Values: (A=" << row.at(0) << ", B=" << row.at(1) << ")" << 
endl;
}
db->close();
}

[sqlite] Newbie question - SQLite the best choice?

[Richard Cooke]

Hi Folks,

Please forgive me if this isn't the proper forum to post this question 
but I am in need of some expert advice concerning if SQLite is the best 
choice for my application.

Our application could have up to 10,000 users via a public facing web 
site.  As a first stab at the schema, I thought I'd have one "Master 
User" database which will probably look like this:

*Master DB *- (would contain contact info for all of the "Users")
user_ID - PRIMARY KEY
last_name
first_name
email
street_addr1
street_addr2
city
state
zip
date_signed_up
date_to_renew
username
password

If I use SQLite, there would be another database that contains user 
setup data that describes the various different ways that their page 
would be displayed, each user could have up to 100 "subjects"and then 
each "subject" could have 10-25 "events".  So this database could look 
like this:

*User DB* - each user gets one of these that includes 3 tables 
(user_setup, subjects, events)
TABLE_USER_SETUP
 user_id (FOREIGN KEY)
 setup_id
 data1
 data2
 data3
 data4

TABLE_SUBJECTS
 subject_id PRIMARY KEY
 last_name
 first_name
 email
 street_addr1
 street_addr2
 city
 state
 zip
 password

TABLE_EVENTS
 events_id PRIMARY KEY
 type
 data
 subject_id

Can I use the user_id (as a FOREIGN KEY) that resides in another 
database to link the two DBs together?  I'm a newbie to all of theis 
database design so I am using this project as a learning experience.  
 From my limited knowledge of MySQL I think I could have everything in 
one database and just have a bunch of tables.  If I use SQLite I'd have 
a bunch of files (or smaller databases).  If there was a problem I can 
see just losing one ueser's data and not the whole "ball" of data with 
all the user's data.

You folks are the experts and I'd like to hear what you recommend.

Thanks,

Richard
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE3 Recompiling for FTS3

[Hector]

For Windows:

I followed the best of my ability recompiling the most current 
SQLITE3.DLL to get the FTS3 incorporated.  I added the compiler directive

SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS

and was able to create a SQLITE3.DLL but it faults when a example test 
C/C++ program runs.

No problem compiling with FTS3 disable.  But the DLLs are smaller than 
the official sqlite.org website v3.6.22 precompiled binaries For Windows.

My compile:  417,792 sqlite3.dll
Official:511,383 sqlite3.dll

The difference I can only see if the official has a dependency on  std 
C++ rtl msvcrt.dllwhere my compiled has no dependency on msvcrt.dll

I am not sure if I have the MS VS2005 project file correct, but I also 
recompiled under VC6 too.   All I did to create the project was to add 
all the *.C/*.H files to a project and added the defined (for release)

WIN32
NDEBUG
_WINDOWS
_USRDLL
SQLITE3_EXPORTS;
SQLITE_ENABLE_FTS3
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_COLUMN_METADATA

I guess, I ideally I would like to able to reproduce the same official 
size and then see how to get the FTS3 logic added.

Tips?

Thanks in Advance

--
HLS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with SQLite in BCB 4

[Chimerian]

I have a problem with SQLite in BCB 4.0. I wrote my own application -
I found very useful information on 
http://www.dreamincode.net/forums/index.php?showtopic=122300

On form (Form1) I have: Memo1 and Button3.

File Unit1.cpp:


//---
#include 
#pragma hdrstop
#include 
#include "Unit1.h"
//---
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;

// DLL handle
HANDLE DLLHandle = LoadLibrary("sqlite3.dll");

// SQLite class
Database::Database(char* filename)
{
sqlite3_open = (int (__cdecl *)(const char *,
sqlite3**))GetProcAddress(DLLHandle,"sqlite3_open");

sqlite3_close = (int (__cdecl 
*)(sqlite3*))GetProcAddress(DLLHandle,"sqlite3_close");

sqlite3_prepare_v2 = (int (__cdecl *)(sqlite3*, const char*, int, 
sqlite3_stmt**, const
char**))GetProcAddress(DLLHandle,"sqlite3_prepare_v2");

sqlite3_column_count = (int (__cdecl
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_column_count");

sqlite3_step = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_step");

sqlite3_column_text = (const unsigned char (__cdecl *)(sqlite3_stmt*, 
int
iCol))GetProcAddress(DLLHandle,"sqlite3_column_text");

sqlite3_finalize = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_finalize");

database = NULL;
open(filename);
}

Database::~Database()
{
}

bool Database::open(char* filename)
{
if(sqlite3_open(filename, ) == SQLITE_OK)
{
Form1->Memo1->Lines->Add("You have access to base.");
return true;
}

return false;   
}

vector Database::query(char* query)
{
sqlite3_stmt *statement;
vector results;

if(sqlite3_prepare_v2(database, query, -1, , 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{

values.push_back((char*)sqlite3_column_text(statement, col));  
// HERE IS ERROR !
}
results.push_back(values);
}
else
{
break;   
}
}

sqlite3_finalize(statement);
}
  
return results;
}

void Database::close()
{
sqlite3_close(database);
}

//---
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{

}
//---

void __fastcall TForm1::Button3Click(TObject *Sender)
{
Database *db;
db = new Database("Database.sqlite");
db->query("CREATE TABLE a (a INTEGER, b INTEGER);");
db->query("INSERT INTO a VALUES(1, 2);");
db->query("INSERT INTO a VALUES(5, 4);");
vector result = db->query("SELECT a, b FROM a;");
for(vector::iterator it = result.begin(); it < result.end(); 
++it)
{
vector row = *it;
cout << "Values: (A=" << row.at(0) << ", B=" << row.at(1) << ")" << 
endl;
}
db->close();
}
//---




and file Unit1.h




//---
#ifndef Unit1H
#define Unit1H
//---
#include 
#include 
#include 
#include 
#include "sqlite3.h"
#include 
#include 
//---
class TForm1 : public TForm
{
__published:// IDE-managed Components
TMemo *Memo1;
TButton *Button3;
void __fastcall Button3Click(TObject *Sender);
private:// User declarations
public:// User declarations
__fastcall TForm1(TComponent* Owner);
};
//---
extern PACKAGE TForm1 *Form1;
//---

class Database
{
public:
Database(char* filename);
~Database();

bool open(char* filename);
vector query(char* query);
void close();
sqlite3 *database;

private:
int (*sqlite3_open)(const char *, sqlite3**);
int (*sqlite3_prepare_v2)(sqlite3*, const char*, int, sqlite3_stmt**, 
const 

[sqlite] Problem with SELECT in BCB

[Puma01]

I have a problem with SQLite in BCB 4.0. I wrote my own application -
I found very useful information on 
http://www.dreamincode.net/forums/index.php?showtopic=122300

On form (Form1) I have: Memo1 and Button3.

File Unit1.cpp:


//---
#include 
#pragma hdrstop
#include 
#include "Unit1.h"
//---
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;

// DLL handle
HANDLE DLLHandle = LoadLibrary("sqlite3.dll");

// SQLite class
Database::Database(char* filename)
{
sqlite3_open = (int (__cdecl *)(const char *, 
sqlite3**))GetProcAddress(DLLHandle,"sqlite3_open");

sqlite3_close = (int (__cdecl 
*)(sqlite3*))GetProcAddress(DLLHandle,"sqlite3_close");

sqlite3_prepare_v2 = (int (__cdecl *)(sqlite3*, const char*, int, 
sqlite3_stmt**, const char**))GetProcAddress(DLLHandle,"sqlite3_prepare_v2");

sqlite3_column_count = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_column_count");

sqlite3_step = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_step");

sqlite3_column_text = (const unsigned char (__cdecl *)(sqlite3_stmt*, 
int iCol))GetProcAddress(DLLHandle,"sqlite3_column_text");

sqlite3_finalize = (int (__cdecl 
*)(sqlite3_stmt*))GetProcAddress(DLLHandle,"sqlite3_finalize");

database = NULL;
open(filename);
}

Database::~Database()
{
}

bool Database::open(char* filename)
{
if(sqlite3_open(filename, ) == SQLITE_OK)
{
Form1->Memo1->Lines->Add("You have access to base.");
return true;
}

return false;   
}

vector Database::query(char* query)
{
sqlite3_stmt *statement;
vector results;

if(sqlite3_prepare_v2(database, query, -1, , 0) == SQLITE_OK)
{
int cols = sqlite3_column_count(statement);
int result = 0;
while(true)
{
result = sqlite3_step(statement);

if(result == SQLITE_ROW)
{
vector values;
for(int col = 0; col < cols; col++)
{

values.push_back((char*)sqlite3_column_text(statement, col));   // HERE IS 
ERROR !
}
results.push_back(values);
}
else
{
break;   
}
}

sqlite3_finalize(statement);
}
  
return results;
}

void Database::close()
{
sqlite3_close(database);
}

//---
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{

}
//---

void __fastcall TForm1::Button3Click(TObject *Sender)
{
Database *db;
db = new Database("Database.sqlite");
db->query("CREATE TABLE a (a INTEGER, b INTEGER);");
db->query("INSERT INTO a VALUES(1, 2);");
db->query("INSERT INTO a VALUES(5, 4);");
vector result = db->query("SELECT a, b FROM a;");
for(vector::iterator it = result.begin(); it < result.end(); 
++it)
{
vector row = *it;
cout << "Values: (A=" << row.at(0) << ", B=" << row.at(1) << ")" << 
endl;
}
db->close();
}
//---




and file Unit1.h




//---
#ifndef Unit1H
#define Unit1H
//---
#include 
#include 
#include 
#include 
#include "sqlite3.h"
#include 
#include 
//---
class TForm1 : public TForm
{
__published:// IDE-managed Components
TMemo *Memo1;
TButton *Button3;
void __fastcall Button3Click(TObject *Sender);
private:// User declarations
public:// User declarations
__fastcall TForm1(TComponent* Owner);
};
//---
extern PACKAGE TForm1 *Form1;
//---

class Database
{
public:
Database(char* filename);
~Database();

bool open(char* filename);
vector query(char* query);
void close();
sqlite3 *database;

private:
int (*sqlite3_open)(const char *, sqlite3**);
int (*sqlite3_prepare_v2)(sqlite3*, const char*, int, sqlite3_stmt**, 
const 

Re: [sqlite] Password protection?

[Simon Slavin]

On 6 Mar 2010, at 3:04pm, Joe wrote:

> Is there a pre-compiled SEE release available? One that matches the current 
> public release? As far as I know we have no need to customize SQLite, and 
> even if we did I am not sure we would even know how ... it isn't what we do.

I think that if you paid the licensee fee this would be made available to you, 
or easy instructions can be prepared for you if you're using a rare platform.  
But I don't know for sure, and you should contact the company using the email 
address at the bottom of the page I quoted.

I can tell you that the extra protection facilities are very easy to use and 
add almost no work to what you'd need to do to work with unencrypted databases. 
 But they do encrypt the entire database file, so if you need to have people 
access one table but not another, they may not suit what you want.  email the 
address and discuss it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Password protection?

[Joe]

Is there a pre-compiled SEE release available? One that matches the current 
public release? As far as I know we have no need to customize SQLite, and 
even if we did I am not sure we would even know how ... it isn't what we do.

Joe Weinpert
PGAS, Inc.


- Original Message - 
From: "Simon Slavin" 
To: ; "General Discussion of SQLite Database" 

Sent: Saturday, March 06, 2010 6:09 AM
Subject: Re: [sqlite] Password protection?


>
> On 6 Mar 2010, at 4:12am, Neville Franks wrote:
>
>> Saturday, March 6, 2010, 2:57:59 PM, you wrote:
>>
>> J> Does SQLite allow for password protecting a database? How about
>> J> tables, can they be individually password protected?
>>
>> No, you would need to do this in your application. A password alone
>> would be very weak as anyone can look at an SQLIte database with a
>> host of browser apps or the SQLite command line tool.
>>
>> There is an extension you can purchase that does database encryption
>> if you want real protection.
>
> The encryption extension is written and maintained by the same person 
> (people ?) who bosses SQLite, so you can be sure of no finger-pointing if 
> something doesn't work the way you expected.  For further details see
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Password protection?

[Simon Slavin]

On 6 Mar 2010, at 4:12am, Neville Franks wrote:

> Saturday, March 6, 2010, 2:57:59 PM, you wrote:
> 
> J> Does SQLite allow for password protecting a database? How about
> J> tables, can they be individually password protected?
> 
> No, you would need to do this in your application. A password alone
> would be very weak as anyone can look at an SQLIte database with a
> host of browser apps or the SQLite command line tool.
> 
> There is an extension you can purchase that does database encryption
> if you want real protection.

The encryption extension is written and maintained by the same person (people 
?) who bosses SQLite, so you can be sure of no finger-pointing if something 
doesn't work the way you expected.  For further details see



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users