none
Подскажите как можно экспортировать данные из Microsoft SQL Server RRS feed

  • Вопрос

  • Подскажите как можно экспортировать данные из Microsoft SQL Server так же как это делается в phpMyAdmin для MySQL в виде набора SQL команд? Или как можно конвертировать базу из MSSQL в MySQL?
    6 апреля 2012 г. 16:10

Ответы

  • Спасибо. Правда платные скрипты демо которых я попробовал не все данные копируют...

    Порылся тут и нашел скрипт на VB для MS Office который через MS BC 2005 и MySQL ODBC 3.51 мне все данные перекинул напрямую в MySQL. Правда его допиливать пришлось, но вроде нормально все сработало, проект потом на MySQL нормально заработал и ошибок не выдавал.

    Ссылку на скрипт или сам скрипт могу позже с работы выложить ;)

    • Помечено в качестве ответа Nail Mukhametshin 7 апреля 2012 г. 17:16
    7 апреля 2012 г. 17:15
  • Я использовал этот макрос для Office. Ему необходим компонент обратной совместимости SQL2005BC и драйвер MySQL ODBC? если хотите напрямую в MySQL конвертировать.

    Код принадлежит его автору, я его немного изменил что бы не выдавал ошибок о неправильном типе переменных.

    ' this program converts Microsoft SQL Server databases to MySQL databases
    
    ' (c) 2001-2005 Michael Kofler
    '     http://www.kofler.cc/mysql
    '     mssql2mysql-at-kofler.cc
    
    ' LICENSE: GPL (Gnu Public License)
    ' VERSION: 0.12 (Mar 31st 2005)
    '
    ' HISTORY:
    ' 0.01 (Jan. 18 2001): initial version
    ' 0.02 (June 23 2001): better handling of decimal numbers
    '   by <dave.whitla-at-ocean.net.au>
    ' 0.03 (August 7 2001): compatibility with Office 97
    '   by <DaveMeaker-at-angelshade.com>
    '   (uncomment Replace function at the end of the script!)
    ' 0.04 (September 30 2001): slightly faster (ideas by
    '    janivar-at-acos.no)
    ' 0.05 (April 8 2002): compatible with MyODBC 3.51
    '    (set variable MyODBCVersion accordingly!)
    '    thanks to Silvio Iaccarino
    ' 0.05a (October 17 2002): support for BIGINT
    '    thanks to Ugo Gioia
    ' 0.06 (December 18 2002): support for adBinary (thanks to Roberto Alicata)
    '    changed handling of TIMESTAMP (Michael Kofler):
    '      TIMESTAMP (MSSQL) --> TINYBLOB (MySQL)
    ' 0.07 (Jan. 27th 2003): support no-standard ports for MySQL
    ' 0.08 (Apr. 30th 2003): Boolean now converts to 1/0 instead of -1/0
    '      for True/False (Michael Kofler)
    ' 0.09 (Aug. 19th 2003): better GUID conversion (Hermann Wiesner)
    ' 0.10 (Nov. 12th 2003): better handling of table and field names
    '      in function MySQLName (Carlo)
    ' 0.11 (Mar. 2nd 2005): needs MySQL 4.1 or higher!
    '      new functions: - supports myisam and innodb tables
    '                     - per default all text columns are now created with character
    '                       set utf8and collation utf8_general_ci; you may choose another
    '                       charset + collation (constants CHARSET and COLLATION)
    ' 0.12 (Mar. 31st 2005): ignores hypothetical indexes (thanks to Brian K. Andersen)
    
    ' USEAGE:
    ' 1) copy this code into a new VBA 6 module
    '    (i.e. start Excel 2000 or Word 2000 or another
    '     program with VBA editor, hit Alt+F11, execute
    '     Insert|Module, insert code)
    '    OR copy code into an empty form of a new VB6 project
    '
    ' 2) change the constants at the beginning of the code,
    ' 3) hit F5 and execute Main()
    '    the program now connects to Microsoft SQL Server
    '    and converts the database; the resulting SQL commands
    '    are either saved in an ASCII file or executed immediately
    
    ' FUNCTION:
    ' converts both schema (tables and indices) and
    ' data (numbers, strings, dates etc.)
    ' handles table and column names which are not legal in MySQL (see MySQLName())
    
    ' LIMITATIONS:
    ' no foreign keys
    ' no SPs, no triggers (MSSQL syntax incompatible to MySQL)
    ' no views
    ' no user defined data types (not yet supported by MySQL)
    ' AUTO_INCREMENTs: MySQL does not support tables with more
    '   than one AUTO_INCREMENT column; the AUTO_INCREMENT column
    '   must also be a key column; the converter does not check this,
    '   so use MSSQL to add an index to the AUTO_INCREMENT column
    '   before starting the conversion
    ' no privileges/access infos (the idea of logins/users in M$ SQL Server
    '     is incompatible with user/group/database/table/column
    '     privileges of MySQL)
    ' cannot handle ADO type adFileTime yet
    ' GUIDs not tested
    ' fairly slow and no visible feedback during conversion process
    '   for example, it takes 80 seconds to convert Northwind (2.8 MB data)
    '   with M$SQL running on PII 350 (CPU=0) and this script running in
    '   Excel 2000 on PII 400 (CPU=100); unfortunately, compiling the program
    '   with VB6 does not make it any faster
    '   tip: test with MAX_RECORDS = 10 first to see if it works for you at all
    
    ' INTERNALS:
    ' method:   read database schema using DMO
    '           read data using a ADO recordset
    
    ' NECESSARY LIBRARIES:
    '   ADODB          (tested with 2.8, should also run with all versions >=2.1)
    '   Connector/ODBC (testet with 3.51.11, should also run with older versions;
    '                   if you use MySQL server >= 4.1,
    '                   you MUST use Connector/ODBC >= 3.51.10!!!)
    '   SQLDMO (tested with the version provided by M$ SQL Server 2000)
    '   SCRIPTING
    
    Option Explicit
    Option Compare Text
    
    ' -------------- change these constants before use!
    
                                      'M$ SQL Server
    Const MSSQL_SECURE_LOGIN = True   'login type (True for NT security)
    Const MSSQL_LOGIN_NAME = "sa"       'login name (for NT security use "" here)
    Const MSSQL_PASSWORD = "1"         'password   (for NT security use "" here)
    Const MSSQL_HOST = "(local)"      'hostname of M$ SQL Server; if localhost: use "(local)"
    Const MSSQL_DB_NAME = "TESTRAIL" 'database name
    
    Const OUTPUT_TO_FILE = 0          '1 --> write file (latin1 encoding);
                                      '0 --> connect to MySQL, execute SQL commands directly
                                      
                                      'output file (only needed if OUTPUT_TO_FILE=1)
    Const OUTPUT_FILENAME = "c:\tmp\export.sql"
    
                                      'connect to MySQL (only needed if OUTPUT_TO_FILE=0)
    Const MYSQL_USER_NAME = "roots"    'login name
    Const MYSQL_PASSWORD = "passwd"   'password
    Const MYSQL_HOST = "dns-nsme" 'if localhost: use "localhost"
    Const MYSQL_PORT = 3306           'change if you use another port
    Const MyODBCVersion = "MySQL ODBC 3.51 Driver"
                                      'for MyODBC 2.51.* use "MySQL"
    
    Const NEW_DB_NAME = ""            'name of new MySQL database ("" if same as M$SQL db name)
                                      'conversion options
    Const DROP_DATABASE = True        'begin with DROP dbname?
    Const MAX_RECORDS = 0             'max. nr of records per table (0 for all records, n for testing purposes)
    Const VARCHAR_MAX = 4096           'max. nr of characters in VARCHAR
                                      '255 for MySQL <=5.0.2
                                      'ca. 32000 for MySQL >= 5.0.3 (depends on char. set, max. 65535 bytes)
    Const TABLE_ENGINE = "InnoDB"     'MyISAM or InnoDB
    Const CHARSET = "utf8"            'character set for all text columns
    Const COLLATION = "utf8_general_ci"              'collation; leave empty if you want to use the default collation for
                                      'the specified character set
    
    ' ----------------------------- don't change below here (unless you know what you are doing)
    
    Const SQLDMOIndex_DRIPrimaryKey = 2048
    Const SQLDMOIndex_Unique = 2
    Const adEmpty = 0
    Const adTinyInt = 16
    Const adSmallInt = 2
    Const adInteger = 3
    Const adBigInt = 20
    Const adUnsignedTinyInt = 17
    Const adUnsignedSmallInt = 18
    Const adUnsignedInt = 19
    Const adUnsignedBigInt = 21
    Const adSingle = 4
    Const adDouble = 5
    Const adCurrency = 6
    Const adDecimal = 14
    Const adNumeric = 131
    Const adBoolean = 11
    Const adError = 10
    Const adUserDefined = 132
    Const adVariant = 12
    Const adIDispatch = 9
    Const adIUnknown = 13
    Const adGUID = 72
    Const adDate = 7
    Const adDBDate = 133
    Const adDBTime = 134
    Const adDBTimeStamp = 135
    Const adBSTR = 8
    Const adChar = 129
    Const adVarChar = 200
    Const adLongVarChar = 201
    Const adWChar = 130
    Const adVarWChar = 202
    Const adLongVarWChar = 203
    Const adBinary = 128
    Const adVarBinary = 204
    Const adLongVarBinary = 205
    Const adChapter = 136
    Const adFileTime = 64
    Const adPropVariant = 138
    Const adVarNumeric = 139
    Const adArray = &H2000
    
    Public dmoApplic 'As New SQLDMO.Application  'SQLDMO Application object
    Public dmoSrv    'As New SQLDMO.SQLServer    'SQLDMO Server object
    Public mssqlConn 'As New Connection          'ADO Connection to M$ SQL Server
    Public mysqlConn 'As New Connection          'ADO Connection to MySQL
    Public fso       'As Scripting.FileSystemObject
    Public fileout   'AS FSO.TextStream
    
    Public Sub Main()
      Set dmoApplic = CreateObject("SQLDMO.Application")
      Set dmoSrv = CreateObject("SQLDMO.SQLServer")
      Set mssqlConn = CreateObject("ADODB.Connection")
      Set mysqlConn = CreateObject("ADODB.Connection")
      Set fso = CreateObject("Scripting.FileSystemObject")
      ConnectToDatabases
      ConvertDatabase
      MsgBox "done"
    End Sub
    
    ' connect to M$ SQL Server and MySQL
    Private Sub ConnectToDatabases()
      dmoSrv.LoginTimeout = 10
      On Error Resume Next
      
      ' DMO connection to M$ SQL Server
      If MSSQL_SECURE_LOGIN Then
        dmoSrv.LoginSecure = True
        dmoSrv.Connect MSSQL_HOST
      Else
        dmoSrv.LoginSecure = False
        dmoSrv.Connect MSSQL_HOST, MSSQL_LOGIN_NAME, MSSQL_PASSWORD
      End If
      If Err Then
        MsgBox "Sorry, cannot connect to M$ SQL Server. " & _
          "Please edit the MSSQL constats at the beginning " & _
          "of the code." & vbCrLf & vbCrLf & Error
        End
      End If
      
      ' ADO connection to M$ SQL Server
      Dim tmpCStr$
      tmpCStr = _
        "Provider=SQLOLEDB;" & _
        "Data Source=" & MSSQL_HOST & ";" & _
        "Initial Catalog=" & MSSQL_DB_NAME & ";"
      If MSSQL_SECURE_LOGIN Then
        tmpCStr = tmpCStr & "Integrated Security=SSPI"
      Else
        tmpCStr = tmpCStr & _
          "User ID=" & MSSQL_LOGIN_NAME & ";" & _
          "Password=" & MSSQL_PASSWORD
      End If
      mssqlConn.ConnectionString = tmpCStr
      mssqlConn.Open
      If Err Then
        MsgBox "Sorry, cannot connect to M$ SQL Server. " & _
          "Please edit the MSSQL constats at the beginning " & _
          "of the code." & vbCrLf & vbCrLf & Error
        End
      End If
      
      ' ADO connection to MySQL or open output file
      If (OUTPUT_TO_FILE = 0) Then
        mysqlConn.ConnectionString = _
          "Provider=MSDASQL;" & _
          "Driver=" & MyODBCVersion & ";" & _
          "Server=" & MYSQL_HOST & ";" & _
          "UID=" & MYSQL_USER_NAME & ";" & _
          "PWD=" & MYSQL_PASSWORD & ";" & _
          "Port=" & MYSQL_PORT
        mysqlConn.Open
        mysqlConn.Execute "SET character_set_client='cp1251';"
        mysqlConn.Execute "SET character_set_results='cp1251';"
        mysqlConn.Execute "SET collation_connection='cp1251_general_ci';"
        If Err Then
          MsgBox "Sorry, cannot connect to MySQL. " & _
            "Please edit the MYSQL constats at the beginning " & _
            "of the code." & vbCrLf & vbCrLf & Error
          End
        End If
      Else
        Set fileout = fso.CreateTextFile(OUTPUT_FILENAME, Overwrite:=True)
      End If
    End Sub
    
    Private Sub ConvertDatabase()
      ' copy database schema
      Dim dmoDB 'As SQLDMO.Database
      Set dmoDB = dmoSrv.Databases(MSSQL_DB_NAME)
      DBDefinition dmoDB
      ' copy data
      CopyDB dmoDB
    End Sub
    
    ' build SQL code to define one column
    ' ColDefinition$(col As SQLDMO.Column)
    Function ColDefinition$(col)
      Dim cdef$
      cdef = MySQLName(col.Name) & " " & DataType(col)
      If col.Default <> "" Then
        cdef = cdef & " DEFAULT " & col.Default
      End If
      If col.AllowNulls Then
        cdef = cdef & " NULL"
      Else
        cdef = cdef & " NOT NULL"
      End If
      If col.Identity Then
        cdef = cdef & " AUTO_INCREMENT"
      End If
      ColDefinition = cdef
    End Function
    
    ' datatype transition M$ SQL Server --> MySQL
    ' DataType$(col As SQLDMO.Column)
    Function DataType$(col)
      Dim oldtype$, length&, precision&, scal&
      Dim newtype$
      
      oldtype = col.PhysicalDatatype
      length = col.length
      precision = col.NumericPrecision
      scal = col.NumericScale
      If LCase(oldtype) = "money" Then
        precision = 19
        scal = 4
      ElseIf LCase(oldtype) = "smallmoney" Then
        precision = 10
        scal = 4
      End If
      
      Select Case LCase(oldtype)
      
      ' integers
      Case "bit", "tinyint"
        newtype = "TINYINT"
      Case "smallint"
        newtype = "SMALLINT"
      Case "int"
        newtype = "INT"
      Case "bigint"
        newtype = "BIGINT"
      
      ' floating points
      Case "float"
        newtype = "DOUBLE"
      Case "real"
        newtype = "FLOAT"
      Case "decimal", "numeric", "money", "smallmoney"
        newtype = "DECIMAL(" & precision & ", " & scal & ")"
      
      ' strings
      Case "char", "nchar"
        If length < 255 Then
          newtype = "CHAR(" & length & ")"
        Else
          newtype = "TEXT"
        End If
      Case "varchar", "nvarchar"
        If length = 0 Then
          newtype = "LONGTEXT"
        ElseIf length < VARCHAR_MAX Then
          newtype = "VARCHAR(" & length & ")"
        Else
          newtype = "TEXT"
        End If
      Case "text", "ntext"
        newtype = "LONGTEXT"
        
      ' date/time
      Case "datetime", "smalldatetime"
        newtype = "DATETIME"
      Case "timestamp"
        newtype = "TINYBLOB"
        
      ' binary and other
      Case "uniqueidentifier"
        newtype = "TINYBLOB"
      Case "binary", "varbinary"
        newtype = "BLOB"
      Case "image"
        newtype = "LONGBLOB"
      
      Case Else
        ' unknown data type, not supported
        Stop
      End Select
      
      DataType = newtype
    End Function
    
    ' IndexDefinition$(tbl As SQLDMO.Table, idx As SQLDMO.Index)
    Function IndexDefinition$(tbl, idx)
      Dim i&
      Dim tmp$
      Dim col 'As SQLDMO.Column
      ' don't deal with system indices (used i.e. to ensure ref. integr.)
      If Left$(idx.Name, 1) = "_" Or Left$(idx.Name, 5) = "hind_" Then Exit Function
      ' index type (very incomplete !!!)
      If idx.Type And SQLDMOIndex_DRIPrimaryKey Then
        tmp = tmp & "PRIMARY KEY"
      ElseIf idx.Type And SQLDMOIndex_Unique Then
        tmp = tmp & "UNIQUE " & MySQLName(idx.Name)
      Else
        tmp = tmp & "INDEX " & MySQLName(idx.Name)
      End If
      ' index columns
      tmp = tmp & "("
      For i = 1 To idx.ListIndexedColumns.Count
        Set col = idx.ListIndexedColumns(i)
        tmp = tmp & MySQLName(col.Name)
        ' specify index length
        If Right$(DataType(col), 4) = "BLOB" Or Right$(DataType(col), 4) = "TEXT" Then
          tmp = tmp & "(" & IIf(col.length < 255, col.length, 255) & ")"
        End If
        ' seperate, if more than one index column
        If i < idx.ListIndexedColumns.Count Then tmp = tmp & ","
      Next
      tmp = tmp & ")"
      IndexDefinition = tmp
    End Function
    
    ' build SQL code to define one table
    ' TableDefinition$(tbl As SQLDMO.Table)
    Function TableDefinition$(tbl)
      Dim i&
      Dim tmp$, ixdef$
      ' table
      tmp = "CREATE TABLE " & _
            NewDBName(tbl.Parent) & "." & MySQLName(tbl.Name) & vbCrLf & "("
      For i = 1 To tbl.Columns.Count
        tmp = tmp & ColDefinition(tbl.Columns(i))
        If i < tbl.Columns.Count Then
          tmp = tmp & ", " & vbCrLf
        End If
      Next
      ' indices
      For i = 1 To tbl.Indexes.Count
        ixdef = IndexDefinition(tbl, tbl.Indexes(i))
        If ixdef <> "" Then
          tmp = tmp & ", " & vbCrLf & ixdef
        End If
      Next
      tmp = tmp & ")"
      ' engine
      tmp = tmp & " ENGINE " & TABLE_ENGINE
      ' characterset
      If CHARSET <> "" Then
        tmp = tmp & " CHARACTER SET " & CHARSET
      End If
      ' collation
      If COLLATION <> "" Then
        tmp = tmp & " COLLATE " & COLLATION
      End If
      TableDefinition = tmp
    End Function
    
    ' build SQL code to define database (all tables)
    ' DBDefinition(db As SQLDMO.Database)
    Sub DBDefinition(db)
      Dim i&
      Dim sql, dbname$
      dbname = NewDBName(db)
      If DROP_DATABASE Then
        sql = "DROP DATABASE IF EXISTS " & dbname
        ExecuteSQL sql
      End If
      sql = "CREATE DATABASE " & dbname & " CHARACTER SET " & CHARSET
      If COLLATION <> "" Then
        sql = sql & " COLLATE " & COLLATION
      End If
      ExecuteSQL sql
      For i = 1 To db.Tables.Count
        If Not db.Tables(i).SystemObject Then
          sql = TableDefinition(db.Tables(i))
          ExecuteSQL sql
        End If
      Next
    End Sub
    
    ' copy content of all M$ SQL Server tables to new MySQL database
    ' CopyDB(msdb As SQLDMO.Database)
    Sub CopyDB(msdb)
      Dim i&
      Dim tmp$
      ExecuteSQL "USE " & NewDBName(msdb)
      For i = 1 To msdb.Tables.Count
        If Not msdb.Tables(i).SystemObject Then
          CopyTable msdb.Tables(i)
        End If
      Next
    End Sub
    
    ' copy content of one table from M$ SQL Server to MySQL
    ' CopyTable(mstable As SQLDMO.Table)
    Sub CopyTable(mstable)
      Dim rec ' As Recordset
      Dim sqlInsert$, sqlValues$
      Dim i&, recordCounter&
      Set rec = CreateObject("ADODB.Recordset")
      rec.Open "SELECT * FROM [" & mstable.Name & "]", mssqlConn
      ' build beginning statement of SQL INSERT command
      ' for example: INSERT INTO tablename (column1, column2)
      sqlInsert = "INSERT INTO " & MySQLName(mstable.Name) & " ("
      For i = 0 To rec.Fields.Count - 1
        sqlInsert = sqlInsert & MySQLName(rec.Fields(i).Name)
        If i <> rec.Fields.Count - 1 Then
          sqlInsert = sqlInsert & ", "
        End If
      Next
      sqlInsert = sqlInsert & ") "
      ' for each recordset in M$SS table: build sql statement
      Do While Not rec.EOF
        sqlValues = ""
        For i = 0 To rec.Fields.Count - 1
          sqlValues = sqlValues & DataValue(rec.Fields(i))
          If i <> rec.Fields.Count - 1 Then
            sqlValues = sqlValues & ", "
          End If
        Next
        ExecuteSQL sqlInsert & " VALUES(" & sqlValues & ")"
        rec.MoveNext
        ' counter
        recordCounter = recordCounter + 1
        If MAX_RECORDS <> 0 Then
          If recordCounter >= MAX_RECORDS Then Exit Do
        End If
      Loop
    End Sub
    
    ' data transition M$ SQL Server --> MySQL
    ' DataValue$(fld As ADO.Field)
    Function DataValue$(fld)
      If IsNull(fld.Value) Then
        DataValue = "NULL"
      Else
    
        Select Case fld.Type
        
        ' integer numbers
        Case adBigInt, adInteger, adSmallInt, adTinyInt, adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt
          DataValue = fld.Value
        
        ' decimal numbers
        Case adCurrency, adDecimal, adDouble, adNumeric, adSingle, adVarNumeric
          DataValue = Str(fld.Value)
          If Not InStr(DataValue, ".") > 0 Then
            DataValue = DataValue & ".0"
          End If
          
        ' boolean
        Case adBoolean
          DataValue = IIf(fld.Value, 1, 0)
          
        ' date, time
        Case adDate, adDBDate, adDBTime
          DataValue = Format(fld.Value, "'yyyy-mm-dd Hh:Nn:Ss'")
        Case adDBTimeStamp
          DataValue = Format(fld.Value, "yyyymmddHhNnSs")
        Case adFileTime
          ' todo
          Beep
          Stop
          
        ' strings
        Case adBSTR, adChar, adLongVarChar, adVarChar, adLongVarWChar, adVarWChar, adWChar
          DataValue = "'" & Quote(fld.Value) & "'"
        
        ' binary and other
        Case adGUID
          DataValue = HexCodeGUID(fld.Value)
        Case adLongVarBinary, adVarBinary
          DataValue = HexCode(fld.Value)
        
        End Select
      End If
    End Function
    
    ' converts a Byte-array into a hex string
    ' HexCode$(bytedata() As Byte)
    Function HexCode(bytedata)
      Dim i&
      Dim tmp$
      tmp = ""
      For i = LBound(bytedata) To UBound(bytedata)
        If bytedata(i) <= 15 Then
          tmp = tmp + "0" + Hex(bytedata(i))
        Else
          tmp = tmp + Hex(bytedata(i))
        End If
      Next
      HexCode = "0x" + tmp
    End Function
    
    ' converts a String into a hex string
    ' HexCode$(bytedata() As Byte)
    Function HexCodeStr(bytedata)
      Dim i&, b&
      Dim tmp$
      tmp = ""
      For i = 1 To LenB(bytedata)
        b = AscB(MidB(bytedata, i, 1))
        If b <= 15 Then
          tmp = tmp + "0" + Hex(b)
        Else
          tmp = tmp + Hex(b)
        End If
      Next
      HexCodeStr = "0x" + tmp
    End Function
    
    ' returns name of new database
    ' NewDBName$(db As SQLDMO.Database)
    Function NewDBName$(db)
      If NEW_DB_NAME = "" Then
        NewDBName = db.Name
      Else
        NewDBName = NEW_DB_NAME
      End If
    End Function
    
    ' quote ' " and \; replace chr(0) by \0
    Function Quote(tmp)
      tmp = Replace(tmp, "\", "\\")
      tmp = Replace(tmp, """", "\""")
      tmp = Replace(tmp, "'", "\'")
      Quote = Replace(tmp, Chr(0), "\0")
    End Function
    
    ' to translate MSSQL names to legal MySQL names
    ' replace blank, -, ( and ) by '_'
    Function MySQLName(tmp)
      tmp = Replace(tmp, " ", "_")
      tmp = Replace(tmp, "-", "_")
      tmp = Replace(tmp, "(", "_")
      MySQLName = "`" & Replace(tmp, ")", "_") & "`"
    End Function
    
    ' either execute SQL command or write it into file
    Function ExecuteSQL(sql)
      If OUTPUT_TO_FILE Then
        fileout.WriteLine sql & ";"
        If Left$(sql, 6) <> "INSERT" Then
          fileout.WriteLine
        End If
      Else
        mysqlConn.Execute sql
      End If
    End Function
    
    ' this event procedures starts the converter if it is run as a VB6 programm
    Private Sub Form_Load()
      Main
      End
    End Sub
    
    ' converts a GUID string into a hex string without format
    Function HexCodeGUID(bytedata)
      Dim tmp$
      If Len(bytedata) = 38 Then
        tmp = Mid(bytedata, 2, 8) + Mid(bytedata, 11, 4) + Mid(bytedata, 16, 4) _
            + Mid(bytedata, 21, 4) + Mid(bytedata, 26, 12)
      Else
        tmp = "00"
      End If
      HexCodeGUID = "0x" + tmp
    End Function
    
    
    ' uncomment the following lines if you are using Office 97,
    ' which does not have the Replace function include; please
    ' note that this Replace function is much slower than the
    ' built-in version in Office 2000/VB6
    '
    ' to replace all occurrences of a string within a string
    ' Function Replace(tmp, fromStr, toStr)
    '     Dim leftOff&
    '     Dim pos&
    '     leftOff = 1
    '     Do While InStr$(leftOff, tmp, fromStr) > 0
    '         pos = InStr$(leftOff, tmp, fromStr)
    '         tmp = Left$(tmp, pos - 1) + toStr + Mid$(tmp, pos + Len(fromStr), Len(tmp))
    '         leftOff = pos + Len(fromStr) + 1
    '     Loop
    '     Replace = tmp
    ' End Function
    



Все ответы

  • Спасибо. Правда платные скрипты демо которых я попробовал не все данные копируют...

    Порылся тут и нашел скрипт на VB для MS Office который через MS BC 2005 и MySQL ODBC 3.51 мне все данные перекинул напрямую в MySQL. Правда его допиливать пришлось, но вроде нормально все сработало, проект потом на MySQL нормально заработал и ошибок не выдавал.

    Ссылку на скрипт или сам скрипт могу позже с работы выложить ;)

    • Помечено в качестве ответа Nail Mukhametshin 7 апреля 2012 г. 17:16
    7 апреля 2012 г. 17:15
  • http://msdn.microsoft.com/ru-ru/library/ms141209%28v=sql.105%29.aspx
    Перенос данных с помощью мастера импорта и экспорта SQL Server

    http://msdn.microsoft.com/ru-ru/library/ms162802%28v=sql.105%29
    Программа bcp используется для массового копирования данных между экземпляром Microsoft  SQL Server и файлом данных в пользовательском формате
  • Я использовал этот макрос для Office. Ему необходим компонент обратной совместимости SQL2005BC и драйвер MySQL ODBC? если хотите напрямую в MySQL конвертировать.

    Код принадлежит его автору, я его немного изменил что бы не выдавал ошибок о неправильном типе переменных.

    ' this program converts Microsoft SQL Server databases to MySQL databases
    
    ' (c) 2001-2005 Michael Kofler
    '     http://www.kofler.cc/mysql
    '     mssql2mysql-at-kofler.cc
    
    ' LICENSE: GPL (Gnu Public License)
    ' VERSION: 0.12 (Mar 31st 2005)
    '
    ' HISTORY:
    ' 0.01 (Jan. 18 2001): initial version
    ' 0.02 (June 23 2001): better handling of decimal numbers
    '   by <dave.whitla-at-ocean.net.au>
    ' 0.03 (August 7 2001): compatibility with Office 97
    '   by <DaveMeaker-at-angelshade.com>
    '   (uncomment Replace function at the end of the script!)
    ' 0.04 (September 30 2001): slightly faster (ideas by
    '    janivar-at-acos.no)
    ' 0.05 (April 8 2002): compatible with MyODBC 3.51
    '    (set variable MyODBCVersion accordingly!)
    '    thanks to Silvio Iaccarino
    ' 0.05a (October 17 2002): support for BIGINT
    '    thanks to Ugo Gioia
    ' 0.06 (December 18 2002): support for adBinary (thanks to Roberto Alicata)
    '    changed handling of TIMESTAMP (Michael Kofler):
    '      TIMESTAMP (MSSQL) --> TINYBLOB (MySQL)
    ' 0.07 (Jan. 27th 2003): support no-standard ports for MySQL
    ' 0.08 (Apr. 30th 2003): Boolean now converts to 1/0 instead of -1/0
    '      for True/False (Michael Kofler)
    ' 0.09 (Aug. 19th 2003): better GUID conversion (Hermann Wiesner)
    ' 0.10 (Nov. 12th 2003): better handling of table and field names
    '      in function MySQLName (Carlo)
    ' 0.11 (Mar. 2nd 2005): needs MySQL 4.1 or higher!
    '      new functions: - supports myisam and innodb tables
    '                     - per default all text columns are now created with character
    '                       set utf8and collation utf8_general_ci; you may choose another
    '                       charset + collation (constants CHARSET and COLLATION)
    ' 0.12 (Mar. 31st 2005): ignores hypothetical indexes (thanks to Brian K. Andersen)
    
    ' USEAGE:
    ' 1) copy this code into a new VBA 6 module
    '    (i.e. start Excel 2000 or Word 2000 or another
    '     program with VBA editor, hit Alt+F11, execute
    '     Insert|Module, insert code)
    '    OR copy code into an empty form of a new VB6 project
    '
    ' 2) change the constants at the beginning of the code,
    ' 3) hit F5 and execute Main()
    '    the program now connects to Microsoft SQL Server
    '    and converts the database; the resulting SQL commands
    '    are either saved in an ASCII file or executed immediately
    
    ' FUNCTION:
    ' converts both schema (tables and indices) and
    ' data (numbers, strings, dates etc.)
    ' handles table and column names which are not legal in MySQL (see MySQLName())
    
    ' LIMITATIONS:
    ' no foreign keys
    ' no SPs, no triggers (MSSQL syntax incompatible to MySQL)
    ' no views
    ' no user defined data types (not yet supported by MySQL)
    ' AUTO_INCREMENTs: MySQL does not support tables with more
    '   than one AUTO_INCREMENT column; the AUTO_INCREMENT column
    '   must also be a key column; the converter does not check this,
    '   so use MSSQL to add an index to the AUTO_INCREMENT column
    '   before starting the conversion
    ' no privileges/access infos (the idea of logins/users in M$ SQL Server
    '     is incompatible with user/group/database/table/column
    '     privileges of MySQL)
    ' cannot handle ADO type adFileTime yet
    ' GUIDs not tested
    ' fairly slow and no visible feedback during conversion process
    '   for example, it takes 80 seconds to convert Northwind (2.8 MB data)
    '   with M$SQL running on PII 350 (CPU=0) and this script running in
    '   Excel 2000 on PII 400 (CPU=100); unfortunately, compiling the program
    '   with VB6 does not make it any faster
    '   tip: test with MAX_RECORDS = 10 first to see if it works for you at all
    
    ' INTERNALS:
    ' method:   read database schema using DMO
    '           read data using a ADO recordset
    
    ' NECESSARY LIBRARIES:
    '   ADODB          (tested with 2.8, should also run with all versions >=2.1)
    '   Connector/ODBC (testet with 3.51.11, should also run with older versions;
    '                   if you use MySQL server >= 4.1,
    '                   you MUST use Connector/ODBC >= 3.51.10!!!)
    '   SQLDMO (tested with the version provided by M$ SQL Server 2000)
    '   SCRIPTING
    
    Option Explicit
    Option Compare Text
    
    ' -------------- change these constants before use!
    
                                      'M$ SQL Server
    Const MSSQL_SECURE_LOGIN = True   'login type (True for NT security)
    Const MSSQL_LOGIN_NAME = "sa"       'login name (for NT security use "" here)
    Const MSSQL_PASSWORD = "1"         'password   (for NT security use "" here)
    Const MSSQL_HOST = "(local)"      'hostname of M$ SQL Server; if localhost: use "(local)"
    Const MSSQL_DB_NAME = "TESTRAIL" 'database name
    
    Const OUTPUT_TO_FILE = 0          '1 --> write file (latin1 encoding);
                                      '0 --> connect to MySQL, execute SQL commands directly
                                      
                                      'output file (only needed if OUTPUT_TO_FILE=1)
    Const OUTPUT_FILENAME = "c:\tmp\export.sql"
    
                                      'connect to MySQL (only needed if OUTPUT_TO_FILE=0)
    Const MYSQL_USER_NAME = "roots"    'login name
    Const MYSQL_PASSWORD = "passwd"   'password
    Const MYSQL_HOST = "dns-nsme" 'if localhost: use "localhost"
    Const MYSQL_PORT = 3306           'change if you use another port
    Const MyODBCVersion = "MySQL ODBC 3.51 Driver"
                                      'for MyODBC 2.51.* use "MySQL"
    
    Const NEW_DB_NAME = ""            'name of new MySQL database ("" if same as M$SQL db name)
                                      'conversion options
    Const DROP_DATABASE = True        'begin with DROP dbname?
    Const MAX_RECORDS = 0             'max. nr of records per table (0 for all records, n for testing purposes)
    Const VARCHAR_MAX = 4096           'max. nr of characters in VARCHAR
                                      '255 for MySQL <=5.0.2
                                      'ca. 32000 for MySQL >= 5.0.3 (depends on char. set, max. 65535 bytes)
    Const TABLE_ENGINE = "InnoDB"     'MyISAM or InnoDB
    Const CHARSET = "utf8"            'character set for all text columns
    Const COLLATION = "utf8_general_ci"              'collation; leave empty if you want to use the default collation for
                                      'the specified character set
    
    ' ----------------------------- don't change below here (unless you know what you are doing)
    
    Const SQLDMOIndex_DRIPrimaryKey = 2048
    Const SQLDMOIndex_Unique = 2
    Const adEmpty = 0
    Const adTinyInt = 16
    Const adSmallInt = 2
    Const adInteger = 3
    Const adBigInt = 20
    Const adUnsignedTinyInt = 17
    Const adUnsignedSmallInt = 18
    Const adUnsignedInt = 19
    Const adUnsignedBigInt = 21
    Const adSingle = 4
    Const adDouble = 5
    Const adCurrency = 6
    Const adDecimal = 14
    Const adNumeric = 131
    Const adBoolean = 11
    Const adError = 10
    Const adUserDefined = 132
    Const adVariant = 12
    Const adIDispatch = 9
    Const adIUnknown = 13
    Const adGUID = 72
    Const adDate = 7
    Const adDBDate = 133
    Const adDBTime = 134
    Const adDBTimeStamp = 135
    Const adBSTR = 8
    Const adChar = 129
    Const adVarChar = 200
    Const adLongVarChar = 201
    Const adWChar = 130
    Const adVarWChar = 202
    Const adLongVarWChar = 203
    Const adBinary = 128
    Const adVarBinary = 204
    Const adLongVarBinary = 205
    Const adChapter = 136
    Const adFileTime = 64
    Const adPropVariant = 138
    Const adVarNumeric = 139
    Const adArray = &H2000
    
    Public dmoApplic 'As New SQLDMO.Application  'SQLDMO Application object
    Public dmoSrv    'As New SQLDMO.SQLServer    'SQLDMO Server object
    Public mssqlConn 'As New Connection          'ADO Connection to M$ SQL Server
    Public mysqlConn 'As New Connection          'ADO Connection to MySQL
    Public fso       'As Scripting.FileSystemObject
    Public fileout   'AS FSO.TextStream
    
    Public Sub Main()
      Set dmoApplic = CreateObject("SQLDMO.Application")
      Set dmoSrv = CreateObject("SQLDMO.SQLServer")
      Set mssqlConn = CreateObject("ADODB.Connection")
      Set mysqlConn = CreateObject("ADODB.Connection")
      Set fso = CreateObject("Scripting.FileSystemObject")
      ConnectToDatabases
      ConvertDatabase
      MsgBox "done"
    End Sub
    
    ' connect to M$ SQL Server and MySQL
    Private Sub ConnectToDatabases()
      dmoSrv.LoginTimeout = 10
      On Error Resume Next
      
      ' DMO connection to M$ SQL Server
      If MSSQL_SECURE_LOGIN Then
        dmoSrv.LoginSecure = True
        dmoSrv.Connect MSSQL_HOST
      Else
        dmoSrv.LoginSecure = False
        dmoSrv.Connect MSSQL_HOST, MSSQL_LOGIN_NAME, MSSQL_PASSWORD
      End If
      If Err Then
        MsgBox "Sorry, cannot connect to M$ SQL Server. " & _
          "Please edit the MSSQL constats at the beginning " & _
          "of the code." & vbCrLf & vbCrLf & Error
        End
      End If
      
      ' ADO connection to M$ SQL Server
      Dim tmpCStr$
      tmpCStr = _
        "Provider=SQLOLEDB;" & _
        "Data Source=" & MSSQL_HOST & ";" & _
        "Initial Catalog=" & MSSQL_DB_NAME & ";"
      If MSSQL_SECURE_LOGIN Then
        tmpCStr = tmpCStr & "Integrated Security=SSPI"
      Else
        tmpCStr = tmpCStr & _
          "User ID=" & MSSQL_LOGIN_NAME & ";" & _
          "Password=" & MSSQL_PASSWORD
      End If
      mssqlConn.ConnectionString = tmpCStr
      mssqlConn.Open
      If Err Then
        MsgBox "Sorry, cannot connect to M$ SQL Server. " & _
          "Please edit the MSSQL constats at the beginning " & _
          "of the code." & vbCrLf & vbCrLf & Error
        End
      End If
      
      ' ADO connection to MySQL or open output file
      If (OUTPUT_TO_FILE = 0) Then
        mysqlConn.ConnectionString = _
          "Provider=MSDASQL;" & _
          "Driver=" & MyODBCVersion & ";" & _
          "Server=" & MYSQL_HOST & ";" & _
          "UID=" & MYSQL_USER_NAME & ";" & _
          "PWD=" & MYSQL_PASSWORD & ";" & _
          "Port=" & MYSQL_PORT
        mysqlConn.Open
        mysqlConn.Execute "SET character_set_client='cp1251';"
        mysqlConn.Execute "SET character_set_results='cp1251';"
        mysqlConn.Execute "SET collation_connection='cp1251_general_ci';"
        If Err Then
          MsgBox "Sorry, cannot connect to MySQL. " & _
            "Please edit the MYSQL constats at the beginning " & _
            "of the code." & vbCrLf & vbCrLf & Error
          End
        End If
      Else
        Set fileout = fso.CreateTextFile(OUTPUT_FILENAME, Overwrite:=True)
      End If
    End Sub
    
    Private Sub ConvertDatabase()
      ' copy database schema
      Dim dmoDB 'As SQLDMO.Database
      Set dmoDB = dmoSrv.Databases(MSSQL_DB_NAME)
      DBDefinition dmoDB
      ' copy data
      CopyDB dmoDB
    End Sub
    
    ' build SQL code to define one column
    ' ColDefinition$(col As SQLDMO.Column)
    Function ColDefinition$(col)
      Dim cdef$
      cdef = MySQLName(col.Name) & " " & DataType(col)
      If col.Default <> "" Then
        cdef = cdef & " DEFAULT " & col.Default
      End If
      If col.AllowNulls Then
        cdef = cdef & " NULL"
      Else
        cdef = cdef & " NOT NULL"
      End If
      If col.Identity Then
        cdef = cdef & " AUTO_INCREMENT"
      End If
      ColDefinition = cdef
    End Function
    
    ' datatype transition M$ SQL Server --> MySQL
    ' DataType$(col As SQLDMO.Column)
    Function DataType$(col)
      Dim oldtype$, length&, precision&, scal&
      Dim newtype$
      
      oldtype = col.PhysicalDatatype
      length = col.length
      precision = col.NumericPrecision
      scal = col.NumericScale
      If LCase(oldtype) = "money" Then
        precision = 19
        scal = 4
      ElseIf LCase(oldtype) = "smallmoney" Then
        precision = 10
        scal = 4
      End If
      
      Select Case LCase(oldtype)
      
      ' integers
      Case "bit", "tinyint"
        newtype = "TINYINT"
      Case "smallint"
        newtype = "SMALLINT"
      Case "int"
        newtype = "INT"
      Case "bigint"
        newtype = "BIGINT"
      
      ' floating points
      Case "float"
        newtype = "DOUBLE"
      Case "real"
        newtype = "FLOAT"
      Case "decimal", "numeric", "money", "smallmoney"
        newtype = "DECIMAL(" & precision & ", " & scal & ")"
      
      ' strings
      Case "char", "nchar"
        If length < 255 Then
          newtype = "CHAR(" & length & ")"
        Else
          newtype = "TEXT"
        End If
      Case "varchar", "nvarchar"
        If length = 0 Then
          newtype = "LONGTEXT"
        ElseIf length < VARCHAR_MAX Then
          newtype = "VARCHAR(" & length & ")"
        Else
          newtype = "TEXT"
        End If
      Case "text", "ntext"
        newtype = "LONGTEXT"
        
      ' date/time
      Case "datetime", "smalldatetime"
        newtype = "DATETIME"
      Case "timestamp"
        newtype = "TINYBLOB"
        
      ' binary and other
      Case "uniqueidentifier"
        newtype = "TINYBLOB"
      Case "binary", "varbinary"
        newtype = "BLOB"
      Case "image"
        newtype = "LONGBLOB"
      
      Case Else
        ' unknown data type, not supported
        Stop
      End Select
      
      DataType = newtype
    End Function
    
    ' IndexDefinition$(tbl As SQLDMO.Table, idx As SQLDMO.Index)
    Function IndexDefinition$(tbl, idx)
      Dim i&
      Dim tmp$
      Dim col 'As SQLDMO.Column
      ' don't deal with system indices (used i.e. to ensure ref. integr.)
      If Left$(idx.Name, 1) = "_" Or Left$(idx.Name, 5) = "hind_" Then Exit Function
      ' index type (very incomplete !!!)
      If idx.Type And SQLDMOIndex_DRIPrimaryKey Then
        tmp = tmp & "PRIMARY KEY"
      ElseIf idx.Type And SQLDMOIndex_Unique Then
        tmp = tmp & "UNIQUE " & MySQLName(idx.Name)
      Else
        tmp = tmp & "INDEX " & MySQLName(idx.Name)
      End If
      ' index columns
      tmp = tmp & "("
      For i = 1 To idx.ListIndexedColumns.Count
        Set col = idx.ListIndexedColumns(i)
        tmp = tmp & MySQLName(col.Name)
        ' specify index length
        If Right$(DataType(col), 4) = "BLOB" Or Right$(DataType(col), 4) = "TEXT" Then
          tmp = tmp & "(" & IIf(col.length < 255, col.length, 255) & ")"
        End If
        ' seperate, if more than one index column
        If i < idx.ListIndexedColumns.Count Then tmp = tmp & ","
      Next
      tmp = tmp & ")"
      IndexDefinition = tmp
    End Function
    
    ' build SQL code to define one table
    ' TableDefinition$(tbl As SQLDMO.Table)
    Function TableDefinition$(tbl)
      Dim i&
      Dim tmp$, ixdef$
      ' table
      tmp = "CREATE TABLE " & _
            NewDBName(tbl.Parent) & "." & MySQLName(tbl.Name) & vbCrLf & "("
      For i = 1 To tbl.Columns.Count
        tmp = tmp & ColDefinition(tbl.Columns(i))
        If i < tbl.Columns.Count Then
          tmp = tmp & ", " & vbCrLf
        End If
      Next
      ' indices
      For i = 1 To tbl.Indexes.Count
        ixdef = IndexDefinition(tbl, tbl.Indexes(i))
        If ixdef <> "" Then
          tmp = tmp & ", " & vbCrLf & ixdef
        End If
      Next
      tmp = tmp & ")"
      ' engine
      tmp = tmp & " ENGINE " & TABLE_ENGINE
      ' characterset
      If CHARSET <> "" Then
        tmp = tmp & " CHARACTER SET " & CHARSET
      End If
      ' collation
      If COLLATION <> "" Then
        tmp = tmp & " COLLATE " & COLLATION
      End If
      TableDefinition = tmp
    End Function
    
    ' build SQL code to define database (all tables)
    ' DBDefinition(db As SQLDMO.Database)
    Sub DBDefinition(db)
      Dim i&
      Dim sql, dbname$
      dbname = NewDBName(db)
      If DROP_DATABASE Then
        sql = "DROP DATABASE IF EXISTS " & dbname
        ExecuteSQL sql
      End If
      sql = "CREATE DATABASE " & dbname & " CHARACTER SET " & CHARSET
      If COLLATION <> "" Then
        sql = sql & " COLLATE " & COLLATION
      End If
      ExecuteSQL sql
      For i = 1 To db.Tables.Count
        If Not db.Tables(i).SystemObject Then
          sql = TableDefinition(db.Tables(i))
          ExecuteSQL sql
        End If
      Next
    End Sub
    
    ' copy content of all M$ SQL Server tables to new MySQL database
    ' CopyDB(msdb As SQLDMO.Database)
    Sub CopyDB(msdb)
      Dim i&
      Dim tmp$
      ExecuteSQL "USE " & NewDBName(msdb)
      For i = 1 To msdb.Tables.Count
        If Not msdb.Tables(i).SystemObject Then
          CopyTable msdb.Tables(i)
        End If
      Next
    End Sub
    
    ' copy content of one table from M$ SQL Server to MySQL
    ' CopyTable(mstable As SQLDMO.Table)
    Sub CopyTable(mstable)
      Dim rec ' As Recordset
      Dim sqlInsert$, sqlValues$
      Dim i&, recordCounter&
      Set rec = CreateObject("ADODB.Recordset")
      rec.Open "SELECT * FROM [" & mstable.Name & "]", mssqlConn
      ' build beginning statement of SQL INSERT command
      ' for example: INSERT INTO tablename (column1, column2)
      sqlInsert = "INSERT INTO " & MySQLName(mstable.Name) & " ("
      For i = 0 To rec.Fields.Count - 1
        sqlInsert = sqlInsert & MySQLName(rec.Fields(i).Name)
        If i <> rec.Fields.Count - 1 Then
          sqlInsert = sqlInsert & ", "
        End If
      Next
      sqlInsert = sqlInsert & ") "
      ' for each recordset in M$SS table: build sql statement
      Do While Not rec.EOF
        sqlValues = ""
        For i = 0 To rec.Fields.Count - 1
          sqlValues = sqlValues & DataValue(rec.Fields(i))
          If i <> rec.Fields.Count - 1 Then
            sqlValues = sqlValues & ", "
          End If
        Next
        ExecuteSQL sqlInsert & " VALUES(" & sqlValues & ")"
        rec.MoveNext
        ' counter
        recordCounter = recordCounter + 1
        If MAX_RECORDS <> 0 Then
          If recordCounter >= MAX_RECORDS Then Exit Do
        End If
      Loop
    End Sub
    
    ' data transition M$ SQL Server --> MySQL
    ' DataValue$(fld As ADO.Field)
    Function DataValue$(fld)
      If IsNull(fld.Value) Then
        DataValue = "NULL"
      Else
    
        Select Case fld.Type
        
        ' integer numbers
        Case adBigInt, adInteger, adSmallInt, adTinyInt, adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt
          DataValue = fld.Value
        
        ' decimal numbers
        Case adCurrency, adDecimal, adDouble, adNumeric, adSingle, adVarNumeric
          DataValue = Str(fld.Value)
          If Not InStr(DataValue, ".") > 0 Then
            DataValue = DataValue & ".0"
          End If
          
        ' boolean
        Case adBoolean
          DataValue = IIf(fld.Value, 1, 0)
          
        ' date, time
        Case adDate, adDBDate, adDBTime
          DataValue = Format(fld.Value, "'yyyy-mm-dd Hh:Nn:Ss'")
        Case adDBTimeStamp
          DataValue = Format(fld.Value, "yyyymmddHhNnSs")
        Case adFileTime
          ' todo
          Beep
          Stop
          
        ' strings
        Case adBSTR, adChar, adLongVarChar, adVarChar, adLongVarWChar, adVarWChar, adWChar
          DataValue = "'" & Quote(fld.Value) & "'"
        
        ' binary and other
        Case adGUID
          DataValue = HexCodeGUID(fld.Value)
        Case adLongVarBinary, adVarBinary
          DataValue = HexCode(fld.Value)
        
        End Select
      End If
    End Function
    
    ' converts a Byte-array into a hex string
    ' HexCode$(bytedata() As Byte)
    Function HexCode(bytedata)
      Dim i&
      Dim tmp$
      tmp = ""
      For i = LBound(bytedata) To UBound(bytedata)
        If bytedata(i) <= 15 Then
          tmp = tmp + "0" + Hex(bytedata(i))
        Else
          tmp = tmp + Hex(bytedata(i))
        End If
      Next
      HexCode = "0x" + tmp
    End Function
    
    ' converts a String into a hex string
    ' HexCode$(bytedata() As Byte)
    Function HexCodeStr(bytedata)
      Dim i&, b&
      Dim tmp$
      tmp = ""
      For i = 1 To LenB(bytedata)
        b = AscB(MidB(bytedata, i, 1))
        If b <= 15 Then
          tmp = tmp + "0" + Hex(b)
        Else
          tmp = tmp + Hex(b)
        End If
      Next
      HexCodeStr = "0x" + tmp
    End Function
    
    ' returns name of new database
    ' NewDBName$(db As SQLDMO.Database)
    Function NewDBName$(db)
      If NEW_DB_NAME = "" Then
        NewDBName = db.Name
      Else
        NewDBName = NEW_DB_NAME
      End If
    End Function
    
    ' quote ' " and \; replace chr(0) by \0
    Function Quote(tmp)
      tmp = Replace(tmp, "\", "\\")
      tmp = Replace(tmp, """", "\""")
      tmp = Replace(tmp, "'", "\'")
      Quote = Replace(tmp, Chr(0), "\0")
    End Function
    
    ' to translate MSSQL names to legal MySQL names
    ' replace blank, -, ( and ) by '_'
    Function MySQLName(tmp)
      tmp = Replace(tmp, " ", "_")
      tmp = Replace(tmp, "-", "_")
      tmp = Replace(tmp, "(", "_")
      MySQLName = "`" & Replace(tmp, ")", "_") & "`"
    End Function
    
    ' either execute SQL command or write it into file
    Function ExecuteSQL(sql)
      If OUTPUT_TO_FILE Then
        fileout.WriteLine sql & ";"
        If Left$(sql, 6) <> "INSERT" Then
          fileout.WriteLine
        End If
      Else
        mysqlConn.Execute sql
      End If
    End Function
    
    ' this event procedures starts the converter if it is run as a VB6 programm
    Private Sub Form_Load()
      Main
      End
    End Sub
    
    ' converts a GUID string into a hex string without format
    Function HexCodeGUID(bytedata)
      Dim tmp$
      If Len(bytedata) = 38 Then
        tmp = Mid(bytedata, 2, 8) + Mid(bytedata, 11, 4) + Mid(bytedata, 16, 4) _
            + Mid(bytedata, 21, 4) + Mid(bytedata, 26, 12)
      Else
        tmp = "00"
      End If
      HexCodeGUID = "0x" + tmp
    End Function
    
    
    ' uncomment the following lines if you are using Office 97,
    ' which does not have the Replace function include; please
    ' note that this Replace function is much slower than the
    ' built-in version in Office 2000/VB6
    '
    ' to replace all occurrences of a string within a string
    ' Function Replace(tmp, fromStr, toStr)
    '     Dim leftOff&
    '     Dim pos&
    '     leftOff = 1
    '     Do While InStr$(leftOff, tmp, fromStr) > 0
    '         pos = InStr$(leftOff, tmp, fromStr)
    '         tmp = Left$(tmp, pos - 1) + toStr + Mid$(tmp, pos + Len(fromStr), Len(tmp))
    '         leftOff = pos + Len(fromStr) + 1
    '     Loop
    '     Replace = tmp
    ' End Function