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
