You are not logged in.

Dear visitor, welcome to Jabaco - Community. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

1

Thursday, September 27th 2012, 4:30pm

Mysql SUM does not return something to me

Am i doing something wrong with the this statement ?
Set Data = Order.executeQuery("SELECT SUM(money) FROM mytable where esoda = 'ΕΣΟΔΑ' ORDER BY mydate")

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

2

Friday, September 28th 2012, 10:08am

Hey there,

when you are aggregating over a column you don't have to 'ORDER' it in any way!

MySQL queries

 
1

SELECT SUM(money) FROM mytable where esoda = 'ΕΣΟΔΑ'

should do the trick.


Dani
latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

3

Friday, September 28th 2012, 11:26am

Dani i found what the problem is, but i can not find the solution.
when i make a selection for example

SELECT SUM(money) FROM mytable where esoda = 'esode'
everything is ok but when i make a selection like
SELECT SUM(money) FROM mytable where esoda = 'ΕΣΟΔΑ'
with Greek world ΕΣΟΔΑ It returns NULL

My collation is utf-8 general ci

WHAT AM I DOING WRONG?

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

4

Friday, September 28th 2012, 2:24pm

Hey there,

Character encoding can be a real nightmare!
Did you by any chance bulg import data not setting the right encoding?
Can you be certain your data was collected with the same encoding?

Check your systems default encoding:

Jabaco Source

 
1

Debug.Print System.getProperty("file.encoding")



Make sure your client connection is set for UTF8 or suitable...

Jabaco Source

 
1

System.setProperty("file.encoding","UTF-8")


Read this article maybe you will find something new:
http://www.howtoforge.com/the-umlaut-pro…ing-mysqldumper

Happy googleing...


Dani
latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

5

Sunday, September 30th 2012, 11:44am

i did ... Debug.Print System.getProperty("file.encoding")
and returns ...
cp1253
utf-8

**the strange is when i make a selection for example**
select * from esoda_exoda and then...
variable=Data.get....
if variable='ΕΣΟΔΑ' ...do something
if variable='ΕΞΟΔΑ'... do something else

IT WORKS PERFECT.
But when i have as i told you a WHERE... or print it to msgbox it returns NOTHING!

so as i suppose it takes my data from db and check them but it can not do the opposite.
Do you have any idea please?

I test my database connection is utf-8, my database use utf-8 and my table have utf-8

This post has been edited 2 times, last edit by "fomaxrge" (Sep 30th 2012, 9:05pm)

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

6

Monday, October 1st 2012, 4:05pm

Hey there,

I have a H2 database encoded cp1253 and everything works fine:

Jabaco Source

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Option Explicit

Public Sub Form_Load()
   Dim conn As Connection 
   Dim qry As String 
   Dim st As java#sql#Statement 
   Dim rs As ResultSet
   Dim a As String 
   java#lang#Class.forName("org.h2.Driver")
   conn = DriverManager.getConnection("jdbc:h2:" & path & dbname, "sa", "")
   a = InputBox("Search for:")
   qry = "SELECT * FROM mytable WHERE esoda = '" & a & "';"
   st = conn.createStatement()
   rs = st.executeQuery(qry)
   rs.next
   Debug.Print rs.getString("esoda")
End Sub


How ever I just realized that if you copy/paste 'ΕΣΟΔΑ' into the Jabaco code editor it returns '?S???' !?

Dani
latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

7

Monday, October 1st 2012, 4:17pm

do you have any idea what i have to do?
Is it it strange that is returns Greek and make comparison with that strings to me, but does not return a where selection?

**i tried your code but it returns java.lang.ClassNotFoundException: org.h2.Driver on me**

Dim conn As Connection
Dim qry As String
Dim st As java#sql#Statement
Dim rs As ResultSet
Dim a As String

java#lang#Class.forName("org.h2.Driver")
conn = DriverManager.getConnection("jdbc:h2:mysql://localhost:3306/my_api_2012", "root", "")
a = "ΕΣΟΔΑ"
qry = "SELECT * FROM esoda_exoda WHERE esoda = '" & a & "';"
st = conn.createStatement()
rs = st.executeQuery(qry)
rs.next
Debug.Print rs.getString("esoda")

do i have something wrong?
do i have to download it from http://www.h2database.com/html/main.html
i found an article of you but in Germany and i can not understand it

This post has been edited 2 times, last edit by "fomaxrge" (Oct 1st 2012, 4:46pm)

Dani

Intermediate

Posts: 325

Date of registration: Nov 19th 2009

Location: GERMANY

  • Send private message

8

Monday, October 1st 2012, 4:59pm

Quoted

do you have any idea what i have to do?

If you don't mind you can switch your database to h2
You would have to download the latest package and copy the h2-1.3.169.jar to your machines' 'C:\Program Files\Java\jre7\lib\ext' or similar path.
Then press F1 in Jabaco and import h2-1.3.169.jar.
You would have to create your database file.
h2 has a very nice build in dbEditor http://www.h2database.com/html/quickstart.html
You can use my code to connect to your database.

Quoted

java#lang#Class.forName("org.h2.Driver")
conn = DriverManager.getConnection("jdbc:h2:mysql://localhost:3306/my_api_2012", "root", "")


You are mixing up h2 and mysql here this is not compatible!
Or did you already create a h2 database file?

Quoted

a = "ΕΣΟΔΑ"


I doubt this will show in the Jabaco editor ->

Quoted

How ever I just realized that if you copy/paste 'ΕΣΟΔΑ' into the Jabaco code editor it returns '?S???' !?


...or wait ... does it show when you type it?
latest Jabaco.jar:
Jabaco.jar on bitbucket since other links are down !!
Jabaco Framework source:
https://code.google.com/p/jabacoframework/source/list

This post has been edited 1 times, last edit by "Dani" (Oct 1st 2012, 5:05pm)

fomaxrge

Trainee

  • "fomaxrge" started this thread

Posts: 88

Date of registration: Aug 14th 2012

  • Send private message

9

Tuesday, October 2nd 2012, 11:57am

Hi Dani finally this "seams" to solve my problem...

DriverManager.getConnection(
"jdbc:mysql://"+host+"/"+dbName+"?useUnicode=true&characterEncoding=UTF-8",user,pass);

I would like to use H2 because it is very good solution as i read, but i have already create many tables in my database and i am scared of make any changes.

Thank you very very much for your help.
You make me understand what was happenig so to find the solution.

Thank you very much again!

Similar threads

Rate this thread
WoltLab Burning Board