List:General Discussion« Previous MessageNext Message »
From:Peter Bruggink Date:July 27 2004 11:52am
Subject:[MYSQL]{FUNCTIONS]
View as plain text  
Hi,

I have designed a application (written in visual basic) to visualize 
projects in a treeview and to attach all kinds of information to those 
treenodes (for example hours used, budget avail, pictures of 
manufactoring parts/ bought-out parts etc).
After using microsoft access for a short time, I have switched to mysql. 
After rewriting al the querys, this gave already a mutch better 
performance. To have one common language between designer, manufactoring 
users and financial users, I have intoduced the tooltree code as the 
position in the treeview. for example, we have a project called '2143' 
(this indicates the 43st project for customer with ID=21. In a project 
we can have multiple lines. For line 31 the tooltreecode will be 
2143.31. In line 31 we can have multiple zones. For zone 020 the 
tooltreecode then will be 2143.31.020. In total we can go up to 8 
levels. The tooltreecode a presented in the treeview is build out of 
seperate strings. Since this can take a lot of time (average number of 
nodes in a project = 15000), the tooltree is opened upto level 2 when 
starting. Then by clicking a node the next level is opened and the 
string are being build.
The function I use in visual basic looks like:

> Public Function FullNodeName(anid As Long) As String
>    Dim aRset As ADODB.Recordset
>    Dim ParentId As Long
>    Dim newname As String
>    
>    ParentId = 0
>    cond = "SELECT NodeParent, NodeName FROM TTNode WHERE TTNodeId=" & 
> CStr(anid)
>    Set aRset = objDBConnection.Execute(cond, ErrStr)
>    With aRset
>       .MoveFirst
>       If Not .EOF Then
>          ParentId = .Fields(0)
>          newname = .Fields(1)
>       End If
>       .Close
>    End With
>    Set aRset = Nothing
>    If newname <> "" And ParentId <> 0 Then
>       FullNodeName = FullNodeName(ParentId) & "." & newname
>    Else
>       FullNodeName = newname
>    End If
> End Function


I'am triing to speed up the access time even more and was thinking that 
it should be possible to have the tooltree string returned by the mysql 
server as part of the resultset. I then have to make a new user function 
in mysql. I was hooping that such a function already excists.
Can anybody help me.
-- 

Peter Bruggink

Manager mechanical Design
+31 76 5792732
p.bruggink@stripped <mailto:p.bruggink@stripped>

*Steelweld BV*
Terheijdenseweg 169
The Netherlands

www.steelweld.com <http://www.steelweld.com/>

------------------------------------------------------------------------

*DISCLAIMER* The information transmitted is confidential and may be 
legally privileged. It is intended solely for the use of the individual 
or entity to whom it is addressed. If you received this in error, please 
contact the sender and delete the material from any computer.

This mail has been checked for all known viruses by McAfee Virusscan.

Thread
[MYSQL]{FUNCTIONS]Peter Bruggink27 Jul