Problem on Query MYSQL using MYSQL UDF

[michaelslamet]

[sOLVED]

I have 2 tables on mysql database:

table "customer" has field custID, name

table "customer-relation" has field custID, name, relation

this is the data for each table:

table "customer":

custID name

0001 mike

0002 sharon

table "customer-relation

custID name relation

0001 wife-of-mike wife

0002 husband-of-sharon husband

I connect to the mysql database:

$SQLInstance = _MySQLConnect($UserName,$Password,$Database,$MySQLServerName)

$SQLCode = "SELECT * FROM customer Where CustID = '0001"

$TableContents = _Query ($SQLInstance, $SQLCode)

With $TableContents

While Not .EOF

$cust_name = .Fields ("name").value

.movenext

WEnd

EndWith

Result from above code is $cust_name = "mike"

Based on table "cutomer-relation" I would like to display

the $relation of $cust_name" is $name

which is: the wife of mike is wife-of-mike

I try to query within while ... wend above as below:

$SQLCode = "SELECT * FROM customer Where CustID = '0001"

$TableContents = _Query ($SQLInstance, $SQLCode)

With $TableContents

While Not .EOF

$cust_name = .Fields ("name").value

.movenext

$SQLCode2 = "SELECT * FROM customer-relation Where CustID = '0001"

$TableContents2 = _Query ($SQLInstance, $SQLCode2)

With $TableContents2

...

...

WEnd

WEnd

EndWith

But produce this error and quit: Nested "With" statements are not allowed

Please help how to get the value of the field from other table based on the field custID ?

Hope somebody here understand what I mean especially with my poor english :-P

Many thanks!!

Michael.

Edited June 2, 2010 by michaelslamet

[DW1]

I know that I am kind of side stepping the issue you are having, but you could just use an inner join to make this a single query.

[michaelslamet]

Thanks, danwilli, for your clue and prompt reply, will look at it.

How can I get a field's value from other table with autoit?

Let say I want to get value of field "name" from table "customer-relation"

.Fields ("customer-relation.name").value

doesn't work

Any help would be highly appreciated.

Thanks!

[PsaltyDS]

The inner join suggested by danwilli will make all the columns in the other database available in your result. This is untested, but should be close:

$SQLCode = "SELECT * " & _
        "FROM customer INNER JOIN customer-relation " & _
        "ON customer.custID=customer-relation.custID "

$TableContents = _Query($SQLInstance, $SQLCode)
With $TableContents
    While Not .EOF
        $cust_name = .Fields("customer.name" ).value
        $relation_name = .Fields("customer-relation.name").value
        $relation = .Fields("").value
        MsgBox(64, "Relation", "The " & $relation & " of " & $cust_name & " is " & $relation_name & ".")
        .movenext
    WEnd
EndWith

[jchd]

Just curious: aren't NATURAL JOINs available in MySQL?

FROM customer INNER JOIN customer-relation ON customer.custID=customer-relation.custID

[PsaltyDS]

Just curious: aren't NATURAL JOINs available in MySQL?

FROM customer INNER JOIN customer-relation ON customer.custID=customer-relation.custID

I posted an INNER JOIN example already, wouldn't yours be more like this?

SELECT *
     FROM customer NATURAL JOIN customer-relation 
     ON custID

Edit: SQLite didn't like that syntax. Should have been just:

SELECT * FROM customer NATURAL JOIN customer-relation

Edited May 31, 2010 by PsaltyDS

[michaelslamet]

PsaltyDS and jchd: many thanks for your help.

But .Fields("customer-relation.name").value give this error:

(77) : ==> Error in expression.:

msgbox(0,"",.Fields ("customer-relation.name").value)

msgbox(0,"",.Fields ("customer-relation.name")^ ERROR

Please advice.

Many thanks again!!

Best regards,

Michael.

I posted an INNER JOIN example already, wouldn't yours be more like this?

SELECT *
     FROM customer NATURAL JOIN customer-relation 
     ON custID

[PsaltyDS]

By the way... are you sure you can have a hyphenated table name without quoting it? Maybe that second table should be customer_relation.

Anyway, I don't have MySQL, so SQLite will have to do for demo:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $sQuery, $hQuery, $aRow, $sMsg

_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

_SQLite_Open() ; open :memory: Database

_SQLite_Exec(-1, "CREATE TABLE customer(custID,name);")
_SQLite_Exec(-1, "INSERT INTO customer(custID,name) VALUES ('0001','Mike');")
_SQLite_Exec(-1, "INSERT INTO customer(custID,name) VALUES ('0002','Sharon');")

_SQLite_Exec(-1, "CREATE TABLE customer_relation(custID,name,relation);")
_SQLite_Exec(-1, "INSERT INTO customer_relation(custID,name,relation) VALUES ('0001','Wife-of-Mike','Wife');")
_SQLite_Exec(-1, "INSERT INTO customer_relation(custID,name,relation) VALUES ('0002','Husband-of-Sharon','Husband');")

$sQuery = "SELECT customer.custID,customer.name,customer_relation.name,customer_relation.relation FROM customer INNER JOIN customer_relation ON customer.custID=customer_relation.custID;"
_SQLite_Query(-1, $sQuery, $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    _ArrayDisplay($aRow, "$aRow")
    MsgBox(64, "CustID: " & $aRow[0], "The " & $aRow[3] & " of " & $aRow[1] & " is " & $aRow[2] & ".")
WEnd
_SQLite_Exec(-1, "DROP TABLE customer;")
_SQLite_Exec(-1, "DROP TABLE customer_relation;")
_SQLite_Close()
_SQLite_Shutdown()

Here's the same demo using NATURAL JOIN, but note it doesn't work with the confusion in column naming ("name" in both tables with different values), so I renamed the realation_name field:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $sQuery, $hQuery, $aRow, $sMsg

_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

_SQLite_Open() ; open :memory: Database

_SQLite_Exec(-1, "CREATE TABLE customer(custID,name);")
_SQLite_Exec(-1, "INSERT INTO customer(custID,name) VALUES ('0001','Mike');")
_SQLite_Exec(-1, "INSERT INTO customer(custID,name) VALUES ('0002','Sharon');")

_SQLite_Exec(-1, "CREATE TABLE customer_relation(custID,relation_name,relation);")
_SQLite_Exec(-1, "INSERT INTO customer_relation(custID,relation_name,relation) VALUES ('0001','Wife-of-Mike','Wife');")
_SQLite_Exec(-1, "INSERT INTO customer_relation(custID,relation_name,relation) VALUES ('0002','Husband-of-Sharon','Husband');")

$sQuery = "SELECT * FROM customer NATURAL JOIN customer_relation;"
_SQLite_Query(-1, $sQuery, $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    _ArrayDisplay($aRow, "$aRow")
    MsgBox(64, "CustID: " & $aRow[0], "The " & $aRow[3] & " of " & $aRow[1] & " is " & $aRow[2] & ".")
WEnd
_SQLite_Exec(-1, "DROP TABLE customer;")
_SQLite_Exec(-1, "DROP TABLE customer_relation;")
_SQLite_Close()
_SQLite_Shutdown()

Edited May 31, 2010 by PsaltyDS

[jchd]

Dear Penguin,

That's exactly what I was going to question!

I'd rather use msgbox(0,"",.Fields ("[customer-relation].name").value) or msgbox(0,"",.Fields ('"customer-relation".name').value) -- I don't know if MySQL accepts [name] or "name" for table- or column names.

[michaelslamet]

Both

.Fields ("[customer-relation].name").value

and

.Fields ("customer-relation.name").value

give this error: Error in expression.

I think my main problem is that I dont know the correct syntax to get the table.field value in MYSQL using AutoIT

the hypen in table name (customer-relation) should be not the problem since the original code has no hypen on the table name.

Really appriciate what you guys have done to help me solve this problem.

Many thanks!

Michael.

[jchd]

Beyond the exact issue exposed, you shouldn't use such denormalized redondant tables to handle his kind of relation.

Take a look a nested trees and how to implement them in SQL. Nested trees are way more efficient at querying relations than back to back tables. Their drawback is that insertion and deletion can be slow, but it's generaly anticipated that relationships such as husband, wife, son, mother, ... don't get inserted by the yard for a given set of slowly growing individuals.

If your real-life application can't support slow inserts or deletes, then at the very least, make use of a foreign key to refer to the main table.

Regarding MySQL, I can't answer much as I don't have a copy installed and, contrary to SQLite, this is the kind of thing you don't setup with 3 lines of code.

[michaelslamet]

Finally, it's solved

Many thanks to danwilli, PsaltyDS and jchd who give me clue about joining table.

Instead of using

.Fields ("FieldName").value

to get the value of the field

We can use .Fields (ColNumber).value

This solve the thread.

Thanks again!!

[jchd]

Necroposting over 9-year bones...