[Gambas-user] Database question

[Caveat]

As Benoit says...

> SQL quoting is automatically done by the following methods of the 
> Connection class: Exec(), Find(), Edit(), Delete(), Subst(), provided
> that:

> - You use "&1", "&2"... inside the request string to tell where
> quoted arguments must be inserted.

> - You actually pass these arguments.

Example:

  ...
  conn = DataAccess.getConnection()
  rSet = conn.Exec("select * from ART001 where CLEF = &1", "AS200000")
  ' ... or e.g. conn.Exec("select * from ART001 
  '               where CLEF = &1 AND DELE = &2", "AS200000", "0")
  '          ... etc.
  If rSet Not Null Then
    If rSet.Count > 0 Then
      rSet.MoveFirst
      Print rSet["CLEF"], rSet["NOMNL"]
    Endif
  Endif

Kind regards,
Caveat

On Thu, 2012-01-19 at 14:09 +0100, M. Cs. wrote:
> Some more clarification of my problem:
> I had two procedures to communicate with the sqlite database:
> 
> Public Sub Trans(be As String) As String
> Dim atmen As String
> atmen = Replace$(be, "$", ".")
> atmen = Replace$(atmen, "€", "-")
> atmen = Replace$(atmen, "_", " ")
> atmen = Replace$(atmen, "£", "/")
> atmen = Replace$(atmen, "¢", "+")
> atmen = Replace$(atmen, "§", "'")
> atmen = Replace$(atmen, "¥", ":")
> 
> Return atmen
> End
> 
> Public Sub TransB(be As String) As String
> Dim atmen As String
> atmen = Replace$(be, ".", "$")
> atmen = Replace$(atmen, "-", "€")
> atmen = Replace$(atmen, " ", "_")
> atmen = Replace$(atmen, "/", "£")
> atmen = Replace$(atmen, "+", "¢")
> atmen = Replace$(atmen, "'", "§")
> atmen = Replace$(atmen, ":", "¥")
> Return atmen
> End
> 
> These were the guards for special characters.
> The first one did the conversion from database to GUI, the second did
> encode the strings for the database insertion.
> I suppose there is a better way for doing it.
> I'd like to learn that way!
> 
> 2012/1/19, M. Cs. <mohareve at ...626...>:
> > I'm sorry Benoit,
> > I would like to ask your help with this kind of insertions:
> > If I have a table called MyTable with creation:
> >
> > CREATE TABLE CENTRAL(VName Text,FPath TEXT,FName TEXT,FSubs
> > INTEGER,FSize REAL,FChanged TEXT);
> >
> > How could I ensure the failsafe insert query with following fields:
> > VName-> Photos 5.
> > FPath -> /Image3 /2011
> > FName-> Brother's day.jpg
> > FSubs-> 1
> > FSize-> 568234.2
> > FChanged-> 2011-12-06
> >
> > So how to use &1 and &2 arguments in work?
> >
> > And how can I retrieve the fields where FName is Like sign ' as in
> > Brother's day.jpg?
> >
> > The query usually fails just like that If I try to seek for the files
> > containing certain characters which are in use by the Sqlite backend.
> >
> > I only need two lines of the answer. Thanks!
> >
> > Csaba
> >
> >
> > 2012/1/18, M. Cs. <mohareve at ...626...>:
> >> Yes, Benoit. I'll check the documentation, since I always used the
> >> Myconnection.Exec("SELECT FROM table WHERE field='"& myvar & "';) form
> >> of the querying, and now I would like to change it to a more
> >> convenient way, since I always had to assure myvar is safe.
> >>
> >> 2012/1/18, Benoît Minisini <gambas at ...1...>:
> >>> Le 18/01/2012 14:17, M. Cs. a écrit :
> >>>> Hi!
> >>>> Is there any built in function in Gambas3 which can secure the
> >>>> database connection from the errors caused by special characters?
> >>>>
> >>>> I have written functions for replacing the dangerous characters like
> >>>> ', +, . and so on, but I'd like to know whether there is a way to make
> >>>> queries secure from failures.
> >>>>
> >>>> Thanks!
> >>>>
> >>>
> >>> SQL quoting is automatically done by the following methods of the
> >>> Connection class: Exec(), Find(), Edit(), Delete(), Subst(), provided
> >>> that:
> >>>
> >>> - You use "&1", "&2"... inside the request string to tell where quoted
> >>> arguments must be inserted.
> >>>
> >>> - You actually pass these arguments.
> >>>
> >>> Is it what you need?
> >>>
> >>> --
> >>> Benoît Minisini
> >>>
> >>> ------------------------------------------------------------------------------
> >>> Keep Your Developer Skills Current with LearnDevNow!
> >>> The most comprehensive online learning library for Microsoft developers
> >>> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
> >>> Metro Style Apps, more. Free future releases when you subscribe now!
> >>> http://p.sf.net/sfu/learndevnow-d2d
> >>> _______________________________________________
> >>> Gambas-user mailing list
> >>> Gambas-user at lists.sourceforge.net
> >>> https://lists.sourceforge.net/lists/listinfo/gambas-user
> >>>
> >>
> >
> 
> ------------------------------------------------------------------------------
> Keep Your Developer Skills Current with LearnDevNow!
> The most comprehensive online learning library for Microsoft developers
> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
> Metro Style Apps, more. Free future releases when you subscribe now!
> http://p.sf.net/sfu/learndevnow-d2d
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user