Re: A problem with quoteString in libSQLClient, and a question

[Sebastian Reitenbach]

On Wednesday, December 12, 2012 17:35 CET, Richard Frith-Macdonald 
<richard@tiptree.demon.co.uk> wrote: 
 
> 
> On 12 Dec 2012, at 15:54, Sebastian Reitenbach wrote:
> 
> > Hi,
> > 
> > for MPDCon, I use libSQLClient to save some data in a local SQLite database.
> > So far, everything works not too bad, I only have a problem with quoting 
> > strings:
> > libSQLClient provides a quoteString: method, which I thought I can use
> > to quote ' characters in the string. 
> > Now, when I don't quote the string, or when I use the libSQLClient method
> > quoteString, then MPDCon hangs, when it comes to a, in my case a file name,
> > containing a ' character.
> > 
> > When I use NSStrings  stringByReplacingOccurrencesOfString: withString:
> > as below, then it just quotes it right, and the query executes fine.
> > 
> > Code example is shown below:
> > 
> > 
> > - (void) setRating: (NSInteger) rating forFile: (NSString *) fileName
> > {
> >  NSString *quotedFileName, *query;
> > 
> >  // the following doesn't seem to work, but the line after it does the 
> > trick!
> >  //quotedFileName = [MPDConDB quoteString:fileName];
> >  quotedFileName = [fileName stringByReplacingOccurrencesOfString:@"'" 
> > withString:@"''"];
> >  query = [NSString stringWithFormat:@"INSERT OR REPLACE INTO \
> >                SongRatings(fileName, rating) values('%@', %i)", 
> > quotedFileName, rating];
> >  [MPDConDB execute: query, nil];
> > }
> 
> The problem is that your code is attempting to quote the string twice ... 
> once by calling the -quoteString: method,  and then again later by using 
> stringWithFormat to quote it.
> Try the following:
> 
> [MPDConDB execute: @"INSERT OR REPLACE INTO  SongRatings(fileName, rating) 
> VALUES (",
>   [MPDConDB quote: fileName], @", ", [MPDConDB quoteInteger: rating], @")", 
> nil];

Ah, now I got it working, a bit different, but works. What I did not expected 
was that it adds quotes also around the whole string.
I expected it only to quote ' signs within the string. Maybe I misread the 
documentation.
After printing out the whole query, I then saw what happened. I should have 
done that before and would probably have 
figured it out before ;)


> 
> > the question I have to libSQLClient is, whether there is a method I can 
> > query the 
> > SQLClient object, which database backends it supports?
> 
> > For example, on different systems, there may be backends for sqlite 
> > available,
> > on others not, depending on how libSQLClient was compiled. 
> > So far I haven't seen anything, but maybe overlooked it.
> 
> 
> Not directly... to use a database you need to know what it's type, location, 
> and login credentials are (since you need to supply those parameters to 
> create a client object) ... and when you try to access the database, if there 
> is no backend to support it, you'll get an exception (at the point when you 
> try to connect to it) so you can use that to detect whether the database 
> backend is available.
> 
> Alternatively, you could check for the existence of the backend bundle in the 
> normal library locations I suppose, but trapping the exception makes more 
> sense to me.
> 
> If building from source, at configure time, the configure script will tell 
> you which backends are being built.

Yes, I know, that is clear. But what I had in mind was:
In the MPDCon preferences, let the user choose the type of database he wants to 
use, either sqlite, mysql, postgres, ... whatever may be available.
I thought about this catching an exception, which may work for sqlite3, where I 
could just try to create a file somewhere, 
that would probably be complicated with postgresql or mysql, since I don't know 
the location and login credentials.

So the trick with detecting the bundle could probably work for me then. 
Now I only need to find a way to tell NSBundle to list me all bundles under 
/usr/local/lib/GNUstep/Bundles/SQLClient,
or wherever they may be installed.

Probably have to read up the NSBundle documentation ;)

thanks a lot,
Sebastian