gambas 3 and mysql

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

gambas 3 and mysql

Unaise EK
hai,
i worte these code for storing data into mysql database, all data stored
into database except date_ad.


Public Sub Save_button_Click()
Dim InsertDb As String
'Dim Dx As Date
'Format(Dx, "dd/mm/yyyy")
InsertDb = "INSERT INTO name_tbl (adm, name, place, date_ad) VALUES ('" &
(TextBox1.Text) & "','" & (TextBox2.Text) & "', '" & (TextBox3.Text) & "',
'" & DateBox1.Value & "' )"
MODMain.MyConn.Exec(InsertDb)
message("Data saved")
clear1


pls help
--
M. Unaise. E.K
9895687604
Librarian,
(BLISc, MLIS)
JDT Islam Polytechnic College,
Vellimadukunnu.
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|

Re: gambas 3 and mysql

Tobias Boege-2
On Wed, 14 Jun 2017, Unaise EK wrote:

> hai,
> i worte these code for storing data into mysql database, all data stored
> into database except date_ad.
>
>
> Public Sub Save_button_Click()
> Dim InsertDb As String
> 'Dim Dx As Date
> 'Format(Dx, "dd/mm/yyyy")
> InsertDb = "INSERT INTO name_tbl (adm, name, place, date_ad) VALUES ('" &
> (TextBox1.Text) & "','" & (TextBox2.Text) & "', '" & (TextBox3.Text) & "',
> '" & DateBox1.Value & "' )"
> MODMain.MyConn.Exec(InsertDb)
> message("Data saved")
> clear1
>
>
> pls help

I think the error comes from the fact that you take DateBox1.Value and
concatenate it to a string. This triggers an implicit converion of the
Date value into a string -- but the way Gambas does this conversion is
not compatible with how MySQL excepts dates to be formatted:

  $ gbx3 -e '"|" & Now() & "|"'
  |06/14/2017 14:16:29.291|

Above is how Gambas serialises a date when concatenating it with a string
(apparently it is independent of the locale, at least), but the MySQL
documentation [1] tells you that the date separator should not be a "/"
slash, as in Gambas, but a "-" hyphen (there may be other incompatibilities,
but one is enough already for the insert to fail).

How to fix this? Writing your own "INSERT INTO" statement is already a
bad idea. Gambas' database drivers can write those statements correctly
for you already. They also prevent SQL injection attacks, such as the one
that is blatantly present in your code. Something like this would be
preferable:

  Dim hInsert As Result

  hInsert = MODMain.MyConn.Create("name_tbl")
  With hInsert
    !adm     = TextBox1.Text
    !name    = TextBox2.Text
    !place   = TextBox3.Text
    !date_ad = DateBox1.Value
  End With
  hInsert.Update()

If you haven't seen this before, look up the corresponding documentation
[2][3], or ask more specific questions.

Regards,
Tobi

[1] https://dev.mysql.com/doc/refman/5.5/en/date-and-time-literals.html
[2] http://gambaswiki.org/wiki/comp/gb.db/connection/create
[3] http://gambaswiki.org/wiki/comp/gb.db/result/update

--
"There's an old saying: Don't change anything... ever!" -- Mr. Monk

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|

Re: gambas 3 and mysql

Unaise EK
Problem solved

Public Sub Save_button_Click()
try MODMain.MyConn.Exec( "INSERT INTO name_tbl (adm, name, place, date_ad)
VALUES (&1,&2,&3,&4)",textbox1.text,textbox2,text,textbox4.text,
datebox1.value)
if not error then
    message("Data saved")
endif

On 14 Jun 2017 4:01 p.m., "Tobias Boege" <[hidden email]> wrote:

On Wed, 14 Jun 2017, Unaise EK wrote:

> hai,
> i worte these code for storing data into mysql database, all data stored
> into database except date_ad.
>
>
> Public Sub Save_button_Click()
> Dim InsertDb As String
> 'Dim Dx As Date
> 'Format(Dx, "dd/mm/yyyy")
> InsertDb = "INSERT INTO name_tbl (adm, name, place, date_ad) VALUES ('" &
> (TextBox1.Text) & "','" & (TextBox2.Text) & "', '" & (TextBox3.Text) & "',
> '" & DateBox1.Value & "' )"
> MODMain.MyConn.Exec(InsertDb)
> message("Data saved")
> clear1
>
>
> pls help

I think the error comes from the fact that you take DateBox1.Value and
concatenate it to a string. This triggers an implicit converion of the
Date value into a string -- but the way Gambas does this conversion is
not compatible with how MySQL excepts dates to be formatted:

  $ gbx3 -e '"|" & Now() & "|"'
  |06/14/2017 14:16:29.291|

Above is how Gambas serialises a date when concatenating it with a string
(apparently it is independent of the locale, at least), but the MySQL
documentation [1] tells you that the date separator should not be a "/"
slash, as in Gambas, but a "-" hyphen (there may be other incompatibilities,
but one is enough already for the insert to fail).

How to fix this? Writing your own "INSERT INTO" statement is already a
bad idea. Gambas' database drivers can write those statements correctly
for you already. They also prevent SQL injection attacks, such as the one
that is blatantly present in your code. Something like this would be
preferable:

  Dim hInsert As Result

  hInsert = MODMain.MyConn.Create("name_tbl")
  With hInsert
    !adm     = TextBox1.Text
    !name    = TextBox2.Text
    !place   = TextBox3.Text
    !date_ad = DateBox1.Value
  End With
  hInsert.Update()

If you haven't seen this before, look up the corresponding documentation
[2][3], or ask more specific questions.

Regards,
Tobi

[1] https://dev.mysql.com/doc/refman/5.5/en/date-and-time-literals.html
[2] http://gambaswiki.org/wiki/comp/gb.db/connection/create
[3] http://gambaswiki.org/wiki/comp/gb.db/result/update

--
"There's an old saying: Don't change anything... ever!" -- Mr. Monk

------------------------------------------------------------
------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user