Discussion:
Can't exec an sql string
(too old to reply)
Fabricio Silva
2006-10-23 13:39:18 UTC
Permalink
Hello guys,

I have this sql: "select price from product order by cast (price as char)"
When I execute this sql inside phpMyAdmin it comes with the right result.
But when executing the sql inside Gambas 1.9.34 it comes with the error:
"Query failde: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near('price, ....."
I'm using price as varchar because in Brazil we use 147,00 instead of
147.00and I haven't figure out yet a way of showing the result with
"," whether
than dot when I use decimal or float types.

Regards
Fabricio Silva
R. Stormo
2006-10-23 14:53:52 UTC
Permalink
Post by Fabricio Silva
Hello guys,
I have this sql: "select price from product order by cast (price as char)"
When I execute this sql inside phpMyAdmin it comes with the right result.
"Query failde: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near('price, ....."
I'm using price as varchar because in Brazil we use 147,00 instead of
147.00and I haven't figure out yet a way of showing the result with
"," whether
than dot when I use decimal or float types.
Regards
Fabricio Silva
What about dont put the price at char it should come out as you want?

You could do a , nasty one,
dim myprice as string
myprice = rs!price
if myprice<>"" and NOT isNull(myprice) then
myprice = replace(myprice,".","-")
myprice = replace(myprice,",",".")
myprice = replace(myprice,"-",",")
else
myprice = "0.00"
end if


Regards Rohnny
My Gambas Community http://gamasforum.tk
--
View this message in context: http://www.nabble.com/Can%27t-exec-an-sql-string-tf2494260.html#a6954599
Sent from the gambas-user mailing list archive at Nabble.com.
Fabien Bodard
2006-10-23 18:39:46 UTC
Permalink
Post by Fabricio Silva
Hello guys,
I have this sql: "select price from product order by cast (price as char)"
When I execute this sql inside phpMyAdmin it comes with the right result.
"Query failde: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near('price, ....."
I'm using price as varchar because in Brazil we use 147,00 instead of
147.00and I haven't figure out yet a way of showing the result with
"," whether
than dot when I use decimal or float types.
and the 'format' command ?

format(value,pattern)

Regards
Post by Fabricio Silva
Fabricio Silva
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job
easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Gambas-user mailing list
https://lists.sourceforge.net/lists/listinfo/gambas-user
Benoit Minisini
2006-10-23 18:48:25 UTC
Permalink
Post by Fabricio Silva
Hello guys,
I have this sql: "select price from product order by cast (price as char)"
When I execute this sql inside phpMyAdmin it comes with the right result.
Are you sure that is SQL standard, and not a phpMyAdmin feature?
Post by Fabricio Silva
"Query failde: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near('price, ....."
I'm using price as varchar because in Brazil we use 147,00 instead of
147.00and I haven't figure out yet a way of showing the result with
"," whether
than dot when I use decimal or float types.
If price is numeric, then you must use the Str() or Format() function to
format the value according to your locale.

Regards,
--
Benoit Minisini
Fabricio Silva
2006-10-26 13:14:36 UTC
Permalink
I was trying this when saving data:

txtprice.Text = Replace$(txtprice.Text, ",", ".")
then executing the sql to save. ok.
So when the user types 1,25 it's saves 1.25 into a float type field.
Perfect.

But my problem is when showing the saved data. I tryed a tableview.
===============================================
PUBLIC SUB show_data()
DIM sql AS String
WITH tbvcadprodutos
.rows.count = 0
.columns.count = 4
.columns[0].text = "Código"
.columns[0].Width = 50
.columns[1].text = "Descrição"
.columns[1].Width = 250
.Columns[2].Text = "Quantidade"
.columns[2].Width = 80
.columns[3].text = "Preço"
.columns[3].Width = 70

END WITH
sql = "SELECT codigo, descricao, quantidade, preco FROM `produto` ORDER BY
`codigo` ASC"
global.preencher(tbvcadprodutos, sql)

=====================================================
Procedure preencher
=====================================================
PUBLIC SUB preencher(tbv AS tableview, qry AS String)
DIM i AS Integer
i = 0
rs1 = db.exec(qry)
DO WHILE i <= rs1.Fields.Count
tbv.rows.count = 0
IF rs1.count <> 0 THEN
tbv.columns.count = rs1.fields.count
tbv.rows.count = rs1.count
END IF
i = i + 1
rs1.MoveNext
LOOP
END
========================================================
and to show the data
========================================================
PUBLIC SUB tbvcadprodutos_Data(Row AS Integer, Column AS Integer)

arrtable[0] = "codigo"
arrtable[1] = "descricao"
arrtable[2] = "quantidade"
arrtable[3] = "preco"
global.rs1.MoveTo(Row)

tbvcadprodutos.data.Text = Str(global.rs1[arrtable[Column]])

END
==================================================

And the 'preco' values are shown wrongly.
34,00 typed by the user is displayed 34.
and 89,56 is displayed 89.56.

So I tried another way of showing the data:

tbvcadprodutos.data.Text = Format(global.rs1[arrtable[Column]], "#.00")

"Type mismatch: Wanted Float got String instead" error appears.
===================================================

I'm stuck in this. I tryed datasource and databrowser pair too... but I
can't manipulated this can I?

Plz help me out in this. I'm desesperated. :)

Best Reards

Fabricio
Benoit Minisini
2006-10-26 16:56:32 UTC
Permalink
Post by Fabricio Silva
txtprice.Text = Replace$(txtprice.Text, ",", ".")
then executing the sql to save. ok.
So when the user types 1,25 it's saves 1.25 into a float type field.
Perfect.
But my problem is when showing the saved data. I tryed a tableview.
===============================================
PUBLIC SUB show_data()
DIM sql AS String
WITH tbvcadprodutos
.rows.count = 0
.columns.count = 4
.columns[0].text = "Código"
.columns[0].Width = 50
.columns[1].text = "Descrição"
.columns[1].Width = 250
.Columns[2].Text = "Quantidade"
.columns[2].Width = 80
.columns[3].text = "Preço"
.columns[3].Width = 70
END WITH
sql = "SELECT codigo, descricao, quantidade, preco FROM `produto` ORDER
BY `codigo` ASC"
global.preencher(tbvcadprodutos, sql)
=====================================================
Procedure preencher
=====================================================
PUBLIC SUB preencher(tbv AS tableview, qry AS String)
DIM i AS Integer
i = 0
rs1 = db.exec(qry)
DO WHILE i <= rs1.Fields.Count
tbv.rows.count = 0
IF rs1.count <> 0 THEN
tbv.columns.count = rs1.fields.count
tbv.rows.count = rs1.count
END IF
i = i + 1
rs1.MoveNext
LOOP
END
========================================================
and to show the data
========================================================
PUBLIC SUB tbvcadprodutos_Data(Row AS Integer, Column AS Integer)
arrtable[0] = "codigo"
arrtable[1] = "descricao"
arrtable[2] = "quantidade"
arrtable[3] = "preco"
global.rs1.MoveTo(Row)
tbvcadprodutos.data.Text = Str(global.rs1[arrtable[Column]])
END
==================================================
And the 'preco' values are shown wrongly.
34,00 typed by the user is displayed 34.
and 89,56 is displayed 89.56.
tbvcadprodutos.data.Text = Format(global.rs1[arrtable[Column]], "#.00")
"Type mismatch: Wanted Float got String instead" error appears.
===================================================
I'm stuck in this. I tryed datasource and databrowser pair too... but I
can't manipulated this can I?
Plz help me out in this. I'm desesperated. :)
Best Reards
Fabricio
You are putting numeric values inside a database string field. So Gambas
automatically converts the numeric value into a string by using the CStr()
function. CStr() uses US localization.

Then, when you fill your TableView, you get a string from the database: the
result of CStr() applied to the number you wanted to store. And of course, if
you use Format(), the function complains because it gets a string instead of
a floating point value.

There are two solutions:

1) Using a database numeric field, and Str() or Format() to convert the value
in TableView_Data.

2) Keeping with your string field, but then you must use Str() or Format() to
store the data (so that you use your localization, not the US one). Then you
display the data in TableView_Data as it is stored.

Regards,
--
Benoit Minisini
Fabien Bodard
2006-10-23 18:39:46 UTC
Permalink
Post by Fabricio Silva
Hello guys,
I have this sql: "select price from product order by cast (price as char)"
When I execute this sql inside phpMyAdmin it comes with the right result.
"Query failde: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near('price, ....."
I'm using price as varchar because in Brazil we use 147,00 instead of
147.00and I haven't figure out yet a way of showing the result with
"," whether
than dot when I use decimal or float types.
and the 'format' command ?

format(value,pattern)

Regards
Post by Fabricio Silva
Fabricio Silva
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job
easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Gambas-user mailing list
https://lists.sourceforge.net/lists/listinfo/gambas-user
Continue reading on narkive:
Loading...