Sign in to follow this  
Followers 0
michaelslamet

Problem on Query MYSQL using MYSQL UDF

12 posts in this topic

#1 ·  Posted (edited)

[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!! :mellow:

Michael.

Edited by michaelslamet

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

:mellow:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

:mellow:

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

SELECT * FROM customer NATURAL JOIN customer-relation
Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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!! :P

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

:mellow:

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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()

:mellow:

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()

:P

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Finally, it's solved :mellow:

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!!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0