[Gambas-user] Database question

[M. Cs.]

Thanks Caveat!
This little helped to me!
A good example is worthy a thousand words!

Csaba

2012/1/19, Caveat <Gambas at ...1950...>:
> 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
>
>
>
> ------------------------------------------------------------------------------
> 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
>