Liberty BASIC Community Forum
« MySQL can be used from LB, example attached »

Welcome Guest. Please Login or Register.
Sep 21st, 2017, 11:02am


Rules|Home|Help|Search|Recent Posts|Notification


« Previous Topic | Next Topic »
Pages: 1 2  Notify Send Topic Print
 hotthread  Author  Topic: MySQL can be used from LB, example attached  (Read 2580 times)
CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 308
xx MySQL can be used from LB, example attached
« Thread started on: Nov 24th, 2006, 4:08pm »

Ok, guys and girls, to shut the discussion about whether MySQL can be used from LB or not; please find below the source code of a complete working example.

I have downloaded MySQL and necessary DLLs from www.mysql.com and got it working in couple of hours.

And, I know almost nothing about databases or Windows. In fact, the example based on Dennis McKinney was so good that everything was self evident. When something didn't worked, the error message exactly displayed what was wrong.

Enjoy !

CryptoMan


PART 1/2

Code:


'Microsoft SQL Server 2000 usage with ODBC on Liberty BASIC
'Copyright (c) 2005, Verisoft, Onur Alver
'Credit: Based on Dennis McKinney's DSNless ODBC Demo Example
'
' SYSTEM REQUIREMENTS:
'      Microsoft SQL Server 2000
'      MyLibertyDB Database must already exist, create this with MS SQL Enterprise Manager


    Driver$ = "ODBC"

    '** Globals
    gTmpTable$(0) = ""      'temporary array for table names
    gTable$(0) = ""         'array for table names
    dim gColname$(0)        'for 1 recordset column names
    dim gRowset$(1,1)       'for 1 recordset row values
    dim gColRow(2)          'for 1 recordset row & col count
    struct gHandle, h as long   'for ODBC handles
    struct NativeErrorPtr, errcode as long  'for error handler
    struct TextLengthPtr, length as short
    struct ColumnCountPtr, count as long    'for GetColumns
    struct NumAttPtr, value as short
    Global hWnd
    Global GlobalCtr
    GlobalCtr=0
    '** end Globals


    UpperLeftX=100
    UpperLeftY=100

    WindowWidth = 800
    WindowHeight =580

    nomainwin

    texteditor #main.te, 0, 0, 790, 530

    UpperLeftX=int((DisplayWidth-WindowWidth)/2)
    UpperLeftY=int((DisplayHeight-WindowHeight)/2)
    menu #main, "&File", "E&xit", [Quit]
    menu #main, "Edit"
    menu #main, "&Database", "&New Database", [CreateDatabase], _
                "&Open Database", [OpenDatabase], "&Close", [CloseDatabase]
    menu #main, "&Table", "&Create 1 Test Table", [CreateTable], _
                "&Drop Test Table", [DeleteTable]
    menu #main, "&Records", "&Add 200 Random Records", [AddSampleRecord], _
                "R&ead Records", [ReadRecords], _
                "&Delete John Strong Record(s)", [DeleteJohnStrong]
    menu #main, "&Help", "About", [About],"Tutorial", [Tutorial]

    txt$ = "Steps:"+chr$(10)+"1: Create MyLibertyDB (one time) with MySQL Command Line Client"+chr$(10)
    txt$ = txt$+"2: Open MyLibertyDB database"+chr$(10)
    txt$ = txt$+"3: Create a table"+chr$(10)
    txt$ = txt$+"4: Add a record"+chr$(10)
    txt$ = txt$+"5: Experiment"+chr$(10)
    txt$ = txt$+"6: Close the database"
    'statictext #main, txt$,10,50,200,200

    open "MySQL ODBC Demo" for window as #main
    #main, "trapclose [Quit]"
    #main.te, "!autoresize";   'Tell the texteditor to resize with the terminal window"
    #main, "font fixedsys 10";

    hWnd=hwnd(#main)

    Open "myodbc3.dll" for dll as #odbc

    [loop]
    wait

[Quit]

    'every database opened must be closed
    If hDb1 > 0 then
        ret = CloseDatabase(hDb1)
    End if

    'every workspace opened must be closed
    hWksp1 = CloseWorkspace(hWksp1)

    Close #odbc: close #main

    end

[About]
    Notice  "About Liberty MySQL Demo"+chr$(13)+"Verisoft MySQL ODBC Demo"+chr$(13)+"Version 1.00"+chr$(13)+"Verisoft, Copyright (c) 2005"+chr$(13)+"www.verisoft.com"+chr$(13)+"Based on Dennis McKinney's DSNless ODBC Demo"
    goto [loop]

[Tutorial]
    Notice  "Liberty MySQL Tutorial"+chr$(13)+txt$ 
    goto [loop]

[CreateDatabase]
    Notice "Create Database First !"+chr$(13)+"Use MySQL Command Line Client"+chr$(13)+"to create MyLibertyDB Database !"
    goto [loop]

[OpenDatabase]
    Cursor hourglass
    If hDb1 = 0 then
            hWksp1 = OpenWorkspace() 'or Environment
            hDb1   = OpenDatabase(hWksp1,Driver$,DbPath$,Uid$,Pwd$)
     Else
        Notice "Database already open."
    End if
    Notice "DB Handle:";STR$(hDb1)
    Cursor normal
goto [loop]

[CloseDatabase]
    hDb1 = CloseDatabase(hDb1)
goto [loop]

[CreateTable]
    Cursor hourglass
    t1=time$("ms")
    If hDb1 > 0 then
        SQL$ = "CREATE TABLE TestTable"
        SQL$ = SQL$ + " (ID int, LastName varchar(20), FirstName varchar(20),"
        SQL$ = SQL$ + " SSN varchar(20), Notes varchar(255) );"
        hStmt = GetStmtHandle(hDb1)
        ret = RunSQL(hStmt, SQL$)
        Call FreeStatement hStmt
        t2=time$("ms")
        Notice "MS SQLServer Table Create Performance"+chr$(13)+"Table created in "+str$(t2-t1)+" milliseconds"
    End if
    Cursor normal
goto [loop]

[AddSampleRecord]
    If hDb1 > 0 then

   t1=time$("ms")
   for i = 1 to 200

        sName$=word$("Mary Michael Greg James John Alice Susan George David Roger",1+int(rnd(1)*9))
        sSurname$=word$("Stone Strong Wood Smith Doe Wonder Farmer Smart Lion Chainer",1+int(rnd(1)*9))
        sSSN$=left$( str$( 100+int(rnd(1)*999) ), 3 ) + "-" + left$( str$( 10+int(rnd(1)*99)),2 ) + "-"+ left$(str$(1000+int(rnd(1)*9999)),4)
        sNotes$=word$("Good-Programmer Great-Programmer www.verisoft.com-programmer Marvelous-Programmer Exceptional-Programmer Spectacular-Programmer Fine-Programmer Wonderful-Programmer Super-Programmer Wizard-Programmer",1+int(rnd(1)*9))

        GlobalCtr=GlobalCtr+1
        IDCTR$="'"+STR$(GlobalCtr)+"' "
        SQL$ = "INSERT INTO TestTable (ID, LastName, FirstName, SSN, Notes)"
        SQL$ = SQL$ + " VALUES(";IDCTR$;",'"+sSurname$+"','"+sName$+"','"+sSSN$+"','"+sNotes$+"');"

        Cursor hourglass

        hStmt = GetStmtHandle(hDb1)

        ret = RunSQL(hStmt, SQL$)

        Call FreeStatement hStmt

        If ret = 0 then exit for
   next i

   t2=time$("ms")
   Notice "MySQL Insert Performance"+chr$(13)+"200 records inserted in "+str$(t2-t1)+" milliseconds"

   Cursor Normal

    End if
goto [loop]

[ReadRecords]

    NoRecs=0

    If hDb1 > 0 then
        t1=time$("ms")

        Cursor hourglass
        SQL$ = "SELECT ID, FirstName, LastName, SSN, Notes"
        SQL$ = SQL$ + " FROM TestTable;"

        ret = OpenRecordset(hDb1,SQL$)
        t2=time$("ms")
        Notice "MySQL Select Performance"+chr$(13)+str$(gColRow(2))+" records selected in "+str$(t2-t1)+" milliseconds"

        print  #main.te,"---------------------------------------------------------------------------------------- "
        If ret <> 0 then
            for i = 1 to gColRow(1)
                print #main.te,gColname$(i)+space$(16-len(gColname$(i)));
            next i
        End If
         print  #main.te,""
        print  #main.te,"---------------------------------------------------------------------------------------- "

        for i = 1 to gColRow(2)
            for j = 1 to gColRow(1)
                print  #main.te,gRowset$(i,j)+space$(16-len(gRowset$(i,j)));
            next j
            print  #main.te," "
            NoRecs=NoRecs+1
        next i
        print  #main.te,"---------------------------------------------------------------------------------------- "
        print  #main.te,"Number records read = ";NoRecs
        Cursor normal
    End if
goto [loop]

[DeleteTable]
    Cursor hourglass
    t1=time$("ms")
    If hDb1 > 0 then
        SQL$ = "DROP TABLE TestTable"
        hStmt = GetStmtHandle(hDb1)
        ret = RunSQL(hStmt, SQL$)
        Call FreeStatement hStmt
    End if
    t2=time$("ms")
    Notice "MySQL Drop Table Performance"+chr$(13)+"Table dropped in "+str$(t2-t1)+" milliseconds"

    Cursor normal
goto [loop]

[DeleteJohnStrong]
    t1=time$("ms")
    Cursor hourglass
    If hDb1 > 0 then
        SQL$ = "DELETE FROM TestTable WHERE FirstName Like 'John%'"
        SQL$ = SQL$ + " AND LastName Like 'Strong%';"
        hStmt = GetStmtHandle(hDb1)
        ret = RunSQL(hStmt, SQL$)
        Call FreeStatement hStmt
    End if
    t2=time$("ms")
    Notice "MySQL Delete Performance"+chr$(13)+"Record(s) deleted in "+str$(t2-t1)+" milliseconds"

    Cursor normal
goto [loop]


 
User IP Logged

.....
CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 308
xx Re: MySQL can be used from LB, example attached
« Reply #1 on: Nov 24th, 2006, 4:15pm »

PART 2/2

Code:
'*********************** SUBS & FUNCTIONS ******************************

Function OpenRecordset(hDb,SQL$)
    SQL.NO.DATA = 100
    hStmt = GetStmtHandle(hDb)
    ret = RunSQL(hStmt, SQL$)
    if ret <> 0 then cols = GetColumnCount(hStmt)
    if cols > 0 then 'a recordset was created
        Redim gColname$(cols)
        For i = 1 to cols
           gColname$(i) = GetColNames$(hStmt,i)
        Next i

        Redim gRowset$(10000,cols)
        'move to the first row of the result set
        calldll #odbc,"SQLFetch",hStmt as long,ret as short

        i = 1
        While ret <> SQL.NO.DATA
            For j = 1 to cols
                gRowset$(i,j) = GetColData$(hDb,hStmt,j)
            Next j
            i = i + 1
            calldll #odbc,"SQLFetch",hStmt as long,ret as short
            If i > 9999 then Exit While
        Wend

        'store col & row count
        gColRow(1) = cols
        gColRow(2) = i-1

        OpenRecordset = 1 'success
    else
        OpenRecordset = 0 'fail
    end if

    calldll #odbc,"SQLCloseCursor",hStmt as long,ret as short
    Call FreeStatement hStmt
End Function

Function GetColData$(hDb,hStmt,ColNum)
    SQLState$ = space$(5) + chr$(0)
    MessageText$ = space$(256) + chr$(0)
    SQL.Char = 1
    SQL.SUCCESS = 0
    SQL.SUCCESS.WITH.INFO = 1
    SQL.NO.DATA = 100
    SQL.ERROR = -1
    'To test the handling of truncated data use
    'Buffer$ = Space$(10)
    Buffer$ = Space$(512)
    Buflen = len(Buffer$)

    While ret <> SQL.NO.DATA
        calldll #odbc,"SQLGetData",hStmt as long,ColNum as short, _
            SQL.Char as short,Buffer$ as ptr, Buflen as short, _
            TextLengthPtr as ptr,ret as short
        Select case ret
            case SQL.SUCCESS
                GetColData$ = GetColData$ + _
                left$(Buffer$, TextLengthPtr.length.struct)
            case SQL.ERROR
                GetColData$ = ""
                Exit While
            case SQL.SUCCESS.WITH.INFO
                calldll #odbc,"SQLGetDiagRec",3 as short,hStmt as long, _
                1 as short,SQLState$ as ptr,NativeErrorPtr as ptr, _
                MessageText$ as ptr,256 as short,TextLengthPtr as ptr, _
                ret as short
                If left$(SQLState$,5) = "01004" then
                    GetColData$ = GetColData$ + _
                    left$(Buffer$,instr(Buffer$,chr$(0))-1)
                End if
        End select
    Wend
End Function

Function GetColNames$(hStmt,ColNum)
    SQL.COLUMN.LABEL  = 18
    Buffer$ = Space$(256) + chr$(0)
    calldll #odbc,"SQLColAttribute",hStmt as long,ColNum as short, _
        SQL.COLUMN.LABEL as short,Buffer$ as ptr,255 as short, _
        TextLengthPtr as ptr,NumAttPtr as ptr,ret as short

    GetColNames$ = left$(Buffer$, TextLengthPtr.length.struct)
End Function

Function GetColumnCount(hStmt)
 'In: statement handle.
 'Out: number of columns in result set, if any.
 '0 if error or no result set was created.
    SQL.SUCCESS = 0
    SQL.STILL.EXECUTING = 2
    ColumnCountPtr.count.struct = 0

    ret = SQL.STILL.EXECUTING
    While ret = SQL.STILL.EXECUTING
        calldll #odbc,"SQLNumResultCols",hStmt as long, _
            ColumnCountPtr as ptr,ret as short
        Select Case ret
            case SQL.STILL.EXECUTING
            case SQL.SUCCESS
                GetColumnCount = ColumnCountPtr.count.struct
            case Else
                Call ErrMsg 3,hStmt
                GetColumnCount = 0
        End Select
    Wend
End Function


Function CreateDatabase(Driver$,Attributes$)
    open "odbccp32.dll" for dll as #odbccp32
    calldll #odbccp32,"SQLConfigDataSource",0 as long,1 as short, _
        Driver$ as ptr,Attributes$ as ptr,ret as boolean
    close #odbccp32

    If ret = 0 Then
        Notice "Create Database Error"+chr$(13)+"Failed to create database"
    End If
    CreateDatabase = ret
End Function

Function GetTables(InputHandle)
 'Purpose: fill gTable$ array with table names
 'In: database connection handle
 'Out: number of table names added to gTable$ array
    Redim gTmpTable$(10000)
    struct LenRead, val as long
    struct OutputHandlePtr, handle as long
    SQL.NO.DATA = 100
    SQL.HANDLE.STMT = 3
    TableName$ = Space$(256) + chr$(0)
    TableType$ = "'TABLE'" + chr$(0)
    lenTableType = Len(TableType$)

    calldll #odbc, "SQLAllocHandle",SQL.HANDLE.STMT as short, _
        InputHandle as long,OutputHandlePtr as ptr,ret as short
    If ret <> 0 then
        GetTables = 0
        goto [exitGetTables]
    End If
    hStmt = OutputHandlePtr.handle.struct

    calldll #odbc, "SQLTables",hStmt as long,0 as short,-3 as short, _
        0 as short,-3 as short,0 as short,-3 as short,TableType$ as ptr, _
        lenTableType as short,ret as short

    If ret <> 0 then
        Call ErrMsg 3,hStmt
        GetTables = 0
        goto [exitGetTables]
    End If

    calldll #odbc,"SQLFetch",hStmt as long,ret as short
    If ret <> 0 then
        GetTables = 0
        goto [exitGetTables]
    End If

    count = 0
    While intRc <> SQL.NO.DATA
        calldll #odbc,"SQLGetData",hStmt as long,3 as short,1 as short, _
        TableName$ as ptr,255 as short,LenRead as ptr,intRc as short

        TableName$ = left$(TableName$,LenRead.val.struct)

        gTmpTable$(count) = TableName$
        count = count + 1

        TableName$ = Space$(256) + chr$(0)
        calldll #odbc,"SQLFetch",hStmt as long,intRc as short
    Wend

    Redim gTable$(count)
    For i = 0 to count-1
        gTable$(i) = gTmpTable$(i)
    next i
    Redim gTmpTable$(0)

    GetTables = count

 [exitGetTables]
    Call FreeStatement hStmt
End Function

Function OpenWorkspace()
 'Out: Workspace (environment) handle.
    gHandle.h.struct = 0
    calldll #odbc,"SQLAllocHandle",1 as short,0 as long,gHandle as ptr, _
        ret as short
    hEnv = gHandle.h.struct

    ver3 = 3 'use ODBC 3
    calldll #odbc,"SQLSetEnvAttr",hEnv as long,200 as long,ver3 as long, _
        0 as long,ret as short

    OpenWorkspace = hEnv
End Function

Function CloseWorkspace(hWorkspace)
    calldll #odbc,"SQLFreeHandle",1 as short,hWorkspace as long,ret as short
    CloseWorkspace = ret
End Function

Function OpenDatabase(WorkSpace,Driver$,DbPath$,Uid$,Pwd$)
 'In: Workspace/environment handle, datasource path,
     'optional user ID, optional password.
 'Out: database connection handle, 0 if failed.

    gHandle.h.struct = 0
    calldll #odbc,"SQLAllocHandle",2 as short,WorkSpace as long, _
    gHandle as ptr,ret as short

    hCon = gHandle.h.struct


    connstr$ ="Driver={MySQL ODBC 3.51 Driver}; Server=localhost;PORT=3306; Database=mylibertydb; UID=root; PWD=sa; OPTION=3 "
    outConnStr$ = space$(1024)
    Lconnstr = len(connstr$)
    struct cbStrLen, x as long

    calldll #odbc,"SQLDriverConnect", hCon as ulong, 0 as long, connstr$ as ptr, _
        Lconnstr as short, outConnStr$ as ptr, 1024 as short, cbStrLen as ptr, _
        0 as short, ret as long

    IF ret <> 0 then
        Call ErrMsg 2,hCon
        hCon = CloseDatabase(hCon)
    End IF

    OpenDatabase = hCon
End Function

Function CloseDatabase(hDb)
 'In: database connection handle.
 'Out: 0 if successful, >0 if failed.
    calldll #odbc,"SQLDisconnect",hDb as long,ret as short
    calldll #odbc,"SQLFreeHandle",2 as short,hDb as long,ret as short

    IF ret <> 0 then Call ErrMsg 2,hDb

    CloseDatabase = ret
End Function

Function GetStmtHandle(hDBC)
    gHandle.h.struct = 0

    calldll #odbc,"SQLAllocHandle",3 as short,hDBC as long, _
        gHandle as ptr, ret as short
    hStmt = gHandle.h.struct

    GetStmtHandle = hStmt
End Function

Function RunSQL(hStmt,strSQL$)
 'In: database connection handle, SQL statement to execute.
 'Out: 0 if failed, statement handle if successful.

    SQL.STILL.EXECUTING = 2
    lenStmt = len(strSQL$)

    ret = SQL.STILL.EXECUTING
    While ret = SQL.STILL.EXECUTING
        calldll #odbc, "SQLExecDirect",hStmt as long,strSQL$ as ptr,_
            lenStmt as short,ret as short
    Wend

    If ret <> 0 then
        Call ErrMsg 3,hStmt
        RunSQL = 0
        goto [exitRunSQL]
    End If

    RunSQL = hStmt
 [exitRunSQL]
End Function

Sub FreeStatement hStmt
    calldll #odbc,"SQLFreeHandle",3 as short,hStmt as long,ret as short
End Sub

Sub ErrMsg hType,Hndl
    SQLState$ = space$(5) + chr$(0)
    MessageText$ = space$(256) + chr$(0)

    calldll #odbc,"SQLGetDiagRec",hType as short,Hndl as long, _
    1 as short,SQLState$ as ptr,NativeErrorPtr as ptr, _
    MessageText$ as ptr,256 as short,TextLengthPtr as ptr,ret as short
    i = i + 1

    msg$ = "SQLState Code: "+left$(SQLState$,5)+chr$(10)
    msg$=msg$+"Native Error: "+str$(NativeErrorPtr.errcode.struct)+chr$(10)
    msg$=msg$+left$(MessageText$,TextLengthPtr.length.struct)

    If msg$ <> "" Then
        Notice "LB ODBC Error"+chr$(13)+msg$
    Else
        Notice "LB ODBC Error"+chr$(13)+"An unKnown error occured. Check your code."
    End if
End Sub

 
User IP Logged

.....
CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 308
xx Re: MySQL can be used from LB, example attached
« Reply #2 on: Nov 24th, 2006, 4:19pm »

SMALL CORRECTION:
Comments must read as follows:

Code:

'MySQL usage with ODBC on Liberty BASIC
'Copyright (c) 2005, Verisoft, Onur Alver
'Credit: Based on Dennis McKinney's DSNless ODBC Demo Example
'
' SYSTEM REQUIREMENTS:
'      MySQL 3.51
'      MyLibertyDB Database must already exist, create this with MySQL Command Line Client


    Driver$ = "ODBC"

    '** Globals
    gTmpTable$(0) = ""      'temporary array for table names
    gTable$(0) = ""         'array for table names
    dim gColname$(0)        'for 1 recordset column names
    dim gRowset$(1,1)       'for 1 recordset row values
    dim gColRow(2)          'for 1 recordset row & col count
    struct gHandle, h as long   'for ODBC handles
    struct NativeErrorPtr, errcode as long  'for error handler
    struct TextLengthPtr, length as short
    struct ColumnCountPtr, count as long    'for GetColumns
    struct NumAttPtr, value as short
    Global hWnd
    Global GlobalCtr
    GlobalCtr=0
    '** end Globals


    UpperLeftX=100
    UpperLeftY=100

    WindowWidth = 800
    WindowHeight =580

    nomainwin

    texteditor #main.te, 0, 0, 790, 530

    UpperLeftX=int((DisplayWidth-WindowWidth)/2)
    UpperLeftY=int((DisplayHeight-WindowHeight)/2)
    menu #main, "&File", "E&xit", [Quit]
    menu #main, "Edit"
    menu #main, "&Database", "&New Database", [CreateDatabase], _
                "&Open Database", [OpenDatabase], "&Close", [CloseDatabase]
    menu #main, "&Table", "&Create 1 Test Table", [CreateTable], _
                "&Drop Test Table", [DeleteTable]
    menu #main, "&Records", "&Add 200 Random Records", [AddSampleRecord], _
                "R&ead Records", [ReadRecords], _
                "&Delete John Strong Record(s)", [DeleteJohnStrong]
    menu #main, "&Help", "About", [About],"Tutorial", [Tutorial]

    txt$ = "Steps:"+chr$(10)+"1: Create MyLibertyDB (one time) with MySQL Command Line Client"+chr$(10)
    txt$ = txt$+"2: Open MyLibertyDB database"+chr$(10)
    txt$ = txt$+"3: Create a table"+chr$(10)
    txt$ = txt$+"4: Add a record"+chr$(10)
    txt$ = txt$+"5: Experiment"+chr$(10)
    txt$ = txt$+"6: Close the database"
    'statictext #main, txt$,10,50,200,200

    open "MySQL ODBC Demo" for window as #main
    #main, "trapclose [Quit]"
    #main.te, "!autoresize";   'Tell the texteditor to resize with the terminal window"
    #main, "font fixedsys 10";

    hWnd=hwnd(#main)

    Open "myodbc3.dll" for dll as #odbc

    [loop]
    wait

[Quit]

    'every database opened must be closed
    If hDb1 > 0 then
        ret = CloseDatabase(hDb1)
    End if

    'every workspace opened must be closed
    hWksp1 = CloseWorkspace(hWksp1)

    Close #odbc: close #main

    end

[About]
    Notice  "About Liberty MySQL Demo"+chr$(13)+"Verisoft MySQL ODBC Demo"+chr$(13)+"Version 1.00"+chr$(13)+"Verisoft, Copyright (c) 2005"+chr$(13)+"www.verisoft.com"+chr$(13)+"Based on Dennis McKinney's DSNless ODBC Demo"
    goto [loop]

[Tutorial]
    Notice  "Liberty MySQL Tutorial"+chr$(13)+txt$ 
    goto [loop]

[CreateDatabase]
    Notice "Create Database First !"+chr$(13)+"Use MySQL Command Line Client"+chr$(13)+"to create MyLibertyDB Database !"
    goto [loop]

[OpenDatabase]
    Cursor hourglass
    If hDb1 = 0 then
            hWksp1 = OpenWorkspace() 'or Environment
            hDb1   = OpenDatabase(hWksp1,Driver$,DbPath$,Uid$,Pwd$)
     Else
        Notice "Database already open."
    End if
    Notice "DB Handle:";STR$(hDb1)
    Cursor normal
goto [loop]

[CloseDatabase]
    hDb1 = CloseDatabase(hDb1)
goto [loop]

[CreateTable]
    Cursor hourglass
    t1=time$("ms")
    If hDb1 > 0 then
        SQL$ = "CREATE TABLE TestTable"
        SQL$ = SQL$ + " (ID int, LastName varchar(20), FirstName varchar(20),"
        SQL$ = SQL$ + " SSN varchar(20), Notes varchar(255) );"
        hStmt = GetStmtHandle(hDb1)
        ret = RunSQL(hStmt, SQL$)
        Call FreeStatement hStmt
        t2=time$("ms")
        Notice "MS SQLServer Table Create Performance"+chr$(13)+"Table created in "+str$(t2-t1)+" milliseconds"
    End if
    Cursor normal
goto [loop]

[AddSampleRecord]
    If hDb1 > 0 then

   t1=time$("ms")
   for i = 1 to 200

        sName$=word$("Mary Michael Greg James John Alice Susan George David Roger",1+int(rnd(1)*9))
        sSurname$=word$("Stone Strong Wood Smith Doe Wonder Farmer Smart Lion Chainer",1+int(rnd(1)*9))
        sSSN$=left$( str$( 100+int(rnd(1)*999) ), 3 ) + "-" + left$( str$( 10+int(rnd(1)*99)),2 ) + "-"+ left$(str$(1000+int(rnd(1)*9999)),4)
        sNotes$=word$("Good-Programmer Great-Programmer www.verisoft.com-programmer Marvelous-Programmer Exceptional-Programmer Spectacular-Programmer Fine-Programmer Wonderful-Programmer Super-Programmer Wizard-Programmer",1+int(rnd(1)*9))

        GlobalCtr=GlobalCtr+1
        IDCTR$="'"+STR$(GlobalCtr)+"' "
        SQL$ = "INSERT INTO TestTable (ID, LastName, FirstName, SSN, Notes)"
        SQL$ = SQL$ + " VALUES(";IDCTR$;",'"+sSurname$+"','"+sName$+"','"+sSSN$+"','"+sNotes$+"');"

        Cursor hourglass

        hStmt = GetStmtHandle(hDb1)

        ret = RunSQL(hStmt, SQL$)

        Call FreeStatement hStmt

        If ret = 0 then exit for
   next i

   t2=time$("ms")
   Notice "MySQL Insert Performance"+chr$(13)+"200 records inserted in "+str$(t2-t1)+" milliseconds"

   Cursor Normal

    End if
goto [loop]

[ReadRecords]

    NoRecs=0

    If hDb1 > 0 then
        t1=time$("ms")

        Cursor hourglass
        SQL$ = "SELECT ID, FirstName, LastName, SSN, Notes"
        SQL$ = SQL$ + " FROM TestTable;"

        ret = OpenRecordset(hDb1,SQL$)
        t2=time$("ms")
        Notice "MySQL Select Performance"+chr$(13)+str$(gColRow(2))+" records selected in "+str$(t2-t1)+" milliseconds"

        print  #main.te,"---------------------------------------------------------------------------------------- "
        If ret <> 0 then
            for i = 1 to gColRow(1)
                print #main.te,gColname$(i)+space$(16-len(gColname$(i)));
            next i
        End If
         print  #main.te,""
        print  #main.te,"---------------------------------------------------------------------------------------- "

        for i = 1 to gColRow(2)
            for j = 1 to gColRow(1)
                print  #main.te,gRowset$(i,j)+space$(16-len(gRowset$(i,j)));
            next j
            print  #main.te," "
            NoRecs=NoRecs+1
        next i
        print  #main.te,"---------------------------------------------------------------------------------------- "
        print  #main.te,"Number records read = ";NoRecs
        Cursor normal
    End if
goto [loop]

[DeleteTable]
    Cursor hourglass
    t1=time$("ms")
    If hDb1 > 0 then
        SQL$ = "DROP TABLE TestTable"
        hStmt = GetStmtHandle(hDb1)
        ret = RunSQL(hStmt, SQL$)
        Call FreeStatement hStmt
    End if
    t2=time$("ms")
    Notice "MySQL Drop Table Performance"+chr$(13)+"Table dropped in "+str$(t2-t1)+" milliseconds"

    Cursor normal
goto [loop]

[DeleteJohnStrong]
    t1=time$("ms")
    Cursor hourglass
    If hDb1 > 0 then
        SQL$ = "DELETE FROM TestTable WHERE FirstName Like 'John%'"
        SQL$ = SQL$ + " AND LastName Like 'Strong%';"
        hStmt = GetStmtHandle(hDb1)
        ret = RunSQL(hStmt, SQL$)
        Call FreeStatement hStmt
    End if
    t2=time$("ms")
    Notice "MySQL Delete Performance"+chr$(13)+"Record(s) deleted in "+str$(t2-t1)+" milliseconds"

    Cursor normal
goto [loop]


 
Code:
TEXT 
Code:
TEXT 
User IP Logged

.....
CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 308
xx Re: MySQL can be used from LB, example attached
« Reply #3 on: Nov 24th, 2006, 4:35pm »

REMEMBER:

You must go into

CONTROL PANEL

Performance and Maintanence

Administrative Tools

Data Sources (odbc)

Add "MySQL ODBC Driver 3.51"

-----

MySQL and MyODBC3.dll from www.mysql.com site download page.

After installing MySQL, you can use the MySQL Command Line Client to manage your database and give interactive SQL commands. Use this utility to create MyLibertyDB.

Please note the user name and passwords which you will need later in the connection string.

Play around with the client utility to create tables, issue select commands and master database admin commands before moving onto Liberty code. These are related and it will help finding your way around.

CryptoMan









User IP Logged

.....
Lewis Eason
Team Liberty BASIC


member is offline

Avatar




PM

Gender: Male
Posts: 346
xx Re: MySQL can be used from LB, example attached
« Reply #4 on: Dec 29th, 2006, 10:00am »

on Nov 24th, 2006, 4:35pm, CryptoMan wrote:
REMEMBER:

You must go into

CONTROL PANEL

Performance and Maintanence

Administrative Tools

Data Sources (odbc)

Add "MySQL ODBC Driver 3.51"


Any way to automate?

thanks
User IP Logged

CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 308
xx Re: MySQL can be used from LB, example attached
« Reply #5 on: Dec 30th, 2006, 04:01am »

I don't know if all this could be automated but maybe someone can collect all the components and make an installer.

I don't know MySQL much or use it.

I have only done this exercise to show that it can be done because some people were complaining that this was not possible.

So, I went to MySQL web site and downloaded all those parts required and struggled a bit to understand the tricks to get it going and when finished I published it to help those interested.

Months past and nobody made any comments and I have drifted into other things. As I don't use it actively, I am not really the expert to give the best suggestions here.

However, I think the demo which I have adapted from my previous Microsoft SQL Server demo is interesting and comprehensive to demonstrate various things about building useful SQL applications with LB.

Please excuse my terse notes about installation. I just left some clues as much I could remember. It will be good if someone takes good notes following my clues and publish here a comprehensive installation guide for other's benefit - I can make use of it as well.
User IP Logged

.....
ardentcrest
New Member
Image


member is offline

Avatar




PM

Gender: Male
Posts: 47
xx Re: MySQL can be used from LB, example attached
« Reply #6 on: Jan 30th, 2007, 4:35pm »

I've downloaded the code Found it helpful

But

I have a database on the internet that I want to use no matter what i do I cant get to it

can any one help
User IP Logged

CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 308
xx Re: MySQL can be used from LB, example attached
« Reply #7 on: Feb 1st, 2007, 2:24pm »

What do you mean with "I have a database on internet" ?

Do you mean your database server is connected to Internet and you want to access this from another workstation over Internet ?

To make this work, you need to know your "real IP" address. For this you can try "www.whatismyip.com".

Once you know your address visible from Internet to others, you have to make sure that your DSL or Wireless router allows the database server port open. Normally the firewall on the routers keeps all the ports closed. You have to open it explicitly.

If it is open as I explained, it should work.



User IP Logged

.....
bartslipper
New Member
Image


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 19
xx Re: MySQL can be used from LB, example attached
« Reply #8 on: May 19th, 2007, 08:02am »

You also have to config that your MySQL server is accessible form the world wide web. Normally is your MySQL server only accessible from the localhost. And you have to give the user that u use rights to that what i say above.


Bartslippr
User IP Logged

traceykiel
Guest
xx Re: MySQL can be used from LB, example attached
« Reply #9 on: Jul 18th, 2007, 2:27pm »

I think ardencrest is saying he/she has a database on their website server that they normally access via their website's control panel.

I have read this post with interest and think it would be excellent of Cryptoman and others to post their findings.
Findings such as;

What is the exact procedure for installing mysql? Step by step.

When you have mysql running how do you get a website database to be global, not localhost?

When the above two are exaplained, give an example of putting it all together with a real example of connecting to a test website based database.

Please note; I know nothing of this kind of programming but like others would like the above explained and exampled.

Good luck wink

Tracey
User IP Logged

CryptoMan
Senior Member
ImageImageImageImage


member is offline

Avatar




Homepage PM

Gender: Male
Posts: 308
xx Re: MySQL can be used from LB, example attached
« Reply #10 on: Jul 25th, 2007, 07:19am »

I have made that example months ago and I explained what I did to make it work.

I am not an expert on databases and I did that example to show that it can be done to someone who was claiming that it can not be done, etc.

What I had done was, I went to www.mysql.com web site, download all necessary things into my PC and made sure that mySQL is running stand alone in my PC. One of the things that comes during this installations is a black MS DOS box from where you can start, stop the server and act as a MySQL Server DBA (database admin). Using this, I made sure that I can create and drop tables, and able to give command line SQL commands.

When you pass this stage, you have to download the ODBC component and make the necessary configurations as I explained.

Once this is done, you can test it in local mode, i.e. on the same PC where mySQL server and LB ODBC example running together.

If you need to access this database from remote then you have to know your global IP number and port; and you have to make sure that your firewall allows access to this port.

If all that made; you can specify that IP for remote access to your database.

For web applications, this db should be visible to the web server; i.e. either it must be on the same machine or another server in the LAN. Web server itself is another application which works as a TCP/IP server listening at PORT 80. It sends streams of data in HTML format to render images and text on web browsers. This data may come from strings and numbers hard wired in the server, or from (a) text file(s) containing yet other HTML encoded test strings or collected from random access files or from database tables. Hence, if one wishes to reflect a db table to web, for example list of students in a class from an SQL table, that web server must connect to that SQL server to fetch the data and render it in HTML format so anyone with a web browser can see it. This is a matter of writing a merged application which acts as a web server and an ODBC based SQL client.

User IP Logged

.....
hanklaw
New Member
Image


member is offline

Avatar




PM


Posts: 3
xx Re: MySQL can be used from LB, example attached
« Reply #11 on: Mar 25th, 2008, 11:08pm »

I too am very interested in getting access to MySQL via LB.

I went to MySQL.com and downloaded the needed ODBC 3.51 install file (and installed it). I then did the following:

You must go into

CONTROL PANEL

Performance and Maintanence

Administrative Tools

Data Sources (odbc)

Add "MySQL ODBC Driver 3.51"

per above instructions but the was no "MySQL ODBC Driver 3.51" option to ADD.

I am taking detailed notes of everything I do and will post a guide for DUMMIES once I get it all to work.

What should I do now since I can't seem to install this needed Data Source? If you need more info from me, let me know.

hank
User IP Logged

Stefan Pendl
Global Moderator
ImageImageImageImageImage


member is offline

Avatar

Computers are like babies, you must teach them what you like them to do ...


Homepage PM

Gender: Male
Posts: 5295
xx Re: MySQL can be used from LB, example attached
« Reply #12 on: Mar 26th, 2008, 02:15am »

on Mar 25th, 2008, 11:08pm, hanklaw wrote:
Add "MySQL ODBC Driver 3.51"

per above instructions but the was no "MySQL ODBC Driver 3.51" option to ADD.

Did you check, if it was already there huh
I think it should register itself.
User IP Logged

Stefan

Make sure to read and follow the Forum Guidelines

Liberty BASIC Pro 4.04, Windows 10 Professional x64, Intel Core i7-4710MQ 2.5GHz, 16GB RAM
hanklaw
New Member
Image


member is offline

Avatar




PM


Posts: 3
xx Re: MySQL can be used from LB, example attached
« Reply #13 on: Mar 26th, 2008, 10:50am »

Yup - it's not there. Below is a list of what my options were and I couldn't find the 3.51 as an option (I hope you can read this, if not I'll type up the options and repost)

http://www.lhs.fuhsd.org/alumni/NOTEMPLATE/0_odbc.jpg

hank

« Last Edit: Mar 26th, 2008, 11:19am by Alyce Watson » User IP Logged

hanklaw
New Member
Image


member is offline

Avatar




PM


Posts: 3
xx Re: MySQL can be used from LB, example attached
« Reply #14 on: Mar 26th, 2008, 11:08am »

OK - it didn't like my jpg link... here's the list of what shows up:

CR DB2 Wire Protocol ODBC Driver 5.0
DR Informix ODBC Driver 5.0
CR Informix Wire Protocol ODBC Driver 5.0
CR ODBC XML Driver 5.0
CR Oracle ODBC Driver 5.0
CR Oracle Wire Protocol ODBC Driver 5.0
CR SQL Server Wire Protocol Mapper 5.0
CR Sybase Wire Protocol ODBC Driver 5.0
CR Text ODBC Driver 5.0
Driver da Microsoft para arquivos texto **.txt,*.csv)
Driver do Microsoft Access (*.mdb)
Driver do Microsoft dBase (*.dbf)
Driver do Microsoft Excel (*.xls)
Driver do Microsoft Paradox (*.db)
Driver para o Microsoft Visual FoxPro
Microsoft Access Driver (*.mdb)
Microsoft Access-Treiber (*.mdb)
Microsoft dBase Driver (*.dbf)
Microsoft dBase VFP Driver (*.dbf)
Microsoft dBase-Treiber (*.dbf)
Microsoft Excel Driver (*.xls)
Microsoft Excel-Treiber (*.xls)
Microsoft FoxPro Driver (*.dbf)
Microsoft FoxPro VFP Driver (*.dbf)
Microsoft ODBC for Oracle
Microsoft Paradox Driver (*.db)
Microsoft Paradox-Treiber (*.db)
Microsoft Text Driver (*.txt,*.csv)
Microsoft Test-Treiber (*.txt,*.csv)
Microsoft Visual FoxPro Driver
Microsoft Visual FoxPro-Treiber Driver
SQL Server


This last one looking the most promising out of all of them but does fit the description of what I'm supposed to add so not wanting to mess something up, I left alone. The file I downloaded and ran from www.mysql.com is:

mysql-connector-odbc-3.51.23-win32.msi

and it looked like it ran correctly - it gave me the FINISH button which I click on...

Any ideas?

hank
User IP Logged

Pages: 1 2  Notify Send Topic Print
« Previous Topic | Next Topic »

Rules|Home|Help|Search|Recent Posts|Notification

Donate $6.99 for 50,000 Ad-Free Pageviews!

| |

This forum powered for FREE by Conforums ©
Sign up for your own Free Message Board today!
Terms of Service | Privacy Policy | Conforums Support | Parental Controls