Re: [sqlite] What am I doing wrong?

[Luuk]

On 8-8-2014 23:57, Errol Emden wrote:

The SQL script you wrote actually provides the same information as mine
- it lists all movies that Julie Andrews is in but it does NOT provide
who is the leading actor in each movie, as all names selected is that of
Julie Andrews.



must have missed that ;-)

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

Re: [sqlite] What am I doing wrong?

[Errol Emden]

Keith...as a neophyte, I am totally blown away by your skill level in SQL and 
the ease with which you explain its underlying operations. I am extremly 
impressed by the 'flattening of the query' and its operation...it worked 
perfectly!!. 

I am so happy that this forum has persons of your caliber who totally 
understands SQL and can provide efficient solutions to problems forum members, 
like myself, may have. Again, thanks and be well.

> Subject: RE: [sqlite] What am I doing wrong?
> Date: Fri, 8 Aug 2014 17:40:28 -0600
> From: kmedc...@dessus.com
> To: eem...@hotmail.com
> CC: sqlite-users@sqlite.org
> 
> 
> Leaving out the extra fields the movie table, enforcing referential 
> integrity, and using "same column names" to hold the same data, and declaring 
> the right indexes, you can get a much more efficient solution, both in the 
> expression and the execution:
> 
> These optimizations are only necessary *after* you have solved the problem, 
> and then really only if performance is inadequate.  Enforcement of 
> referential integrity and ensuring good indexes should always be done, of 
> course.
_
> >> 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] What am I doing wrong?

[Keith Medcalf]

 query and is somewhat implementation 
dependent.  Some SQL optimizers may arrive at the same end result given the 
original simpler query, and some may have other solution methods that are more 
efficient depending on how the query is phrased.

Theoretically, the plan used to execute the query (ie, obtain results) should 
be independent of which of the many ways you could phrase the query.  However, 
almost no query optimizer is perfect and each database will have quirks that 
you may need to use to phrase the query so that an optimum execution is 
obtained.  However, you really only want/need to do these optimizations after 
arriving at a working "most direct" (or brute force) translation in order to 
remove obvious inefficiency and perhaps coax maximum performance from the 
solution.


>-Original Message-
>From: Errol Emden [mailto:eem...@hotmail.com]
>Sent: Friday, 8 August, 2014 16:20
>To: General Discussion of SQLite Database; kmedc...@dessus.com
>Subject: RE: [sqlite] What am I doing wrong?
>
>Keith...thanks vmuch for your insightful and enlightening approach of
>translating 'in parts'. I am truly indebted to you for your mentoring
>approach...be well.
>
>
>
>> Date: Fri, 8 Aug 2014 15:03:13 -0600
>> From: kmedc...@dessus.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] What am I doing wrong?
>>
>> >I am to list the film title and the leading actor for all of the films
>> >'Julie Andrews' played in.
>>
>> >The following is structure of the tables used:
>>
>> >movie(id, title, yr, director, budget, gross);
>> >actor(id, name);
>> >casting(movieid, actorid, ord).
>> >The column name ord has a value of 1 if the actor is in starring role.
>>
>> Translate the English in parts:
>>
>> "all the films "Julie Andrews" olayed in
>> ->
>> select movie.id
>> from movie, casting, actor
>> where movie.id = casting.movieid
>> and casting.actorid = actor.id
>> and actor.name = 'Julie Andrews'
>>
>> then wrap the part "list the film title and the leading actor" around
>that list of films:
>>
>> select movie.name, actor.name
>> from movie, casting, actor
>> where movie.id = casting.movieid
>> and casting.actorid = actor.id
>> and casting.org = 1
>> and movie.id in (select movie.id
>> from movie, casting, actor
>> where movie.id = casting.movieid
>> and casting.actorid = actor.id
>> and actor.name = 'Julie Andrews');
>>
>> Being able to translate problem statements from well formed human
>language into well formed computer language statement(s) "The Solution"
>is what separates "programmers" from "coders". The problem statement does
>not require any ordering of the results, so they are in visitation order
>(which is not random, but may appear to be random to "coders").
>>
>>
>>
>>
>>
>> ___
>> 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] What am I doing wrong?

[Errol Emden]

Keith...thanks vmuch for your insightful and enlightening approach of 
translating 'in parts'. I am truly indebted to you for your mentoring 
approach...be well.


> Date: Fri, 8 Aug 2014 15:03:13 -0600
> From: kmedc...@dessus.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What am I doing wrong?
> 
> >I am  to list the film title and the leading actor for all of the films
> >'Julie Andrews' played in. 
> 
> >The following is structure of the tables used:
> 
> >movie(id, title, yr, director, budget, gross);
> >actor(id, name);
> >casting(movieid, actorid, ord). 
> >The column name ord has a value of 1 if the actor is in starring role.
> 
> Translate the English in parts:
> 
> "all the films "Julie Andrews" olayed in
> ->
> select movie.id
>   from movie, casting, actor
>  where movie.id = casting.movieid
>and casting.actorid = actor.id
>and actor.name = 'Julie Andrews'
> 
> then wrap the part "list the film title and the leading actor" around that 
> list of films:
> 
> select movie.name, actor.name
>   from movie, casting, actor
>  where movie.id = casting.movieid
>and casting.actorid = actor.id
>and casting.org = 1
>and movie.id in (select movie.id
>   from movie, casting, actor
>  where movie.id = casting.movieid
>and casting.actorid = actor.id
>and actor.name = 'Julie Andrews');
> 
> Being able to translate problem statements from well formed human language 
> into well formed computer language statement(s) "The Solution" is what 
> separates "programmers" from "coders".  The problem statement does not 
> require any ordering of the results, so they are in visitation order (which 
> is not random, but may appear to be random to "coders").
> 
> 
> 
> 
> 
> ___
> 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] What am I doing wrong?

[Errol Emden]

The SQL script you wrote actually provides the same information as mine - it 
lists all movies that Julie Andrews is in but it does NOT provide who is the 
leading actor in each movie, as all names selected is that of Julie Andrews.

> Date: Fri, 8 Aug 2014 20:55:55 +0200
> From: luu...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What am I doing wrong?
> 
> On 8-8-2014 20:35, Errol Emden wrote:
> >
> >
> >
> > I am  to list the film title and the leading actor for all of the films 
> > 'Julie Andrews' played in. The following is structure of the tables used:
> > movie(id, title, yr, director, budget, gross);
> > actor(id, name);
> > casting(movieid, actorid, ord). The column name ord has a value of 1 if the 
> > actor is in starring role.
> >
> > My attempt at a solution, produces all the movie titles but the actors' 
> > names are all for Julie Andrews:
> >
> > titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie 
> > AndrewsDuet for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie 
> > AndrewsMary PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie 
> > AndrewsShrek the ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of 
> > EmilyJulie AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess 
> > DiariesJulie AndrewsThe Princess Diaries 2: Royal EngagementJulie 
> > AndrewsThe Sound of MusicJulie AndrewsThe Tamarind SeedJulie 
> > AndrewsThoroughly Modern MillieJulie AndrewsTooth FairyJulie AndrewsTorn 
> > CurtainJulie AndrewsVictor VictoriaJulie Andrews
> > The SQL code for the above is given below:
> > SELECT distinct m1.title, a1.name FROM casting c1
> > JOIN movie m1 on m1.id=c1.movieid
> > JOIN actor a1 on a1.id=c1.actorid
> > WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
> >  SELECT a2.name FROM actor a2
> >  WHERE c1.ord<>1 and a2.id=a1.id));
> >
> > Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for 
> > Despicable Me, etc.? I should appreciate your help in finding the 
> > appropriate solution. Thanks and be well.
> >Errol Emden
> >
> 
> This should list all movies with 'Julie Andrews' in a role
> (starring, or not):
> 
> SELECT distinct m1.title, a1.name FROM casting c1
> JOIN movie m1 on m1.id=c1.movieid
> JOIN actor a1 on a1.id=c1.actorid
> WHERE a1.name='Julie Andrews'
> 
> Can you explain why you added the part that i erased from the query?
> 
> Because, basically adding
> 'she should be starring' (ord=1)
> or
> 'she should have a role not starring'
> (c1.ord<>1 and a2.id=a1.id)
> 
> will make things unnecessarily complicated.
> 
> 
> ___
> 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] What am I doing wrong?

[Keith Medcalf]

On Friday, 8 August, 2014 13:46, Petite Abeille  said:

>On Aug 8, 2014, at 8:35 PM, Errol Emden  wrote:
>
>> I am  to list the film title and the leading actor for all of the films
>'Julie Andrews' played in.
>
>And another one, for diversity's sake...
>
>Assuming a slightly different data model:
>
>with
>AndrewsMovie
>as
>(
>  select  movie_cast.movie_id
>  frommovie_cast
>  joinperson
>  on  person.id = movie_cast.person_id
>  where   person.name = 'Andrews, Julie (I)'
>)
>selectmovie.year,
>  movie.title,
>  person.name,
>  movie_cast.attribute
>from  movie_cast
>join  movie
>onmovie.id = movie_cast.movie_id
>join  person
>onperson.id = movie_cast.person_id
>where movie_cast.attribute like '%<1>'
>and   exists
>  (
>select  1
>fromAndrewsMovie
>where   AndrewsMovie.movie_id = movie_cast.movie_id
>  )
>order by  movie.year,
>  movie.title;
>
>
>Sample for 2010:
>
>2010|"20 to 1" (2005) {Our All Time Favourite Films (#9.11)}|Newton,
>Bert|[Himself - Host]  <1>
>2010|"Breakfast" (2000) {(2010-05-24)}|Stayt, Charlie|[Himself -
>Presenter]  <1>
>2010|"The Daily Show" (1996) {Julie Andrews (#15.12)}|Stewart, Jon
>(I)|[Himself - Host]  <1>
>2010|"The Late Late Show with Craig Ferguson" (2005) {(#6.80)}|Ferguson,
>Craig (I)|[Himself - Host]  <1>
>2010|"The Oprah Winfrey Show" (1986) {(2010-10-28)}|Winfrey,
>Oprah|[Herself - Host]  <1>
>2010|Despicable Me (2010)|Carell, Steve|(voice)  [Gru]  <1>
>2010|Shrek Forever After (2010)|Myers, Mike (I)|(voice)  [Shrek]  <1>
>2010|Tooth Fairy (2010/I)|Johnson, Dwayne (I)|[Derek]  <1>

Or perhaps even more complicated from the TMS database:

bindings -> :lineup=0008100:X; :duration=24

  select date(schedule.time, 'unixepoch', 'localtime') as showdate,
 time(schedule.time, 'unixepoch', 'localtime') as showtime,
 map.channel as channel,
 callsign,
 coalesce(runtime / 60, duration / 60) as runtime,
 case hdtv when 1 then 'HD' else '  ' end as ishd,
 case new when 1 then 'NEW' else '   ' end as isnew,
 showtype,
 case when subtitle != '' then title || ': ' || subtitle else title end 
as title,
 description,
 (select group_concat(data, '; ')
from (select rolename || ': ' || group_concat(givenname || ' ' || 
surname, ', ') as data
from crewlink natural join crew
  natural join role
   where crewlink.program = program.program
group by rolename)
 ) as cast,
 (select group_concat(advisoryname, ', ')
from advlink, advisory
   where advlink.program = program.program
 and advlink.advisory = advisory.advisory
group by advlink.program) as advisories,
 (select group_concat(class, ', ')
from genrlink, genre
   where genrlink.program = program.program
 and genrlink.genre = genre.genre
group by genrlink.program) as genres,
 originalAirDate,
 year,
 starRating,
 syndicatedEpisodeNumber
from schedule, map, mychannels, station, program
   where map.lineup = :lineup
 and mychannels.lineup = map.lineup
 and mychannels.channel = map.channel
 and station.station = map.station
 and schedule.station = map.station
 and schedule.time >= strftime('%s', 'now') - 86400
 and schedule.time <= strftime('%s', 'now') + (3600 * :duration)
 and schedule.endtime > strftime('%s', 'now')
 and program.program = schedule.program
 and map.validfrom <= schedule.time
 and map.validto >= schedule.endtime
 and showtype != 'Paid Programming'
 and (   showtype not in ('Series', 'Sports', 'Paid programming')
  or series in mySeries
  or (substr(schedule.program, 1, 2) = 'EP' and new = 1)
  or series in (select distinct series
  from schedule join program using (program)
 where new
   and series is not null
   and program like 'EP%')
 )
 and map.channel between 200 and 314
order by 1,2,9,3;

to produce (after formatting):

--
   15:45 280 MCHD  1:34 HD Movie  Hyde Park on Hudson 
(2012); **+ Historical drama; Adult Situations; In
  June 1939, President 
Franklin Delano Roosevelt (Bill Murray) receives
  Britain's monarchs 
(Samuel West) at his home in upstate New York for an
  unforgettable 
weekend. Actor: Laura Linney, Bill 

Re: [sqlite] What am I doing wrong?

[Keith Medcalf]

>I am  to list the film title and the leading actor for all of the films
>'Julie Andrews' played in. 

>The following is structure of the tables used:

>movie(id, title, yr, director, budget, gross);
>actor(id, name);
>casting(movieid, actorid, ord). 
>The column name ord has a value of 1 if the actor is in starring role.

Translate the English in parts:

"all the films "Julie Andrews" olayed in
->
select movie.id
  from movie, casting, actor
 where movie.id = casting.movieid
   and casting.actorid = actor.id
   and actor.name = 'Julie Andrews'

then wrap the part "list the film title and the leading actor" around that list 
of films:

select movie.name, actor.name
  from movie, casting, actor
 where movie.id = casting.movieid
   and casting.actorid = actor.id
   and casting.org = 1
   and movie.id in (select movie.id
  from movie, casting, actor
 where movie.id = casting.movieid
   and casting.actorid = actor.id
   and actor.name = 'Julie Andrews');

Being able to translate problem statements from well formed human language into 
well formed computer language statement(s) "The Solution" is what separates 
"programmers" from "coders".  The problem statement does not require any 
ordering of the results, so they are in visitation order (which is not random, 
but may appear to be random to "coders").





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

Re: [sqlite] What am I doing wrong?

[Simon Slavin]

On 8 Aug 2014, at 7:35pm, Errol Emden  wrote:

> I am  to list the film title and the leading actor for all of the films 
> 'Julie Andrews' played in.

You know, one day we should actually solve someone's homework questions.  Just 
to mess with their head.

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

Re: [sqlite] What am I doing wrong?

[Petite Abeille]

On Aug 8, 2014, at 8:35 PM, Errol Emden  wrote:

> I am  to list the film title and the leading actor for all of the films 
> 'Julie Andrews' played in. 

And another one, for diversity’s sake…

Assuming a slightly different data model:

with
AndrewsMovie
as
(
  select  movie_cast.movie_id
  frommovie_cast
  joinperson
  on  person.id = movie_cast.person_id
  where   person.name = 'Andrews, Julie (I)'
)
selectmovie.year,
  movie.title,
  person.name,
  movie_cast.attribute
from  movie_cast
join  movie
onmovie.id = movie_cast.movie_id
join  person
onperson.id = movie_cast.person_id
where movie_cast.attribute like '%<1>'
and   exists
  (
select  1
fromAndrewsMovie
where   AndrewsMovie.movie_id = movie_cast.movie_id
  )
order by  movie.year,
  movie.title;


Sample for 2010:

2010|"20 to 1" (2005) {Our All Time Favourite Films (#9.11)}|Newton, 
Bert|[Himself - Host]  <1>
2010|"Breakfast" (2000) {(2010-05-24)}|Stayt, Charlie|[Himself - Presenter]  <1>
2010|"The Daily Show" (1996) {Julie Andrews (#15.12)}|Stewart, Jon (I)|[Himself 
- Host]  <1>
2010|"The Late Late Show with Craig Ferguson" (2005) {(#6.80)}|Ferguson, Craig 
(I)|[Himself - Host]  <1>
2010|"The Oprah Winfrey Show" (1986) {(2010-10-28)}|Winfrey, Oprah|[Herself - 
Host]  <1>
2010|Despicable Me (2010)|Carell, Steve|(voice)  [Gru]  <1>
2010|Shrek Forever After (2010)|Myers, Mike (I)|(voice)  [Shrek]  <1>
2010|Tooth Fairy (2010/I)|Johnson, Dwayne (I)|[Derek]  <1>



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

Re: [sqlite] What am I doing wrong?

[Simon Slavin]

On 8 Aug 2014, at 7:59pm, Simon Slavin  wrote:

> SELECT leadActor.name, movie.title FROM casting
> JOIN actor AS leadActor ON leadActor.id = casting.actorid
> JOIN movie ON movie.id = casting.movieid
> JOIN actor AS selectedActor ON selectedActor.id = casting.actorid
> WHERE casting.ord = 1 AND selectedActor.name = 'Julie Andrews'

Apologies.  This does not actually do what you want.  Your main table should 
actually be movie for this one.  JOIN once to find the leadActor, and JOIN 
again to make sure that Julie Andrews is in it.

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

Re: [sqlite] What am I doing wrong?

[Simon Slavin]

On 8 Aug 2014, at 7:35pm, Errol Emden  wrote:

> I am  to list the film title and the leading actor for all of the films 
> 'Julie Andrews' played in. The following is structure of the tables used:
> movie(id, title, yr, director, budget, gross);
> actor(id, name); 
> casting(movieid, actorid, ord). The column name ord has a value of 1 if the 
> actor is in starring role.


All the lead roles of all stars in all movies

SELECT leadActor.name, movie.title FROM casting
JOIN actor AS leadActor ON leadActor.id = casting.actorid
JOIN movie ON movie.id = casting.movieid
WHERE casting.ord = 1

All the lead roles of all stars in all movies Julie Andrews has been in:

SELECT leadActor.name, movie.title FROM casting
JOIN actor AS leadActor ON leadActor.id = casting.actorid
JOIN movie ON movie.id = casting.movieid
JOIN actor AS selectedActor ON selectedActor.id = casting.actorid
WHERE casting.ord = 1 AND selectedActor.name = 'Julie Andrews'

If would be more efficient to find out Julie's actorid first, and use that for 
filtering instead of her name.

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

Re: [sqlite] What am I doing wrong?

[Luuk]

On 8-8-2014 20:35, Errol Emden wrote:




I am  to list the film title and the leading actor for all of the films 'Julie 
Andrews' played in. The following is structure of the tables used:
movie(id, title, yr, director, budget, gross);
actor(id, name);
casting(movieid, actorid, ord). The column name ord has a value of 1 if the 
actor is in starring role.

My attempt at a solution, produces all the movie titles but the actors' names 
are all for Julie Andrews:

titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie AndrewsDuet 
for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie AndrewsMary 
PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie AndrewsShrek the 
ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of EmilyJulie 
AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess DiariesJulie 
AndrewsThe Princess Diaries 2: Royal EngagementJulie AndrewsThe Sound of 
MusicJulie AndrewsThe Tamarind SeedJulie AndrewsThoroughly Modern MillieJulie 
AndrewsTooth FairyJulie AndrewsTorn CurtainJulie AndrewsVictor VictoriaJulie 
Andrews
The SQL code for the above is given below:
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
 SELECT a2.name FROM actor a2
 WHERE c1.ord<>1 and a2.id=a1.id));

Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for 
Despicable Me, etc.? I should appreciate your help in finding the appropriate 
solution. Thanks and be well.
   Errol Emden



This should list all movies with 'Julie Andrews' in a role
(starring, or not):

SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE a1.name='Julie Andrews'

Can you explain why you added the part that i erased from the query?

Because, basically adding
'she should be starring' (ord=1)
or
'she should have a role not starring'
(c1.ord<>1 and a2.id=a1.id)

will make things unnecessarily complicated.


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

Re: [sqlite] What am I doing wrong ?

[Olivier FAURAX]

Igor Tandetnik a écrit :
> Olivier FAURAX wrote:
>> I tried with -1 and the length value of the string, but it didn't
>> help...
> 
> Post a small complete compilable program that reproduces the problem. I 
> don't see anything wrong in the code you've shown - hence, the problem 
> is likely in the code you haven't.

You are right, the problem was elsewhere: the data was not correctly
written to disk, due to a cache problem (missing flush). Thanks and
sorry for the noise.

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

Re: [sqlite] What am I doing wrong ?

[Igor Tandetnik]

Olivier FAURAX  wrote:
> I'm beginner and uses sqlite3.
> I'm trying to record informations in a database, but this fails.
> I have followed http://www.sqlite.org/c3ref/stmt.html
>
> rc = sqlite3_open(DB_FILENAME, _db);
> rc = sqlite3_exec(metadata_db,
>  "CREATE TABLE IF NOT EXISTS metadata "
>  "( filename TEXT, key INT, data BLOB, length INT);",
>  NULL, NULL,
>  );
> rc = sqlite3_prepare_v2(metadata_db,
>   "INSERT INTO metadata VALUES "
>   "( ?1, ?2, ?3, ?4 );",
>   0, , NULL);

The third parameter (where you pass 0) is the length of the string 
pointed to by second parameter. So, you effectively pass an empty string 
to sqlite3_prepare_v2. You must specify an actual string length, or -1 
to have the function calculate it automatically.

Igor Tandetnik



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

Re: [sqlite] What am I doing wrong ?

[Hoover, Jeffrey]

is autocommit on?
try adding a manual commit.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Olivier FAURAX
Sent: Thursday, March 19, 2009 10:56 AM
To: General Discussion of SQLite Database
Subject: [sqlite] What am I doing wrong ?

Hello,

I'm beginner and uses sqlite3.
I'm trying to record informations in a database, but this fails.
I have followed http://www.sqlite.org/c3ref/stmt.html

rc = sqlite3_open(DB_FILENAME, _db);
rc = sqlite3_exec(metadata_db,
  "CREATE TABLE IF NOT EXISTS metadata "
  "( filename TEXT, key INT, data BLOB, length INT);",
  NULL, NULL,
  );
rc = sqlite3_prepare_v2(metadata_db,
   "INSERT INTO metadata VALUES "
   "( ?1, ?2, ?3, ?4 );",
   0, , NULL);
rc = sqlite3_bind_text(sthl, 1, filename, 12, NULL);
rc = sqlite3_bind_int(sthl, 2, key);
rc = sqlite3_bind_blob(sthl, 3, data, data_length, NULL);
rc = sqlite3_bind_int(sthl, 4, data_length);
rc = sqlite3_step(sthl);
rc = sqlite3_finalize(sthl);
rc = sqlite3_close(metadata_db);

I checked all "rc", they are all 0, except sqlite3_step returns 101 (aka
SQLITE_DONE) which is correct according to doc.

The problem is that I obtain a correct database (with 4 fields), but
nothing in it. If I retry this code with an existing database, I still
have the table, but nothign in it.

The only working case is when I add a dummy sqlite3_exec before the
close : this way, the statement is executed (I see data in the db), but
the dummy sqlite3_exec isn't executed (it's a simple INSERT).
This suggests that there is a kind of "flushing" that must be done, and
that it has been done to exec the new sqlite3_exec.

Please let me know what I did wrong.

Have a nice day
-- 
Olivier FAURAX
___
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