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 [
