[sqlite] retrieving message set with sqlite3_result_error()

[Jeremy Hinegardner]

Hi all,

I'm using sqlite3_create_function() to create an sql function,  and in
the implementation of the sql function calling sqlite3_result_error()
and sqlite3_result_error_code(). 

The value set with sqlite3_result_error_code() immediately appears as
the return code from sqlite3_step() and is available as the return value
from sqlite3_errcode() as soon as sqlite3_step returns. which is good.

The problem is the value set with sqlite3_result_error() is not
retrievable using sqlite3_errmsg() until after sqlite3_finalize() or
sqlite3_reset() is called.

Here's an example : http://pastie.org/352677
download as text  : http://pastie.org/352677.txt 

Output from when I run it:

  SQLite Version: 3.6.7
  Writting error msg 'This is an error' and error_code 42 to context
  Return code from sqlite3_step  : 42
  Before finalizing error code is: 42
  Before finalizing error message is : unknown error
  Return value from sqlite3_finalize : 42
  After finalizing errcode is: 42
  After finalizing error message is  : This is an error

Am I mistaken in thinking that the error message set in the
implementation of an sql function should be available at the same time as
the error code? 

Is this a bug?

enjoy,

-jeremy

-- 

 Jeremy Hinegardner  jer...@hinegardner.org 

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

Re: [sqlite] Parenthetical LIKE query ??

[P Kishor]

On 1/4/09, Ben Marchbanks  wrote:
> SELECT * FROM `pagesText` WHERE  pageText LIKE ( "%muffler%" , "%clamp%" )
>
>  Is there a nice way to write a query using parenthetical LIKE ?

afaik, there is no option for using LIKE in the manner of IN (as you
are trying to do above). You can match for each pattern separately
using OR between each clause.

Also, use double quotes to quote the table or column names and single
quotes to delimit the pattern... so, you should use

SELECT * FROM "pagesText" WHERE pageText LIKE '%muffler%' OR pageText
LIKE '%clamp%';


>
>  Queries like this can get quite long otherwise
>
>  Any suggestions ?
>
>
>  *Ben Marchbanks*
>
>  www.magazooms.com 
>  Signature
>  Email: b...@magazooms.com 
>  Phone: (864) 284.9918
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parenthetical LIKE query ??

[Igor Tandetnik]

"Ben Marchbanks"  wrote in
message news:49615144.7090...@alqemy.com
> SELECT * FROM `pagesText` WHERE  pageText LIKE ( "%muffler%" ,
> "%clamp%" )
>
> Is there a nice way to write a query using parenthetical LIKE ?

SELECT * FROM pagesText
WHERE pageText LIKE '%muffler%'  OR pageText LIKE '%clamp%';

If you have a long list of patterns you want to compare against, insert 
them all into a temp table then do something like this:

SELECT * FROM pagesText WHERE EXISTS (
select 1 from patterns where pageText LIKE '%' || pattern || '%');

Igor Tandetnik 



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

[sqlite] Parenthetical LIKE query ??

[Ben Marchbanks]

SELECT * FROM `pagesText` WHERE  pageText LIKE ( "%muffler%" , "%clamp%" )

Is there a nice way to write a query using parenthetical LIKE ?

Queries like this can get quite long otherwise

Any suggestions ?


*Ben Marchbanks*

www.magazooms.com 
Signature
Email: b...@magazooms.com 
Phone: (864) 284.9918
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem with ".dump" -- not generating insert statements on biggish table (12K by 2.8K)

[Webb Sprague]

Hi All,

When I run ".dump" on a small table I created at the prompt, it works
fine.  But when I try to run .dump on a big table it only gives me the
table schema, pauses, then stops (see below for example).  It happens
when I run in batch  from the command line, too.  I am able to select
from the large table just fine ('select count(*) from nlsy79;' gives
25374, group by's work fine, etc.).

I am working with a modified 3.6.7 codebase (edited various max_*
modified to be very large).  Though I can't imagine why that would
matter, unless there is something that won't dump unless under a
certain max.

(Note that I am not looking for solutions that require that I truncate
the table or get rid of columns, because of the nature of the
problem.)

Example 1, first table, works fine:

sqlite> .dump blah
BEGIN TRANSACTION;
CREATE TABLE blah (i int, s text);
INSERT INTO "blah" VALUES(10,'blah blah');
COMMIT;

Example 2, second table, notice lack of insert statements:

sqlite> .dump NLSY79
BEGIN TRANSACTION;
CREATE TABLE NLSY79 ( "Obs" integer
, "R100" integer
, "R149" integer
, "R150" integer
--... (SNIP some 2000+ columns)
 , "W0072300" integer
, "W0072400" integer
);
COMMIT;

Thanks for all the help!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using Mac osx to update sqlite

[Jay A. Kreibich]

On Sat, Jan 03, 2009 at 05:54:28PM -0500, Mark Fraser scratched on the wall:
> Jay,
>
> Thanks for your reply.
>
>> It might make more sense to install a newer version in /usr/local
>>  rather than /usr.  That way you can use the updated version for your 
>
> That is what I did.  I downloaded and extracted the .bin file and copied  
> it to /usr/local/bin.
>
> Then after chmod +x it I ran it and got the Bus error.
>
> By what you are saying it appears that the one I already had in  
> /usr/local/bin was not the original OS X version, so maybe it was  
> installed by another application.  I do have an older version in  
> /usr/bin which is ostensibly the OS X delivered version.

  Sounds like it.  It is Apple's policy to keep their hands out of
  /usr/local, so something else must have put that there.

> Any other suggestions?

  Not really.  I assume you noticed that the pre-compiled binary
  download is for Intel-based Macs only.  If you have an older PPC
  Mac, you need to build the executable from source.

  Also, .bin is an old System Seven era Mac encoding format used to 
  encode applications and other resource-fork files.  The download is
  NOT a .bin file-- just gunzip the download, chmod, and that's all
  you should need to do.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] History of SQL

[Satish]

Hi all,

 

I want to set up a history of changes in a database schema. I hoped I could
simply set up triggers for changes to the SQLite_Master table, like this:

 

create table "BF SQLite_Master History"   -- record changes to  

SQLite_Master

(

ID integer primary key

  , Date date -- julianday utc of the date & time of the
change

  , Event text  -- insert, delete, or update

  , SQL text  -- SQL prior to change for delete and update,
after  

change for insert

)

;

 

create trigger "BF SQLite_Master Insert"

before insert

on SQLite_Master

begin

insert into "BF SQLite_Master History"

(

Date

  , Event

  , SQL

)

select

julianday('now', 'utc')

  , 'insert'

  , new.SQL

;

end

;

 

But I get an error:

 

SQL error near line 1: cannot create trigger on system table

 

Is it possible to enable this functionality?

 

Thanks,

G.Satish

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