Re: [sqlite] What am I doing wrong?
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?
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?
loops" method that SQLite uses to actually perform the 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?
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?
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?
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 Murray, Elizabeth
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
Re: [sqlite] What am I doing wrong?
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?
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?
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?
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?
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] 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. 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 ___ 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 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 ?
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. 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 ?
I tried : rc = sqlite3_prepare_v2(metadata_db, "INSERT INTO metadata VALUES " "( ?1, ?2, ?3, ?4 ); COMMIT;", -1, &sthl, NULL); But this didn't helped. Anyway, thanks for your help. Olivier Hoover, Jeffrey a écrit : > 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, &metadata_db); > rc = sqlite3_exec(metadata_db, > "CREATE TABLE IF NOT EXISTS metadata " > "( filename TEXT, key INT, data BLOB, length INT);", > NULL, NULL, > &error); > rc = sqlite3_prepare_v2(metadata_db, >"INSERT INTO metadata VALUES " >"( ?1, ?2, ?3, ?4 );", >0, &sthl, 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 -- Au bout de la chaîne de gestion des priorités Olivier FAURAX | +33 4 42 98 11 20 (243 en interne) ___ 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 a écrit : > 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, &metadata_db); >> rc = sqlite3_exec(metadata_db, >> "CREATE TABLE IF NOT EXISTS metadata " >> "( filename TEXT, key INT, data BLOB, length INT);", >> NULL, NULL, >> &error); >> rc = sqlite3_prepare_v2(metadata_db, >> "INSERT INTO metadata VALUES " >> "( ?1, ?2, ?3, ?4 );", >> 0, &sthl, 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. Thanks for your answer. I tried with -1 and the length value of the string, but it didn't help... Have a nice day -- 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 ?
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, &metadata_db); > rc = sqlite3_exec(metadata_db, > "CREATE TABLE IF NOT EXISTS metadata " > "( filename TEXT, key INT, data BLOB, length INT);", > NULL, NULL, > &error); > rc = sqlite3_prepare_v2(metadata_db, > "INSERT INTO metadata VALUES " > "( ?1, ?2, ?3, ?4 );", > 0, &sthl, 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 ?
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, &metadata_db); rc = sqlite3_exec(metadata_db, "CREATE TABLE IF NOT EXISTS metadata " "( filename TEXT, key INT, data BLOB, length INT);", NULL, NULL, &error); rc = sqlite3_prepare_v2(metadata_db, "INSERT INTO metadata VALUES " "( ?1, ?2, ?3, ?4 );", 0, &sthl, 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
[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, &metadata_db); rc = sqlite3_exec(metadata_db, "CREATE TABLE IF NOT EXISTS metadata " "( filename TEXT, key INT, data BLOB, length INT);", NULL, NULL, &error); rc = sqlite3_prepare_v2(metadata_db, "INSERT INTO metadata VALUES " "( ?1, ?2, ?3, ?4 );", 0, &sthl, 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