Re: [sqlite] Question on Blobs

[Neville Franks]

Hi Mike,
The data in this example happens to come from file, but that isn't
relevant. The line:

   rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);

is binding a chunk of data on the heap to the blob column and
inserting that into the database. Where this chunk of data comes from
isn't relevant. HTH.


Thursday, February 28, 2008, 2:35:27 PM, you wrote:

MM> Wow, Peter, didn't expect that anyone would go to the trouble of writing a
MM> program on the spot
MM> Just curious, but from those few things that I have seen, it appears that
MM> you can only put a Blob into the DB if it is already on disc, right? All
MM> three examples I have seen passed the filename to the database, and one of
MM> them was working within a server context, so I wasn't sure how the local
MM> filename would be of any use to a machine that is in another part of the
MM> room (or anywhere else...).

MM> Just so you understand what it is I am trying to do, I am working in a
MM> Multimedia programming environment (Pure Data), and I would like to be able
MM> to read and write some chunks of audio or video as needed. While Pure Data
MM> is a realtime environment, I am not expecting this to be responsive to work
MM> in realtime.

MM> Thanks again, I will study this to see if it tells me anything more...

MM> Mike


MM> On Wed, Feb 27, 2008 at 8:02 PM, Peter A. Friend <[EMAIL PROTECTED]>
MM> wrote:

>>
>> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
>>
>> > Hello all,
>> > I was hoping that someone might share some tips on working with
>> > Blobs? I
>> > would like to be able to store some images and sound files in a
>> > database,
>> > but never having dealt with them, I am kind of at a loss for some
>> > examples.
>> > I have looked on the web, and there are few examples that were of use.
>>
>> Well, I wrote a quick and dirty program for stuffing image files into
>> a database. You just provide a directory and it stats() each file,
>> allocates enough space for the image data, then loads it from disk.
>> Sql statement is something like:
>>
>> char* sql = "insert into i (name, data) values (?, ?);";
>>
>> Of course if your images are huge this method coud be problematic. I
>> believe SQLite supports an incremental way to do this but I haven't
>> looked at those calls yet.
>>
>>while ( (dentry = readdir(dir)) != '\0') {
>>   if (dentry->d_name[0] == '.')
>>  continue;
>>
>>   if (fd != -1) {
>>  close(fd);
>>  fd = -1;
>>   }
>>
>>   if (data != '\0') {
>>  free(data);
>>  data = '\0';
>>   }
>>
>>   snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
>>   stat(fname, );
>>
>>   if ( (data = malloc(sb.st_size)) == '\0') {
>>  fprintf(stderr, "malloc() failed\n");
>>  sqlite3_finalize(stmt);
>>  sqlite3_close(db);
>>  exit(1);
>>   }
>>
>>   if ( (fd = open(fname, O_RDONLY, )) == -1) {
>>  fprintf(stderr, "open() failed\n");
>>  sqlite3_finalize(stmt);
>>  sqlite3_close(db);
>>  exit(1);
>>   }
>>
>>   if ( (retval = read(fd, data, sb.st_size)) == -1) {
>>  fprintf(stderr, "read() failed\n");
>>  sqlite3_finalize(stmt);
>>  sqlite3_close(db);
>>  exit(1);
>>   }
>>
>>   if (retval != sb.st_size) {
>>  fprintf(stderr, "read() failed\n");
>>  sqlite3_finalize(stmt);
>>  sqlite3_close(db);
>>  exit(1);
>>   }
>>
>>   rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
>>  SQLITE_STATIC);
>>
>>   if (rc != SQLITE_OK) {
>>  fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
>> (db));
>>  sqlite3_finalize(stmt);
>>  sqlite3_close(db);
>>  exit(1);
>>   }
>>
>>   rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
>>
>>   if (rc != SQLITE_OK) {
>>  fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
>> (db));
>>  sqlite3_finalize(stmt);
>>  sqlite3_close(db);
>>  exit(1);
>>   }
>>
>>   rc = sqlite3_step(stmt);
>>
>>   if (rc != SQLITE_DONE) {
>>  fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
>>  sqlite3_finalize(stmt);
>>  sqlite3_close(db);
>>  exit(1);
>>   }
>>
>>   sqlite3_reset(stmt);
>>}
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>





-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

Re: [sqlite] Question on Blobs

[Fred J. Stephens]

Mike McGonagle wrote:
> Hello all,
> I was hoping that someone might share some tips on working with Blobs? 
I am also curious about this.
For instance, how can I store a file in a table?
Not read the file and store the text, but the binary file itself?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Like operator

[Igor Tandetnik]

"Mahalakshmi.m"
<[EMAIL PROTECTED]> wrote
in message
news:[EMAIL PROTECTED]
> Rowid Id Album
> 1 4 
> 2 3 
> 3 2 
> 4 1 
>
> SELECT rowid,Album FROM Temp WHERE Album like 'c%';
> Output: rowed -> 2 and Album -> 
>
> My doubt is for this statement
> "SELECT rowid , Album FROM Temp WHERE Album like 'b%'; " I am not
> having any Album with match pattern starting with b so I want the
> rowed and Album for the string next to the provided ie, rowed ->3
> and Album -> .

select rowid, album from temp
where album >= 'b' collate nocase
order by album limit 1;

That should work both when there is and isn't a match.

Igor Tandetnik 



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

[sqlite] Like operator

[Mahalakshmi.m]

Hi,
I am working in 3.3.6
My table is as follows:

"create table MUSIC(id integer primary key,Album text,Artist text);"
IdAlbum Artist
1   z
2   w
3   s
4   t

I want to sort the Album First and then I have to tell the rowid of the
particular string pattern.If that pattern is not present it has to provide
the next string match.

I tried as follows:
"CREATE TABLE IF NOT EXISTS Temp as SELECT Album from MUSIC ORDER BY Album;

Rowid   Id  Album   
1   4   
2   3   
3   2   
4   1   

SELECT rowid,Album FROM Temp WHERE Album like 'c%'; 
Output: rowed -> 2 and Album -> 

My doubt is for this statement
"SELECT rowid , Album FROM Temp WHERE Album like 'b%'; " I am not having any
Album with match pattern starting with b so I want the rowed and Album for
the string next to the provided ie, rowed ->3  and Album -> .

Can any one please help to solve my problem.

Thanks & Regards,
Mahalakshmi.M




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

Re: [sqlite] Question on Blobs

[Mike McGonagle]

Wow, Peter, didn't expect that anyone would go to the trouble of writing a
program on the spot
Just curious, but from those few things that I have seen, it appears that
you can only put a Blob into the DB if it is already on disc, right? All
three examples I have seen passed the filename to the database, and one of
them was working within a server context, so I wasn't sure how the local
filename would be of any use to a machine that is in another part of the
room (or anywhere else...).

Just so you understand what it is I am trying to do, I am working in a
Multimedia programming environment (Pure Data), and I would like to be able
to read and write some chunks of audio or video as needed. While Pure Data
is a realtime environment, I am not expecting this to be responsive to work
in realtime.

Thanks again, I will study this to see if it tells me anything more...

Mike


On Wed, Feb 27, 2008 at 8:02 PM, Peter A. Friend <[EMAIL PROTECTED]>
wrote:

>
> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
>
> > Hello all,
> > I was hoping that someone might share some tips on working with
> > Blobs? I
> > would like to be able to store some images and sound files in a
> > database,
> > but never having dealt with them, I am kind of at a loss for some
> > examples.
> > I have looked on the web, and there are few examples that were of use.
>
> Well, I wrote a quick and dirty program for stuffing image files into
> a database. You just provide a directory and it stats() each file,
> allocates enough space for the image data, then loads it from disk.
> Sql statement is something like:
>
> char* sql = "insert into i (name, data) values (?, ?);";
>
> Of course if your images are huge this method coud be problematic. I
> believe SQLite supports an incremental way to do this but I haven't
> looked at those calls yet.
>
>while ( (dentry = readdir(dir)) != '\0') {
>   if (dentry->d_name[0] == '.')
>  continue;
>
>   if (fd != -1) {
>  close(fd);
>  fd = -1;
>   }
>
>   if (data != '\0') {
>  free(data);
>  data = '\0';
>   }
>
>   snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
>   stat(fname, );
>
>   if ( (data = malloc(sb.st_size)) == '\0') {
>  fprintf(stderr, "malloc() failed\n");
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  exit(1);
>   }
>
>   if ( (fd = open(fname, O_RDONLY, )) == -1) {
>  fprintf(stderr, "open() failed\n");
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  exit(1);
>   }
>
>   if ( (retval = read(fd, data, sb.st_size)) == -1) {
>  fprintf(stderr, "read() failed\n");
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  exit(1);
>   }
>
>   if (retval != sb.st_size) {
>  fprintf(stderr, "read() failed\n");
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  exit(1);
>   }
>
>   rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
>  SQLITE_STATIC);
>
>   if (rc != SQLITE_OK) {
>  fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
> (db));
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  exit(1);
>   }
>
>   rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
>
>   if (rc != SQLITE_OK) {
>  fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
> (db));
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  exit(1);
>   }
>
>   rc = sqlite3_step(stmt);
>
>   if (rc != SQLITE_DONE) {
>  fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
>  sqlite3_finalize(stmt);
>  sqlite3_close(db);
>  exit(1);
>   }
>
>   sqlite3_reset(stmt);
>}
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Peace may sound simple—one beautiful word— but it requires everything we
have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question on Blobs

[Igor Tandetnik]

"Mike McGonagle" <[EMAIL PROTECTED]> wrote
in message
news:[EMAIL PROTECTED]
> My impression from what I have seen is that Blobs are dealt with in a
> different way than other data types. Am I correct in assuming that
> Blobs need to be created explicitly, and then you repeatedly call
> 'sqlite3_blob_write()' to store them, and 'sqlite3_blob_read()' to
> read them?

That's a fairly recent API. The old way, that still happily works, is to 
use prepared parameterized statements (see sqlite3_prepare et al) 
together with sqlite3_bind_blob. You bind a BLOB just as you would any 
other parameter.

> Also, can you have a select statement that retrieves regular data as
> well as blob data in the same query?

Yes. To extract BLOB field, use sqlite3_column_blob.

Igor Tandetnik 



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

Re: [sqlite] Question on Blobs

[Peter A. Friend]

On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:

> Hello all,
> I was hoping that someone might share some tips on working with  
> Blobs? I
> would like to be able to store some images and sound files in a  
> database,
> but never having dealt with them, I am kind of at a loss for some  
> examples.
> I have looked on the web, and there are few examples that were of use.

Well, I wrote a quick and dirty program for stuffing image files into  
a database. You just provide a directory and it stats() each file,  
allocates enough space for the image data, then loads it from disk.  
Sql statement is something like:

char* sql = "insert into i (name, data) values (?, ?);";

Of course if your images are huge this method coud be problematic. I  
believe SQLite supports an incremental way to do this but I haven't  
looked at those calls yet.

while ( (dentry = readdir(dir)) != '\0') {
   if (dentry->d_name[0] == '.')
  continue;

   if (fd != -1) {
  close(fd);
  fd = -1;
   }

   if (data != '\0') {
  free(data);
  data = '\0';
   }

   snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
   stat(fname, );

   if ( (data = malloc(sb.st_size)) == '\0') {
  fprintf(stderr, "malloc() failed\n");
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  exit(1);
   }

   if ( (fd = open(fname, O_RDONLY, )) == -1) {
  fprintf(stderr, "open() failed\n");
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  exit(1);
   }

   if ( (retval = read(fd, data, sb.st_size)) == -1) {
  fprintf(stderr, "read() failed\n");
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  exit(1);
   }

   if (retval != sb.st_size) {
  fprintf(stderr, "read() failed\n");
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  exit(1);
   }

   rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
  SQLITE_STATIC);

   if (rc != SQLITE_OK) {
  fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg 
(db));
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  exit(1);
   }

   rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);

   if (rc != SQLITE_OK) {
  fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg 
(db));
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  exit(1);
   }

   rc = sqlite3_step(stmt);

   if (rc != SQLITE_DONE) {
  fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  exit(1);
   }

   sqlite3_reset(stmt);
}

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

Re: [sqlite] IF...THEN constructs

[Jason Salas]

nice work!



Stephen Oberholtzer wrote:
> On Wed, Feb 27, 2008 at 6:00 PM, Jason Salas <[EMAIL PROTECTED]> wrote:
>   
>> Hi Igor,
>>
>>  Thanks for the insight.  I'm used to doing stored procedures for web
>>  apps, which conditionally execute statements based on state and/or the
>>  presence of variables.  Consider this construct, which I built recently
>>  to populate a table with URL for a web spider bot I built:
>>
>>  CREATE PROCEDURE AddLinkInfo
>>  (
>> @ProviderName VARCHAR(200),
>> @LinkPath VARCHAR(200),
>> @LinkText VARCHAR(200)
>>  )
>>  AS
>> DECLARE @ProviderIDINT
>>
>> -- only store a link if it isn't already listed in the database
>> IF NOT EXISTS(SELECT LinkPath FROM SpiderBot WHERE LinkPath = @LinkPath)
>> BEGIN
>> -- is this a known provider?  if not, add it into the DB and
>>  then assign it's new ID
>> IF EXISTS(SELECT ContentProviderID FROM
>>  SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
>> BEGIN
>> SET @ProviderID= (SELECT ContentProviderID FROM
>>  SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
>> END
>> ELSE
>> BEGIN
>> INSERT INTO SpiderBot_ContentProviders VALUES
>>  (@ProviderName)
>> SET @ProviderID = @@IDENTITY
>> END
>>
>>   -- do the main content insertion
>> INSERT INTO SpiderBot (ContentProviderID,LinkPath,LinkText)
>>  VALUES (@ProviderID,@LinkPath,@LinkText)
>> END
>>  GO
>>
>>  How would I got about re-writing something like this in SQLite?  Thanks
>>  again for your help.
>>
>>
>> 
>
> I would create several functions:
>
> function RegisteProvider(providername)
> check with a SELECT statement to see if the provider exists
>if it does, return the provider ID
>
>insert a new provider
>return new provider's ID
>
> function RecordLinkInfo(providername, linkname, linkurl)
>check with a SELECT statement to see if linkurl is inuse
>if it is, bail
>providerId = RegisterProvider(providername)
>insert new row with provider ID, link name, link url
>
>
>
> Remember to use transactions to significantly boost your insert
> performance  Maybe one transaction per page?
>
>   


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

Re: [sqlite] IF...THEN constructs

[Jason Salas]

shoot.  worst suspicions affirmed.  :-)

although this is for a C# console app, it's still largely client/server 
and i designed the back-end as such, to reduce roundtrips to the DB.  no 
sweat, a little refactoring won't hurt.  thanks again!


Igor Tandetnik wrote:
> Jason Salas <[EMAIL PROTECTED]> wrote:
>   
>> Thanks for the insight.  I'm used to doing stored procedures for web
>> apps
>> 
>
> There is no such thing as a stored procedure in SQLite.
>
>   
>> How would I got about re-writing something like this in SQLite?
>> 
>
> You wouldn't. You would write the logic in whatever application you are 
> developing that interacts with SQLite database.
>
> Igor Tandetnik 
>
>
>
> ___
> 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] undefined symbol: sqlite3_prepare_v2

[Derrell Lipman]

On Wed, Feb 27, 2008 at 6:23 PM, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi all,
>  Thx for the response!
>  On different Linux server. So how to find out what is the SQLite version on 
> the server which has the problem.
>  How to find out the sqlite version using Linux commnad.

Doing it with a Linux command is not entirely reliable, since the
Linux command could be staticly linked with a different sqlite library
than the shared one your application is finding.  You can try it,
though:

% sqlite3 :memory:
sqlite> select sqlite_version();

or even:

% echo "select sqlite_version();" | sqlite3 :memory:

More reliable than using the command line tool (sqlite3) is to have
your application issue a "SELECT sqlite_version();" and see what
version is returned.

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

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Joanne Pham]

- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, February 27, 2008 3:23:09 PM
Subject: Re: [sqlite] undefined symbol: sqlite3_prepare_v2


Hi all,
Thx for the response!
On different Linux server. So how to find out what is the SQLite version on the 
server which has the problem.
How to find out the sqlite version using Linux commnad.
Thanks,
JP



- Original Message 
From: Dimitri <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, February 27, 2008 1:54:22 PM
Subject: Re: [sqlite] undefined symbol: sqlite3_prepare_v2

Hi,

> I have been using 3.5.2 sqlite. I forgot the mention this in my previous 
> email.
> If you know the answer to this question please help.

sqlite3_prepare_v2 is available in SQLite 3.5.2. There must be some older 
version of SQLite somewhere on the single server where your code doesn't work.

How is this single server different from the other servers? Different Linux 
distribution? Different version of SQLite?

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






Looking for last minute shopping deals? Find them fast with Yahoo! Search.


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IF...THEN constructs

[Stephen Oberholtzer]

On Wed, Feb 27, 2008 at 6:00 PM, Jason Salas <[EMAIL PROTECTED]> wrote:
> Hi Igor,
>
>  Thanks for the insight.  I'm used to doing stored procedures for web
>  apps, which conditionally execute statements based on state and/or the
>  presence of variables.  Consider this construct, which I built recently
>  to populate a table with URL for a web spider bot I built:
>
>  CREATE PROCEDURE AddLinkInfo
>  (
> @ProviderName VARCHAR(200),
> @LinkPath VARCHAR(200),
> @LinkText VARCHAR(200)
>  )
>  AS
> DECLARE @ProviderIDINT
>
> -- only store a link if it isn't already listed in the database
> IF NOT EXISTS(SELECT LinkPath FROM SpiderBot WHERE LinkPath = @LinkPath)
> BEGIN
> -- is this a known provider?  if not, add it into the DB and
>  then assign it's new ID
> IF EXISTS(SELECT ContentProviderID FROM
>  SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
> BEGIN
> SET @ProviderID= (SELECT ContentProviderID FROM
>  SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
> END
> ELSE
> BEGIN
> INSERT INTO SpiderBot_ContentProviders VALUES
>  (@ProviderName)
> SET @ProviderID = @@IDENTITY
> END
>
>   -- do the main content insertion
> INSERT INTO SpiderBot (ContentProviderID,LinkPath,LinkText)
>  VALUES (@ProviderID,@LinkPath,@LinkText)
> END
>  GO
>
>  How would I got about re-writing something like this in SQLite?  Thanks
>  again for your help.
>
>

I would create several functions:

function RegisteProvider(providername)
check with a SELECT statement to see if the provider exists
   if it does, return the provider ID

   insert a new provider
   return new provider's ID

function RecordLinkInfo(providername, linkname, linkurl)
   check with a SELECT statement to see if linkurl is inuse
   if it is, bail
   providerId = RegisterProvider(providername)
   insert new row with provider ID, link name, link url



Remember to use transactions to significantly boost your insert
performance  Maybe one transaction per page?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IF...THEN constructs

[Igor Tandetnik]

Jason Salas <[EMAIL PROTECTED]> wrote:
> Thanks for the insight.  I'm used to doing stored procedures for web
> apps

There is no such thing as a stored procedure in SQLite.

> How would I got about re-writing something like this in SQLite?

You wouldn't. You would write the logic in whatever application you are 
developing that interacts with SQLite database.

Igor Tandetnik 



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

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Joanne Pham]

Hi all,
Thx for the response!
On different Linux server. So how to find out what is the SQLite version on the 
server which has the problem.
How to find out the sqlite version using Linux commnad.
Thanks,
JP



- Original Message 
From: Dimitri <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, February 27, 2008 1:54:22 PM
Subject: Re: [sqlite] undefined symbol: sqlite3_prepare_v2

Hi,

> I have been using 3.5.2 sqlite. I forgot the mention this in my previous 
> email.
> If you know the answer to this question please help.

sqlite3_prepare_v2 is available in SQLite 3.5.2. There must be some older 
version of SQLite somewhere on the single server where your code doesn't work.

How is this single server different from the other servers? Different Linux 
distribution? Different version of SQLite?

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


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Two different DLLs linked statically to Sqlite and loaded in the same process

[Kalipto]

Hello,

For some particular reasons, one process loads two different DLLs, each 
one linked with a static library of Sqlite. This two DLLs access the 
same database. I know that there is no problem if you access to the 
database from 2 different processes. But I was wondering if there would 
be a problem in this situation. Maybe the two libraries would improperly 
share some objects like mutex in the same process environment ?

Another question is, does it makes a problem that the 2 versions of the 
static Sqlite libraries are different (not the same release number) ?

I ask all these questions because sometimes (even if it is rare) the 
database gets corrupted. So I was wondering if it could be an 
explanation of this problem or if I must look into another direction 
(maybe the program would badly write in the database structure and 
corrupt it).

Last question: if I open a database in read-only mode, does it guarantee 
that the database will not be corrupted.

Thanks for your answers.

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

Re: [sqlite] IF...THEN constructs

[Jason Salas]

Hi Igor,

Thanks for the insight.  I'm used to doing stored procedures for web 
apps, which conditionally execute statements based on state and/or the 
presence of variables.  Consider this construct, which I built recently 
to populate a table with URL for a web spider bot I built:

CREATE PROCEDURE AddLinkInfo
(
@ProviderName VARCHAR(200),
@LinkPath VARCHAR(200),
@LinkText VARCHAR(200)
)
AS
DECLARE @ProviderIDINT

-- only store a link if it isn't already listed in the database
IF NOT EXISTS(SELECT LinkPath FROM SpiderBot WHERE LinkPath = @LinkPath)
BEGIN
-- is this a known provider?  if not, add it into the DB and 
then assign it's new ID
IF EXISTS(SELECT ContentProviderID FROM 
SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)
BEGIN
SET @ProviderID= (SELECT ContentProviderID FROM 
SpiderBot_ContentProviders WHERE ProviderName = @ProviderName)   
END
ELSE
BEGIN
INSERT INTO SpiderBot_ContentProviders VALUES 
(@ProviderName)
SET @ProviderID = @@IDENTITY
END
   
  -- do the main content insertion
INSERT INTO SpiderBot (ContentProviderID,LinkPath,LinkText) 
VALUES (@ProviderID,@LinkPath,@LinkText)   
END
GO

How would I got about re-writing something like this in SQLite?  Thanks 
again for your help.


Igor Tandetnik wrote:
> "Jason Salas" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>   
>> I'm used to doing lengthy T-SQL programming in SQL Server, so this is
>> kinda new to me.  How does one replicate doing IF...THEN conditional
>> blocks in SQLite 3?
>> 
>
> One typically doesn't. Instead, one implements complex logic in one's 
> application that hosts SQLite.
>
> Igor Tandetnik 
>
>
>
> ___
> 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

[sqlite] Keeping ROWID for INSERT OR REPLACE

[Neville Franks]

I want to insert a row if its key clm doesn't exist otherwise update
it. I can search for the row and then do either an insert or update
accordingly. However I was wondering whether the SQLite Conflict
Resolution: INSERT OR REPLACE would be more efficient (faster). The
problem is the REPLACE deletes the existing row and INSERT adds a new
one, loosing the ROWID value of the original row, which I need to
keep.

So my question is should I just forget this and do it the: select ->
if not found insert otherwise update way or is there a way to maintain
the original rowid using INSERT OR REPLACE?

If not what is the fastest way to check if a row exists, assuming the
search is on a single clm which is indexed. ex.
select myclm from mytable where myclm='abc';
select count(*) from mytable where myclm='abc';
add limit 1 to either of the above etc.

-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Dimitri]

Hi,

> I have been using 3.5.2 sqlite. I forgot the mention this in my previous 
> email.
> If you know the answer to this question please help.

sqlite3_prepare_v2 is available in SQLite 3.5.2. There must be some older 
version of SQLite somewhere on the single server where your code doesn't work.

How is this single server different from the other servers? Different Linux 
distribution? Different version of SQLite?

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

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Joanne Pham]

I have been using 3.5.2 sqlite. I forgot the mention this in my previous email.
If you know the answer to this question please help.
Thanks,
JP


- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 27, 2008 9:34:05 AM
Subject: [sqlite] undefined symbol: sqlite3_prepare_v2

Hi All,
I used the  sqlite3_prepare_v2 in my code and some of the servers are ok but 
one of my server return an error message that "undefined symbol: 
sqlite3_prepare_v2 " when the process is started and access the database using 
sqlite3_prepare_v2 . I don't have any clue why this problem is only happened on 
one single server but no others. Please help if you have an answer
Thanks,
JP


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Derrell Lipman]

On Wed, Feb 27, 2008 at 12:34 PM, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All,
>  I used the   sqlite3_prepare_v2 in my code and some of the servers are ok 
> but one of my server return an error message that "undefined symbol: 
> sqlite3_prepare_v2 " when the process is started and access the database 
> using sqlite3_prepare_v2 . I don't have any clue why this problem is only 
> happened on one single server but no others. Please help if you have an answer

It sounds like you're linking for shared library (or DLL) and the
version of sqlite3 on that one system is very old: it predates the
sqlite3_prepare_v2 function being added to sqlite.  I believe there's
an sqlite3_version() function you can call to find out what version of
the library your application is finding.

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

Re: [sqlite] undefined symbol: sqlite3_prepare_v2

[Igor Tandetnik]

Joanne Pham <[EMAIL PROTECTED]> wrote:
> I used the   sqlite3_prepare_v2 in my code and some of the servers
> are ok but one of my server return an error message that "undefined
> symbol: sqlite3_prepare_v2 " when the process is started and access
> the database using sqlite3_prepare_v2 . I don't have any clue why
> this problem is only happened on one single server but no others.

That server has an older version of SQLite installed - one that doesn't 
yet provide sqlite3_prepare_v2 API.

Igor Tandetnik 



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

[sqlite] undefined symbol: sqlite3_prepare_v2

[Joanne Pham]

Hi All,
I used the   sqlite3_prepare_v2 in my code and some of the servers are ok but 
one of my server return an error message that "undefined symbol: 
sqlite3_prepare_v2 " when the process is started and access the database using 
sqlite3_prepare_v2 . I don't have any clue why this problem is only happened on 
one single server but no others. Please help if you have an answer
Thanks,
JP


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating values from multiple varchar fields

[Samuel Neff]

While '||' is the standard, unfortunately MSSQL uses '+' for string
concatentation so people that are used to using '+' are most likely coming
from MSSQL and thus using '||' does not make for portable SQL (MSSQL does
not support '||', at least not in version 2005).

In my applications I have a custom Concat() function to do string
concatenation and then write an implementation of this in both SQLite and
MSSQL so the same SQL can be run on both databases.

Sam


On Wed, Feb 27, 2008 at 2:48 AM, John Stanton <[EMAIL PROTECTED]> wrote:

>
> This is plain vanilla, standard SQL according to the standard.  You will
> find that Sqlite sticks to the standard quite well so if you write ANSI
> SQL it is not only understood by Sqlite but be portable.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IF...THEN constructs

[Igor Tandetnik]

"Jason Salas" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I'm used to doing lengthy T-SQL programming in SQL Server, so this is
> kinda new to me.  How does one replicate doing IF...THEN conditional
> blocks in SQLite 3?

One typically doesn't. Instead, one implements complex logic in one's 
application that hosts SQLite.

Igor Tandetnik 



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

Re: [sqlite] FW: Query to Find number of distinct records

[Bharath Booshan L]

> support for count(distinct) has been added in 3.2.6
> your version is certainly < 3.2.6 (sqlite -version)
Your are right. My version is 3.1.3


Thanks a lot for all your inputs folks :-)

--
Bharath

On 2/27/08 4:31 PM, "Cyril SCETBON" <[EMAIL PROTECTED]> wrote:

> support for count(distinct) has been added in 3.2.6
> your version is certainly < 3.2.6 (sqlite -version)
> 
> Bharath Booshan L wrote:
>> -- Forwarded Message
>> From: Bharath Booshan L <[EMAIL PROTECTED]>
>> Date: Wed, 27 Feb 2008 14:32:14 +0530
>> To: Eugene Wee <[EMAIL PROTECTED]>
>> Conversation: [sqlite] Query to Find number of distinct records
>> Subject: Re: [sqlite] Query to Find number of distinct records
>> 
>> I was off for lunch
>> 
>>> The thing is, it should work. What is the error message?
>> 
>> sqlite> select count(DISTINCT Name) as nameCount from TableA;
>> SQL error: near "DISTINCT": syntax error
>> 
>> 
>> On 2/27/08 1:24 PM, "Eugene Wee" <[EMAIL PROTECTED]> wrote:
>> 
>>> Hi,
>>> 
>>> Bharath Booshan L wrote:
 Yeah!! I got it right this time.
 
 Select count(*) from ( select DISTINCT Name from TableA);
 
 
 But what's not getting into my mind is the difference b/w the following two
 queries:
 
 Select count( Name) from TableA  -- works fine
 
 Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
>>> The thing is, it should work. What is the error message?
>>> 
>>> Regards,
>>> Eugene Wee
>>> 
>> 
>> -- End of Forwarded Message
>> 
>> 
>> 
>> ---
>> Robosoft Technologies - Come home to Technology
>> 
>> Disclaimer: This email may contain confidential material. If you were not an
>> intended recipient, please notify the sender and delete all copies. Emails to
>> and from our network may be logged and monitored. This email and its
>> attachments are scanned for virus by our scanners and are believed to be
>> safe. However, no warranty is given that this email is free of malicious
>> content or virus.
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

Re: [sqlite] FW: Query to Find number of distinct records

[Cyril SCETBON]

support for count(distinct) has been added in 3.2.6
your version is certainly < 3.2.6 (sqlite -version)

Bharath Booshan L wrote:
> -- Forwarded Message
> From: Bharath Booshan L <[EMAIL PROTECTED]>
> Date: Wed, 27 Feb 2008 14:32:14 +0530
> To: Eugene Wee <[EMAIL PROTECTED]>
> Conversation: [sqlite] Query to Find number of distinct records
> Subject: Re: [sqlite] Query to Find number of distinct records
> 
> I was off for lunch
> 
>> The thing is, it should work. What is the error message?
> 
> sqlite> select count(DISTINCT Name) as nameCount from TableA;
> SQL error: near "DISTINCT": syntax error
> 
> 
> On 2/27/08 1:24 PM, "Eugene Wee" <[EMAIL PROTECTED]> wrote:
> 
>> Hi,
>>
>> Bharath Booshan L wrote:
>>> Yeah!! I got it right this time.
>>>
>>> Select count(*) from ( select DISTINCT Name from TableA);
>>>
>>>
>>> But what's not getting into my mind is the difference b/w the following two
>>> queries:
>>>
>>> Select count( Name) from TableA  -- works fine
>>>
>>> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
>> The thing is, it should work. What is the error message?
>>
>> Regards,
>> Eugene Wee
>>
> 
> -- End of Forwarded Message
> 
> 
> 
> ---
> Robosoft Technologies - Come home to Technology
> 
> Disclaimer: This email may contain confidential material. If you were not an 
> intended recipient, please notify the sender and delete all copies. Emails to 
> and from our network may be logged and monitored. This email and its 
> attachments are scanned for virus by our scanners and are believed to be 
> safe. However, no warranty is given that this email is free of malicious 
> content or virus.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
Cyril SCETBON

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

Re: [sqlite] FW: Query to Find number of distinct records

[Simon Davies]

Hi Bharath,

Works for me in 3.4.2

SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table tst( nun integer, nm text );
sqlite> insert into tst values( 23, 'A' );
sqlite> insert into tst values( 23, 'b' );
sqlite> insert into tst values( 24, 'C' );
sqlite> insert into tst values( 25, 'A' );
sqlite> insert into tst values( 23, 'E' );
sqlite> select count( distinct nm ) from tst;
4

Rgds,
Simon


On 27/02/2008, Bharath Booshan L <[EMAIL PROTECTED]> wrote:
>
> -- Forwarded Message
> From: Bharath Booshan L <[EMAIL PROTECTED]>
> Date: Wed, 27 Feb 2008 14:32:14 +0530
> To: Eugene Wee <[EMAIL PROTECTED]>
> Conversation: [sqlite] Query to Find number of distinct records
> Subject: Re: [sqlite] Query to Find number of distinct records
>
> I was off for lunch
>
> > The thing is, it should work. What is the error message?
>
> sqlite> select count(DISTINCT Name) as nameCount from TableA;
> SQL error: near "DISTINCT": syntax error
>
>
> On 2/27/08 1:24 PM, "Eugene Wee" <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> >
> > Bharath Booshan L wrote:
> >> Yeah!! I got it right this time.
> >>
> >> Select count(*) from ( select DISTINCT Name from TableA);
> >>
> >>
> >> But what's not getting into my mind is the difference b/w the following two
> >> queries:
> >>
> >> Select count( Name) from TableA  -- works fine
> >>
> >> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
> >
> > The thing is, it should work. What is the error message?
> >
> > Regards,
> > Eugene Wee
> >
>
> -- End of Forwarded Message
>
>
>
> ---
> Robosoft Technologies - Come home to Technology
>
> Disclaimer: This email may contain confidential material. If you were not an 
> intended recipient, please notify the sender and delete all copies. Emails to 
> and from our network may be logged and monitored. This email and its 
> attachments are scanned for virus by our scanners and are believed to be 
> safe. However, no warranty is given that this email is free of malicious 
> content or virus.
>
>
> ___
> 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

[sqlite] FW: Query to Find number of distinct records

[Bharath Booshan L]

-- Forwarded Message
From: Bharath Booshan L <[EMAIL PROTECTED]>
Date: Wed, 27 Feb 2008 14:32:14 +0530
To: Eugene Wee <[EMAIL PROTECTED]>
Conversation: [sqlite] Query to Find number of distinct records
Subject: Re: [sqlite] Query to Find number of distinct records

I was off for lunch

> The thing is, it should work. What is the error message?

sqlite> select count(DISTINCT Name) as nameCount from TableA;
SQL error: near "DISTINCT": syntax error


On 2/27/08 1:24 PM, "Eugene Wee" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> Bharath Booshan L wrote:
>> Yeah!! I got it right this time.
>> 
>> Select count(*) from ( select DISTINCT Name from TableA);
>> 
>> 
>> But what's not getting into my mind is the difference b/w the following two
>> queries:
>> 
>> Select count( Name) from TableA  -- works fine
>> 
>> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
> 
> The thing is, it should work. What is the error message?
> 
> Regards,
> Eugene Wee
> 

-- End of Forwarded Message



---
Robosoft Technologies - Come home to Technology

Disclaimer: This email may contain confidential material. If you were not an 
intended recipient, please notify the sender and delete all copies. Emails to 
and from our network may be logged and monitored. This email and its 
attachments are scanned for virus by our scanners and are believed to be safe. 
However, no warranty is given that this email is free of malicious content or 
virus.


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

Re: [sqlite] Query to Find number of distinct records

[Eugene Wee]

Hi,

Bharath Booshan L wrote:
> Yeah!! I got it right this time.
> 
> Select count(*) from ( select DISTINCT Name from TableA);
> 
> 
> But what's not getting into my mind is the difference b/w the following two
> queries:
> 
> Select count( Name) from TableA  -- works fine
> 
> Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?

The thing is, it should work.

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