[RESOLVED] OPTION on MySQL Connection String

[afdoal]

hi...
i found this code

Private Sub ConnServer()
  'connect to MySQL server using MySQL ODBC 3.51 Driver
  Set conn = New ADODB.Connection
  conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                        & "SERVER=localhost;" _
                        & " DATABASE=PERSONAL;" _
                        & "UID=root;PWD=root; OPTION=3"
conn.Open
End Sub

on this threat for connect to mysql from VB6.

in other web/blog, i found this code for connect to mysql from vb6

 strConn = "DRIVER={MySQL ODBC 3.51 Driver};" _
                & "SERVER=" & strServer & ";" _
                & "DATABASE=manajemen_sms;" _
                & "UID=" & UNAME & "; PWD=" & UPASS & ";" _
                & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

- what is the purpose of use "OPTION=3"
- where to get the other option parameter??

thanks b4

[techgnome]

a quick googlling of "mysql connectionstring OPTION" turns up a thread where this exact question is asked... and points to this in the documentation for mySql...

http://dev.mysql.com/doc/refman/5.0/...arameters.html

-tg

[afdoal]

a quick googlling of "mysql connectionstring OPTION" turns up a thread where this exact question is asked... and points to this in the documentation for mySql...

http://dev.mysql.com/doc/refman/5.0/...arameters.html

-tg

thanks for your replay
now i have a question, how to add other OPTION paramater on the connection string?? just use "+"??

example: i need use parameter:

• FLAG_LOG_QUERY
• FLAG_NO_CACHE
• FLAG_AUTO_RECONNECT
• FLAG_AUTO_IS_NULL

[techgnome]

if you know their values, you add them... like in this example:
"OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

OR... you add them up and figure out what the total value should be, and put it in there yourself like this:
"OPTION=9999" (999 is just an example)

-tg

[afdoal]

many thanks

I wrote a little enum (copying from the option page on MySQL documentation)

Public Enum MyOption
        'recommended option values for various configurations.
        optVB = 3                      'Microsoft Access, Visual Basic
        optLargeTables = 2049          'Large tables with too many rows
        optSybasePB = 135168           'sysbase powerbuilder
        optLT_nocache = 3145731         'Large tables with no-cache results
 
        'other Option Flags
        optFieldLength = 1              'FLAG_FIELD_LENGTH       'Don't Optimize Column Width
        optFoundRows = 2                'FLAG_FOUND_ROWS         'Return Matching Rows
        optDebug = 4                    'FLAG_DEBUG              'Trace Driver Calls To myoptbc.log
        optBigPacket = 8                'FLAG_BIG_PACKETS        'Allow Big Results
        optNoPrompt = 16                'FLAG_NO_PROMPT          'Don't Prompt Upon Connect
        optDynamicCursor = 32           'FLAG_DYNAMIC_CURSOR     'Enable Dynamic Cursor
        optNoSchema = 64                'FLAG_NO_SCHEMA          'Ignore # in Table Name
        optNoDefaultCursor = 128        'FLAG_NO_DEFAULT_CURSOR  'User Manager Cursors
        optNoLocale = 256               'FLAG_NO_LOCALE          'Don't Use Set Locale
        optPadSpace = 512               'FLAG_PAD_SPACE          'Pad Char To Full Length
        optFullColumnNames = 1024       'FLAG_FULL_COLUMN_NAMES  'Return Table Names for SQLDescribeCol
        optCompressedProto = 2048       'FLAG_COMPRESSED_PROTO   'Use Compressed Protocol
        optIgnoreSpace = 4096           'FLAG_IGNORE_SPACE       'Ignore Space After Function Names
        optNamedPipe = 8192             'FLAG_NAMED_PIPE         'Force Use of Named Pipes
        optNoBigInt = 16384             'FLAG_NO_BIGINT          'Change BIGINT Columns to Int
        optNoCatalog = 32768            'FLAG_NO_CATALOG         'No Catalog
        optUseMyCnf = 65536             'FLAG_USE_MYCNF          'Read Options From my.cnf
        optSafe = 131072                'FLAG_SAFE               'Safe
        optNoTransactions = 262144      'FLAG_NO_TRANSACTIONS    'Disable transactions
        optLogQuery = 524288            'FLAG_LOG_QUERY          'Save queries to myodbc.sql
        optNoCache = 1048576            'FLAG_NO_CACHE           'Don't Cache Result (forward only cursors)
        optForwardCursor = 2097152      'FLAG_FORWARD_CURSOR     'Force Use Of Forward Only Cursors
        optAutoReconnect = 4194304      'FLAG_AUTO_RECONNECT     'Enable auto-reconnect.
        optAutoIsNull = 8388608         'FLAG_AUTO_IS_NULL       'Flag Auto Is Null
        optZeroDateToMin = 16777216     'FLAG_ZERO_DATE_TO_MIN   'Flag Zero Date to Min
        optMinDateToZero = 33554432     'FLAG_MIN_DATE_TO_ZERO   'Flag Min Date to Zero
        optMultiStatements = 67108864   'FLAG_MULTI_STATEMENTS   'Allow multiple statements
        optColumnSizeS32 = 134217728    'FLAG_COLUMN_SIZE_S32    'Limit column size to 32-bit value
End Enum

Public Function KonekToServer(strServer As String, UNAME As String, UPASS) As Boolean
    On Error GoTo errHandle
    Dim MyOdbcOption
    MyOdbcOption = optFoundRows + optVB + optBigPacket + optDynamicCursor + _
                optCompressedProto + optNoBigInt + optAutoReconnect
    
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" _
                & "SERVER=" & strServer & ";" _
                & "DATABASE=ManajemenSekolah;" _
                & "UID=" & UNAME & "; PWD=" & UPASS & ";" _
                & "OPTION=" & MyOdbcOption
                
    Set KonekDB = New ADODB.Connection
    KonekDB.CursorLocation = adUseClient
    KonekDB.ConnectionString = strConn
    KonekDB.Open
    
    KonekToServer = True
    
    Exit Function
    
errHandle:
    MsgBox Err.Description & vbCrLf & vbCrLf & strConn
    KonekToServer = False
End Function

problem solved......