[MYSQL]{FUNCTIONS]
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 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[FUNCTIONs IN MYSQL]
Help needed. To have the possibility to make a nodeview in visual basic, I have made a child-parent relation in one of my tables. Table fields: TTNodeId (autonumber, indicating a node) NodeParent (NodeNumber of the parent) NodeLevel (Level of the node) NodeName (Name of the node) To make it readable for the users, not the nodeid is printed but the nodename as a full name (example 2143.1.20.100) this is a node on level 4 with a nodename '100' and a parent with nodename '20' The visualization is done with visual basic, but takes a lot of time. I have seen that it should be possible to make a function in MySql that can do this operation. Is such a function already available or how do I start -- Peter Bruggink Manager mechanical Design +31 76 5792732 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect via VB
Curlys wrote:
hi ,
mysql databse server is in Red Hat Linux 8 box. I have an application
which is developed from Visual Basic . So that application included a
reqiurement to connect mysql data base.
Can somebody help me how to connect mysql database via VB ?
thanx in advance
curlys
First of all you will need a ODCB driver (you can download this from
mysql.com. Then you will need to write some functions. I have attached
a vb module including the functions I use.
--
Peter Bruggink
Manager mechanical Design
+31 76 5792732
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
*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.
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
Persistable = 0 'NotPersistable
DataBindingBehavior = 0 'vbNone
DataSourceBehavior = 0 'vbNone
MTSTransactionMode = 0 'NotAnMTSObject
END
Attribute VB_Name = swMyDBConnection
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Attribute VB_Ext_KEY = SavedWithClassBuilder6 ,Yes
Attribute VB_Ext_KEY = Top_Level ,Yes
Option Explicit
Private strServer As String
Private strDatabase As String
Private strDBUserId As String
Private strDBPassword As String
Private WithEvents CurConnection As ADODB.Connection
Attribute CurConnection.VB_VarHelpID = -1
Private bConnected As Boolean
Private bExecuteReady As Boolean
Private bCommitReady As Boolean
Private bRollbackReady As Boolean
Public Property Get Connected() As Boolean
Connected = bConnected
End Property
Public Property Get ConnectionString() As String
' Provide proper connection string for current database settings.
' Return when no data set yet.
Dim strOut As String
strOut = DRIVER={ objProperties.MYSPINSQLDRIVER };
strOut = strOut SERVER= strServer ;
strOut = strOut DATABASE= strDatabase ;
strOut = strOut UID= strDBUserId ;
strOut = strOut PWD= strDBPassword ;
strOut = strOut OPTION=35
ConnectionString = strOut
End Property
Public Function MakeConnection() As Boolean
' Make connection, when not made yet. For access, test if the given database
' can be found. If not, open dialog to set database data.
Dim file As String
MakeConnection = False
If Not bConnected Then
On Error GoTo Error_Conn
Set CurConnection = New ADODB.Connection
CurConnection.CursorLocation = adUseServer
CurConnection.ConnectionString = ConnectionString
CurConnection.Open'Open de verbinding
bConnected = True
End If
MakeConnection = bConnected
Exit Function
Error_Conn:
Debug.Print Err.Description
End Function
Public Function Execute(sqlstr As String, Errstr As String, _
Optional bErr As Boolean = True) As Recordset
' Execute the given sqlstring, and return the recordset.
' Wait until the command has been executed.
bExecuteReady = False
Errstr =
Debug.Print sqlstr
On Error GoTo err_execute
Set Execute = CurConnection.Execute(sqlstr)
Do While Not bExecuteReady
DoEvents
Loop
Exit Function
err_execute:
If Err.Number = H80004005 Then
If Not bErr Then Exit Function
Errstr = Item with these data already in database! vbCrLf _
Please change data before trying again
Else
Errstr = Err.Description
Debug.Print Errstr
End If
End Function
Public Function ExecuteAff(sqlstr As String, Errstr As String, _
Optional bErr As Boolean = True) As Long
' Execute the given sqlstring, and return the number of affected records.
' This function should only be used for sql-commands that do not return
' a recordset. bErr allows surpression of the duplicate error message.
' Wait until the command has been executed.
Dim nraff As Long
bExecuteReady = False
Errstr =
Debug.Print sqlstr
On Error GoTo err_execute
CurConnection.Execute sqlstr, nraff
Do While Not bExecuteReady
DoEvents
Loop
ExecuteAff = nraff
Exit Function
err_execute:
If Err.Number = H80004005 Then
If Not bErr Then Exit Function
Errstr = Item with these data already in database! vbCrLf _
Please change data before trying again
Else
Errstr = Err.Description
End If
End Function
Public Sub OpenStatic(aRset As ADODB.Recordset, sqlstr As String)
' Open a static recordset with the given sqlstring
aRset.Open sqlstr, CurConnection, adOpenStatic
End Sub
Public Sub OpenDynamic(aRset As ADODB.Recordset, sqlstr As String)
' Open
Re: [CONNECTION PROBLEMS]
Victor Pendleton wrote: What error messages are you receiving? -Original Message- From: Peter Bruggink To: [EMAIL PROTECTED] Sent: 7/16/04 3:19 AM Subject: [CONNECTION PROBLEMS] To have a better performence I have installed a new server running solaris8 and mysql version 3.23.53. The old server is running solaris 7 and mysql 3.23.48-max. I also transferred the database to the new server and tried to connect the database with mysql front. Everything worked fine. Then I tried to connect with a application written in VB. It now seems that I can send a select string and look at te results, but cannot add or change anything in the tables. The first error appears when a begin transaction is started. I have already solved te problem by installing the mysql 4.0.20 max version. The difference lies in the use of the max version -- Peter Bruggink Manager mechanical Design +31 76 5792732 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[CONNECTION PROBLEMS]
To have a better performence I have installed a new server running solaris8 and mysql version 3.23.53. The old server is running solaris 7 and mysql 3.23.48-max. I also transferred the database to the new server and tried to connect the database with mysql front. Everything worked fine. Then I tried to connect with a application written in VB. It now seems that I can send a select string and look at te results, but cannot add or change anything in the tables. The first error appears when a begin transaction is started. -- Peter Bruggink Manager mechanical Design +31 76 5792732 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
