Hello All,
Suggestions Feedback Requested:
I'm looking for a solution that will not lock a table, yet allow me to
retrieve the:
field name, field type as integer, field size, and other attributes
of each column from a table.
The Issue:
When requesting the Table Schema from mySQL I have found an error that
prevents me from deleting the table. This error was found while testing an
interface DLL to mySQL. The test creates a dummy table, populates it with
data, runs various counts and sums, updates data, and deletes the data.
An important part of the test is to be able to retrieve the table's schema.
I can't explain why without releasing proprietary corporate information, all
I can say is that it's needed. What I can tell you is that when I retrieve
it with a mixed case table name, it prevents me from deleting the table.
The Cause:
VB and VBS programmers usually retrieve table schema through the
ADODB.Connection objects, by calling OpenSchema. I have found, though,
that if OpenSchema is called with a mixed case table name, ie .. JunkA ..
this will lock the table and prevent it from being deleted.
I have also found that mySQL has the flag, "lower_case_table_names". This
flag is set to 1. I have not tested either work around below with this flag
set to 0 or any other value at this time.
Solutions found so far:
I have found two work arounds for this.
1) The first is to force all table names to lower case.
2) The second work around is to use DESCRIBE [table] or SHOW COLUMNS FROM
[table].
Option two requires more programming to accommodate the translation from
string type names to integer type names, as used in ADOVBS. I am hoping,
however, that I have missed something in the mySQL documentation that
actually provides what I'm looking for.
Again:
I'm looking for a solution that will not lock a table, yet allow me to
retrieve the:
field name, field type as integer, field size, and other attributes
of each column from a table.
Code samples follow:
ENetArch
Code samples:
===========================================================
EXE - 1
Option Explicit
Private cn As Object
Private objStore As Object
Private Const szODBC = "UID=sa;PWD=sa;DSN=mySQL;"
Private szTableName As String
Sub Main()
Dim x
Set cn = CreateObject("adodb.connection")
cn.open (szODBC)
Set objStore = CreateObject("navisstardet_mysql.store_mysql")
Set objStore.Connect = cn
szTableName = "atest_A0"
x = objStore.CreateTable(szTableName, Nothing)
x = objStore.getStructure_SCF(szTableName)
x = objStore.DropTable(szTableName)
x = objStore.CreateTable(szTableName, Nothing)
x = objStore.getStructure_OS(szTableName)
x = objStore.DropTable(szTableName)
Set objStore.Connect = cn
End Sub
===========================================================
DLL - Store_mySQL
Option Explicit
' Include ADOVBS
Private cn As Object
' ===========================================
Private Sub Class_Initialize()
Set cn = Nothing
End Sub
' ===========================================
Public Property Set connect(ByVal objCn As Object)
Set cn = objCn
End Property
' ===========================================
Public Function createTable _
(ByVal szTableName As String, _
ByVal objStr As Object)
Dim szSQL As String
szSQL = _
" CREATE TABLE " & szTableName & _
" ( " & _
" ID INTEGER AUTO_INCREMENT, " & _
" PRIMARY KEY ( ID ) , " & _
" Date_Created DATETIME , " & _
" Closed BIT , " & _
" Date_Closed DATETIME , " & _
" SortBy VARCHAR (40), " & _
" CompanyName VARCHAR (40), " & _
" ContactName VARCHAR (40), " & _
" Street1 VARCHAR (40), " & _
" Street2 VARCHAR (40), " & _
" City VARCHAR (20), " & _
" State VARCHAR (2), " & _
" Zip VARCHAR (10), " & _
" Country VARCHAR (20), " & _
" Tele VARCHAR (12), " & _
" Email VARCHAR (250), " & _
" WebSite VARCHAR (250) " & _
" ) "
cn.execute (szSQL)
End Function
' ===========================================
Public Function dropTable(ByVal szTableName As String)
Dim szSQL As String
szSQL = _
" DROP TABLE " & szTableName
cn.execute (szSQL)
End Function
' ===========================================
Function getStructure_OS(ByVal szTableName As String)
' Method 1
' Two methods of retrieving the tables schema are presented here.
' If you choose to use Method 1, make sure that the tables name is
' all lower case. Retrieving a table's schema, where the table name
' is mixed case presently (2002-09-29) causes a mySQL table to
' lock the table, and it's immediate deletion.
' This can be annoying during testing - of install and uninstall
processes.
' Work Around 1 is to use LCASE () to change the table case before
' retrieving the schema.
' Work Around 2 is to issue a " DESCRIBE [table] " or
' " SHOW COLUMN FROM [table] " SQL command.
' =================================================
' Using Work Around 1
szTableName = LCase(szTableName)
' Rem the above line out to see the error!
Dim rs As Object
Set rs = cn.OpenSchema _
(adSchemaColumns, Array(Empty, Empty, szTableName, Empty))
Dim nFieldSize As Long
Debug.Print
Dim t As Long
For t = 0 To rs.RecordCount - 1
nFieldSize = 0
If (Not IsNull(rs("CHARACTER_MAXIMUM_LENGTH"))) Then _
nFieldSize = rs("CHARACTER_MAXIMUM_LENGTH")
Debug.Print rs("COLUMN_NAME"), rs("DATA_TYPE"), nFieldSize
rs.MoveNext
Next
Debug.Print
rs.Close
End Function
' ===========================================
Function getStructure_SCF(ByVal szTableName As String)
' Method 2
' Two methods of retrieving the tables schema are presented here.
' If you choose method two, you will need to create a procedure that
' converts the text type into an integer type ... ie .. where szType =
"INT"
' nType = 3. This is needed if you are matching field types based on
' the Types listed in ADOVBS.
' Work Around 1 is to use LCASE () to change the table case before
' retrieving the schema.
' Work Around 2 is to issue a " DESCRIBE [table] " or
' " SHOW COLUMN FROM [table] " SQL command.
' =================================================
' Using Work Around 2
Dim szSQL As String
szSQL = _
" SHOW COLUMNS " & _
" FROM " & szTableName
Dim rs As Object
Set rs = cn.execute(szSQL)
Dim nFieldSize As Long
Dim szType As String
Debug.Print
Dim p1 As Long
Dim p2 As Long
Dim nType As Long
Dim t As Long
While (Not (rs.BOF Or rs.EOF))
nFieldSize = 0
szType = rs("Type")
nFieldSize = 0
p1 = InStr(szType, "(")
If (p1 > 0) Then
p2 = InStr(szType, ")")
nFieldSize = Int("0" & Mid(szType, p1 + 1, p2 - p1 - 1))
szType = Left(szType, p1 - 1)
End If
nType = getType(szType)
Debug.Print rs("Field"), nType, nFieldSize
rs.MoveNext
Wend
Debug.Print
rs.Close
End Function
' ===========================================
Function getType(ByVal szType) As Long
szType = LCase(szType)
Select Case (szType)
Case "int": getType = 3
Case "varchar": getType = 129
Case "datetime": getType = 135
Case "tinyint": getType = 16
Case Else: getType = 0
End Select
End Function
===========================================================
' Keywords:
' myODBC, mySQL, Visual Basic, VB, ADODB.RECORDSET
' ADODB.CONNECTION, ADO, OpenSchema, DESCRIBE [table],
' SHOW COLUMNS FROM [table], ERRCODE: 13, ADOVBS
' lower case table names, lower_case_table_names
' Error:
' Microsoft OLE DB Provider for ODBC Drivers -2147467259
' [MySQL] [ODBC 3.51 Driver] [mysqld-3.23.51-nt]
' Error on delete of '.\ladder\junka.MYI' (Errcode: 13)
' Environment:
' WkStn - Windows 2000 WkStn, 256 meg, AMD K6-2 500 mhz, build 2195, SP1
' MDAC 2.7
' myODBC 3.51 - WinX
'
' Server - Windows NT 4.0 Server, 64 meg, Pentium II 400 mhz
' mySQL - 3.23.51 - NT
' lower_case_table_names = 1
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
