[sqlite] How to use LIKE wildcards and bound parameters?
Hi All, Could someone tell me how to use bound parameters with LIKE and where the % wildcards should be placed? I currently have the following code extract: char *sql = "SELECT * FROM foo WHERE name LIKE :bp"; rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, &tail); sqlite3_bind_text( stmt, sqlite3_bind_parameter_index(stmt, "%:bp%"), "bar", -1, SQLITE_TRANSIENT ); It compiles but doesn't return any results. If I directly query the database using: SELECT * FROM foo WHERE name LIKE %bar%; This returns results. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use LIKE wildcards and bound parameters?
On Sat, 18 Jun 2011 17:01:41 +1000, Zettai Muri wrote: >Hi All, > >Could someone tell me how to use bound parameters with LIKE and where >the % wildcards should be placed? > >I currently have the following code extract: >char *sql = "SELECT * FROM foo WHERE name LIKE :bp"; > >rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, &tail); >sqlite3_bind_text( stmt, > sqlite3_bind_parameter_index(stmt, "%:bp%"), > "bar", > -1, > SQLITE_TRANSIENT ); > >It compiles but doesn't return any results. > >If I directly query the database using: >SELECT * FROM foo WHERE name LIKE %bar%; > >This returns results. Have the C program include the % in the text parameter: sqlite3_bind_text( stmt, sqlite3_bind_parameter_index(stmt, "%:bp%"), "%bar%", .. Or rewrite the SQL as char *sql = "SELECT * FROM foo WHERE name LIKE '%' || :bp || '%'"; -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use LIKE wildcards and bound parameters?
> Have the C program include the % in the text parameter: > > sqlite3_bind_text( stmt, > sqlite3_bind_parameter_index(stmt, "%:bp%"), > "%bar%", .. Just out of interest would there be a way to use a variable here with the % wildcards as in: char* myvar = "teststr"; sqlite3_bind_text( stmt, sqlite3_bind_parameter_index(stmt, ":bp"), "%myvar%", .. Other than the literal string? > > Or rewrite the SQL as > > char *sql = "SELECT * FROM foo WHERE name LIKE '%' || :bp || '%'"; Thank you very much for this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OSX path
Hello John, > As for "top-posting," In the corporate culture I come from, it is > considered the only way to do things (or at least one faction who > has the ear of the ops boss, believes this). Yes, I noticed that too. I can think of two reasons for doing things in this terribly inefficient way: a) people got started mailing with Outlook, which for several years didn't support threading and therefore no easy reference to earlier material was available (mind you, pretty much any other mailtool did threading years before MS actually came up with Outlook, so I wonder what their excuse is). In fact, the MS Exchange server even implements a specific protocol extension to support to-posting (only the new text needs to be send to the server, which copies the old text straight from the referenced mail). b) people think that their time (spend editing) is much more important that the readers time. But in truth it's probably just plain ignorance that started it. Or maybe in your company conversations really go like this: Well, it's just easier that way. > So what do you like about B? > > Well, personally I would prefer B... > > > Do you think A is a good idea? but personally I believe it's easier to read: > > > Do you think A is a good idea? > > Well, personally I would prefer B... > So what do you like about B? Well, it's just easier that way. Sven -- __ _ _ __ __ __ / _` || ' \ \ \ / http://www.svenutcke.de/ \__, ||_|_|_|/_\_\http://www.dr-utcke.de/ |___/ Key fingerprint = 6F F8 55 1C F9 E3 A8 F7 09 DF F7 2C 25 0C 54 53 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use LIKE wildcards and bound parameters?
Zettai Muri wrote: > Just out of interest would there be a way to use a variable here with > the % wildcards as in: > char* myvar = "teststr"; > > sqlite3_bind_text( stmt, >sqlite3_bind_parameter_index(stmt, ":bp"), > "%myvar%", .. > > Other than the literal string? char myexpandedvar[10]; sprintf(myexpandedvar, "%%%s%%", myvar); sqlite3_bind_text(..., myexpandedvar, ...); In other words, you can use string manipulation facilities available in your programming language. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Critical issue
Dear all, Since I am using the new version of SQLite.NET, some of my users have the following error when launching my application: System.DllNotFoundException: Impossible de charger la DLL 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de HRESULT : 0x8007007E) à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] utf8Filename, Int32 flags, IntPtr& db) à System.Data.SQLite.SQLite3.Open(String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) à System.Data.SQLite.SQLiteConnection.Open() However, the SQLite.Interop is in the application folder. I specify that I am using VB 2010 Express with the framework 4.0. Sorry but I really have troubles: if I cannot solve this critical issue, I cannot see what to do but stopping the development of my application :-( Thank you very much in advance Best regards, Cyrille ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Critical issue
Am 18.06.2011 14:47, schrieb Cyrille: > Dear all, > > Since I am using the new version of SQLite.NET, some of my users have > the following error when launching my application: > > System.DllNotFoundException: Impossible de charger la DLL > 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de > HRESULT : 0x8007007E) > à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] > utf8Filename, Int32 flags, IntPtr& db) > à System.Data.SQLite.SQLite3.Open(String strFilename, > SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) > à System.Data.SQLite.SQLiteConnection.Open() > > However, the SQLite.Interop is in the application folder. I specify that > I am using VB 2010 Express with the framework 4.0. > > Sorry but I really have troubles: if I cannot solve this critical issue, > I cannot see what to do but stopping the development of my application :-( > Thank you very much in advance > Best regards, > Cyrille Are you sure that your users have the right dlls according to their system (x86 vs x64)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Critical issue
See if this thread helps you -- a couple of different solutions. Both of which you should be able to solve for your users. http://forums.asp.net/t/939729.aspx/1 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of beell [be...@web.de] Sent: Saturday, June 18, 2011 8:37 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Critical issue Am 18.06.2011 14:47, schrieb Cyrille: > Dear all, > > Since I am using the new version of SQLite.NET, some of my users have > the following error when launching my application: > > System.DllNotFoundException: Impossible de charger la DLL > 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de > HRESULT : 0x8007007E) > à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] > utf8Filename, Int32 flags, IntPtr& db) > à System.Data.SQLite.SQLite3.Open(String strFilename, > SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) > à System.Data.SQLite.SQLiteConnection.Open() > > However, the SQLite.Interop is in the application folder. I specify that > I am using VB 2010 Express with the framework 4.0. > > Sorry but I really have troubles: if I cannot solve this critical issue, > I cannot see what to do but stopping the development of my application :-( > Thank you very much in advance > Best regards, > Cyrille Are you sure that your users have the right dlls according to their system (x86 vs x64)? ___ 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] Critical issue
Also...try dependency walker to ensure you have all the DLLs you need deployed with your package that aren't "standard". http://www.dependencywalker.com/ Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of beell [be...@web.de] Sent: Saturday, June 18, 2011 8:37 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Critical issue Am 18.06.2011 14:47, schrieb Cyrille: > Dear all, > > Since I am using the new version of SQLite.NET, some of my users have > the following error when launching my application: > > System.DllNotFoundException: Impossible de charger la DLL > 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de > HRESULT : 0x8007007E) > à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] > utf8Filename, Int32 flags, IntPtr& db) > à System.Data.SQLite.SQLite3.Open(String strFilename, > SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) > à System.Data.SQLite.SQLiteConnection.Open() > > However, the SQLite.Interop is in the application folder. I specify that > I am using VB 2010 Express with the framework 4.0. > > Sorry but I really have troubles: if I cannot solve this critical issue, > I cannot see what to do but stopping the development of my application :-( > Thank you very much in advance > Best regards, > Cyrille Are you sure that your users have the right dlls according to their system (x86 vs x64)? ___ 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] Critical issue
Dear Beell and Michael, Thank you very much for your respective answers. I am checking but I am pretty sure that users who experience this issue are using my application with windows 64 bits and I made my project only with... 32 bits DLLs of SQLite.NET. Thus, now my next question is: each time I will release a new version of my application, should I clone my VB project and modify the SQLite.net DLL in the references by the 64 bits version and re-built a new EXE? Or, I just replace, in the built application, the SQLite.Net DLL by the 64 bits one? If my question is not clear, just let me know and I will try to ask it differently :) Best regards, Cyrille Le 18/06/2011 15:37, beell a écrit : > Am 18.06.2011 14:47, schrieb Cyrille: >> Dear all, >> >> Since I am using the new version of SQLite.NET, some of my users have >> the following error when launching my application: >> >> System.DllNotFoundException: Impossible de charger la DLL >> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de >> HRESULT : 0x8007007E) >> à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] >> utf8Filename, Int32 flags, IntPtr& db) >> à System.Data.SQLite.SQLite3.Open(String strFilename, >> SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) >> à System.Data.SQLite.SQLiteConnection.Open() >> >> However, the SQLite.Interop is in the application folder. I specify that >> I am using VB 2010 Express with the framework 4.0. >> >> Sorry but I really have troubles: if I cannot solve this critical issue, >> I cannot see what to do but stopping the development of my application :-( >> Thank you very much in advance >> Best regards, >> Cyrille > > Are you sure that your users have the right dlls according to their > system (x86 vs x64)? > > > ___ > 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] Critical issue
It seems that I have been too quickly enthusiastic. Indeed, I have already received a feedback from a user who has the same issue running a 32-bit Windows. Thus, I tried to follow your advices Michael. Concerning the first link which advice putting the DLL in the environment path. I am not sure to understand but: - the application is correctly running from VB express - all DLLs are in the application folder (once built) So, this issue seems to be ok, am I right? In your second message, you suggest me to use Dependency Walker. I did it and I obtain two errors: Warning: At least one delay-load dependency module was not found. Warning: At least one module has an unresolved import due to a missing export function in a delay-load dependent module. The warnings seem to be linked to the error displayed in the same software: Error opening file for IESHIMS.DLL However, this DLL should be in the IE folder so I do not think I have to copy it in my application folder. Right? Sorry if my questions are stupid but I cannot figure it out. Best regards, Cyrille Le 18/06/2011 15:50, Black, Michael (IS) a écrit : > Also...try dependency walker to ensure you have all the DLLs you need > deployed with your package that aren't "standard". > > http://www.dependencywalker.com/ > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of beell [be...@web.de] > Sent: Saturday, June 18, 2011 8:37 AM > To: sqlite-users@sqlite.org > Subject: EXT :Re: [sqlite] Critical issue > > Am 18.06.2011 14:47, schrieb Cyrille: >> Dear all, >> >> Since I am using the new version of SQLite.NET, some of my users have >> the following error when launching my application: >> >> System.DllNotFoundException: Impossible de charger la DLL >> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de >> HRESULT : 0x8007007E) >> à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] >> utf8Filename, Int32 flags, IntPtr& db) >> à System.Data.SQLite.SQLite3.Open(String strFilename, >> SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) >> à System.Data.SQLite.SQLiteConnection.Open() >> >> However, the SQLite.Interop is in the application folder. I specify that >> I am using VB 2010 Express with the framework 4.0. >> >> Sorry but I really have troubles: if I cannot solve this critical issue, >> I cannot see what to do but stopping the development of my application :-( >> Thank you very much in advance >> Best regards, >> Cyrille > > Are you sure that your users have the right dlls according to their > system (x86 vs x64)? > > > ___ > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Critical issue
If the IE folder is not on the path, yes, you have to either copy it somewhere on the path, copy it to your application folder, or add the IE folder to the path. I believe that not having the IE folder on the path is a mistake in the installer for the newer versions of IE. Back in the day, I think that the IE folder used to be on the path automatically. This is a good example of bad practices on Microsoft's part and also how they have tied IE into the operating system so that you can't get rid of it. I think that this is a typical issue with just about any application (Depends will show a missing delay load) because shell32.dll on newer versions of Windows has this delay load dependency on ieshims.dll. However, usually this is not a problem because usually the delayed load isn't invoked because nothing in the DLL is used. You might want to use Dependency Walker to profile your app and get a good idea of what the exact issue is. If your app is 32-bit, you'll need the 32-bit depends.exe to profile the app. I'm going to bet that if all of your users aren't having the problem, then the ones with IE7 or earlier are good and the ones with IE8 or later are the ones having the problem. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille Sent: Saturday, June 18, 2011 11:36 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Critical issue It seems that I have been too quickly enthusiastic. Indeed, I have already received a feedback from a user who has the same issue running a 32-bit Windows. Thus, I tried to follow your advices Michael. Concerning the first link which advice putting the DLL in the environment path. I am not sure to understand but: - the application is correctly running from VB express - all DLLs are in the application folder (once built) So, this issue seems to be ok, am I right? In your second message, you suggest me to use Dependency Walker. I did it and I obtain two errors: Warning: At least one delay-load dependency module was not found. Warning: At least one module has an unresolved import due to a missing export function in a delay-load dependent module. The warnings seem to be linked to the error displayed in the same software: Error opening file for IESHIMS.DLL However, this DLL should be in the IE folder so I do not think I have to copy it in my application folder. Right? Sorry if my questions are stupid but I cannot figure it out. Best regards, Cyrille Le 18/06/2011 15:50, Black, Michael (IS) a écrit : > Also...try dependency walker to ensure you have all the DLLs you need deployed with your package that aren't "standard". > > http://www.dependencywalker.com/ > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org > [sqlite-users-boun...@sqlite.org] on behalf of beell [be...@web.de] > Sent: Saturday, June 18, 2011 8:37 AM > To: sqlite-users@sqlite.org > Subject: EXT :Re: [sqlite] Critical issue > > Am 18.06.2011 14:47, schrieb Cyrille: >> Dear all, >> >> Since I am using the new version of SQLite.NET, some of my users have >> the following error when launching my application: >> >> System.DllNotFoundException: Impossible de charger la DLL >> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception >> de HRESULT : 0x8007007E) >> à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[] >> utf8Filename, Int32 flags, IntPtr& db) >> à System.Data.SQLite.SQLite3.Open(String strFilename, >> SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) >> à System.Data.SQLite.SQLiteConnection.Open() >> >> However, the SQLite.Interop is in the application folder. I specify >> that I am using VB 2010 Express with the framework 4.0. >> >> Sorry but I really have troubles: if I cannot solve this critical >> issue, I cannot see what to do but stopping the development of my >> application :-( Thank you very much in advance Best regards, Cyrille > > Are you sure that your users have the right dlls according to their > system (x86 vs x64)? > > > ___ > 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-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