Citando John Machin <sjmac...@lexicon.net>: > On 10/03/2009 10:56 PM, Andrea Galeazzi wrote: >> Hi All, >> I'm developing an application which relies on sqllite as back-end. Now >> I face to this problem: I've got a form that allows the user to fill a >> lot of fields, obliviously only a little part of them will actually be >> filled, the others isn't gonna be in the search criteria. So I prepare a >> parameterized query containing the whole possible fields like this: >> SELECT * FROM Song WHERE id = ? AND title = ? AND album LIKE '%?%'; >> How can I bind the unrequested fields? Does a trivial solution exist? >> Thanks > > I suspect that your use of the word "nullable" in the subject is causing > some confusion. > > If the user fills in only the title: > > SELECT * FROM Song WHERE id IS NULL AND title = ? AND album LIKE NULL; > ??? I don't think so. > > AFAICT you want the effect of SELECT * FROM Song WHERE title = ? ; > without the complexity of having 7 (or 15, or 31...) prepared statements > to cope with all possibilities. > > You can do this by using LIKE/GLOB/etc provided that all your columns > are text: > > sql = "SELECT * FROM Song WHERE id LIKE ? AND title LIKE ? AND album > LIKE ?;" > *NOTE* (pax Igor) no apostrophes on the RHS of LIKE -- let your DB > interface do the quoting for you. > Python example: > qid = "%" # Any id; don't care > qtitle = "%O'Reilly%" > # contains "O'Reilly"; the DB i/f will turn that into the equivalent of > ... LIKE '%O''Reilly%' ... > qalbum = "%" > cursor.execute(sql, (qid, qtitle, qalbum)) > > BTW, the query optimiser can ignore column1 LIKE '%' only when column1 > is declared as NOT NULL, so this way of doing it may not be the fastest :-( > > HTH, > John > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >
Yes, that's the problem: I don't wanna test a field for NULL, but I'd like to know the best way to ignore some field. Thanks for your advise but what about numeric field like id? I hope to write a query for each field combination isn't necessary! By the way I'm using C Api. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
