Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: how to create a simple ODBC database?

  1. #1
    Member
    Join Date
    Mar 2009
    Location
    Netherlands
    Age
    50
    Posts
    248
    Rep Power
    37

    how to create a simple ODBC database?

    I have seen the examples for reading an existing ODBC database, but is it difficult to create a new database with a table and some fields?

    And what are other good alternatives to create a database with ThinBasic? For me it's important that it can handle thousands of records and there a good and fast ways to search records. That's why I think ODBC would be the best.

    Some advice?

  2. #2
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    54
    Posts
    8,636
    Blog Entries
    2
    Rep Power
    10

    Re: how to create a simple ODBC database?

    The following piece of code will just create a brand new empty MSACCESS database in the script path.
    I would go with MSACCESS because you should be able to find ODBC drivers on every Windows computers.

    Thanks to the fact that we are using Josť Roca ODBC wrapper library, and and thanks to the fact that Josť library is compliant with ODBC 3.5, you can get info on Internet everywhere they are talking about ODBC.

    I'm at work now but I will reply later on how to add tables and fields.

    [code=thinbasic]
    '----------------------------------------------------------------------------
    '----------------------------------------------------------------------------
    ' This script will demonstrate how to:
    '----------------------------------------------------------------------------
    ' Thanks to Jose Roca for his hard work on ODBC wrapper
    '----------------------------------------------------------------------------

    '---Use console module to show output
    uses "console"

    '---IMPORTANT: include this file in order to use ODBC functionalities
    ' inside your scripts
    #include "%APP_INCLUDEPATH%\ODBC352.INC"

    dim DBName as string
    dim lRet as long

    DBName = app_sourcepath & "new.mdb"
    lRet = SQLConfigDataSource(0, %ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)", "CREATE_DB=" & DBName)

    if lRet then
    printl "Creation of " & DBName & " was OK."
    else
    printl "It was not possible to create " & DBName
    end if

    printl
    printl "---Press a key to finish---"
    waitkey
    [/code]

    Reference:
    http://support.microsoft.com/kb/149558
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  3. #3
    Member
    Join Date
    Mar 2009
    Location
    Netherlands
    Age
    50
    Posts
    248
    Rep Power
    37

    Re: how to create a simple ODBC database?

    Thank you for this code, I'm looking forward to your next example!

    I do also prefer ODBC because I already have some experience with this in Visual Basic.

    Hasta la pasta,

    Martin

    p.s. My messages and replies are often short, that's not because i'm lazy but my english is not very good so sometimes it's hard to express myself.

  4. #4
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    54
    Posts
    8,636
    Blog Entries
    2
    Rep Power
    10

    Re: how to create a simple ODBC database?

    Check attached script: it check if DB is already there if not, create it and add a new table with a primary key and 2 text fields
    [code=thinbasic]
    '----------------------------------------------------------------------------
    '----------------------------------------------------------------------------
    ' This script will demonstrate how to:
    '----------------------------------------------------------------------------
    ' Thanks to Jose Roca for his hard work on ODBC wrapper
    '----------------------------------------------------------------------------

    '---Use console module to show output
    uses "Console"
    uses "File"

    '---IMPORTANT: include this file in order to use ODBC functionalities
    ' inside your scripts
    #include "%APP_INCLUDEPATH%\ODBC352.INC"

    dim DBName as string
    dim lRet as long
    dim DBOk as long
    dim DBJustCreated as long
    dim szCon as asciiz * 256
    dim szAttr as asciiz * 256

    DBName = app_sourcepath & "new.mdb"

    '--_Check if DB exists, if not try to create
    if file_exists(DBName) then
    printl "DB " & dbname & " already present, will not be created"
    DBOk = %TRUE
    else
    szCon = "Microsoft Access Driver (*.mdb)"
    szAttr = "CREATE_DB=" & $DQ & DBName & $DQ
    lRet = SQLConfigDataSource(0, %ODBC_ADD_DSN, szCon, szAttr)

    if lRet then
    printl "Creation of " & DBName & " was OK."
    DBOk = %TRUE
    DBJustCreated = %TRUE
    else
    printl "It was not possible to create " & DBName
    Terminate(%TRUE)
    end if
    end if


    '---Declare needed variables
    dim hEnv AS DWORD '---Environment handle
    dim hDbc AS DWORD '---Connection handle
    dim hStmt AS DWORD '---Statement handle

    dim ConStr AS STRING '---Connection string


    '---Allocates the environment handle
    print "... allocating ODBC environment: "
    hEnv = OdbcAllocEnv
    IF ISFALSE hEnv THEN
    printl("Script aborted. It was not possible to create an environment handle.")
    Terminate(%TRUE)
    end if
    printl "OK"

    '---Allocates the connection handle
    print "... allocating ODBC connection: "
    hDbc = OdbcAllocConnect(hEnv)
    IF ISFALSE hDbc THEN
    printl("Script aborted. It was not possible to allocate the connection handle.")
    Terminate(%TRUE)
    end if
    printl "OK"

    '---Connects with the ODBC driver
    print "... connecting to data: "
    ConStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBName & ";UID=;PWD=;"
    OdbcOpenConnection(hDbc, ConStr)
    IF OdbcError THEN
    printl(OdbcGetConnectionErrorInfo(hDbc))
    Terminate(%TRUE)
    END IF
    printl "OK"

    '---Allocates a statement handle
    print "... allocating statement: "
    hStmt = OdbcAllocStmt(hDbc)
    IF ISFALSE hDbc THEN
    printl("Script aborted. It was not possible to allocate a new statement.")
    Terminate(%TRUE)
    end if
    printl "OK"


    '---If the DB was just created, we need to add new tables and fields
    if DBJustCreated then

    print "... creating table MyDisks: "
    '---Create a new table and its fields
    OdbcExecDirect hStmt, _
    "CREATE TABLE MyDisks " & _
    " (" & _
    " SID integer PRIMARY KEY, " & _
    " Title TEXT(255), " & _
    " Author TEXT(255) " & _
    " ) " & _
    ""
    printl "OK"
    else
    printl ">>> No tables created because DB was already present on disk."
    end if

    Terminate(%False)

    printl
    printl "---All done. Press a key to stop---"
    waitkey

    '----------------------------------------------------------------------------
    function Terminate(optional byval TerminateWithError as long)
    '----------------------------------------------------------------------------

    'IF hStmt THEN OdbcCloseCursor hStmt '---Closes the cursor
    IF hStmt THEN OdbcCloseStmt hStmt '---Closes the statement handle
    IF hDbc THEN OdbcCloseConnection hDbc '---Closes the connection
    IF hEnv THEN OdbcFreeEnv hEnv '---Frees the environment handle

    if TerminateWithError then
    stop
    end if

    end function


    [/code]

    You can find all info on the web searching for SLQ statements.

    Ciao
    Eros

    PS: your English is perfect (well, I'm Italian so I can be partly sure it is ).
    Attached Files Attached Files
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  5. #5
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    54
    Posts
    8,636
    Blog Entries
    2
    Rep Power
    10

    Re: how to create a simple ODBC database?

    Martin,

    attached file shows you how to write data into the table previously created in the DB and read data back.
    Adding 10000 records and reading back in descending order takes less than 0.5 seconds on my computer.
    Reading data is done using columns binding (bind script variables to DB columns) but other more simple strategies are possible.

    Of course table has SID field that is primary key so you cannot add new records having the same SID so in real life application you need to take care of all the duties of checking return results.

    Ciao
    Eros
    Attached Files Attached Files
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  6. #6
    Member
    Join Date
    Mar 2009
    Location
    Netherlands
    Age
    50
    Posts
    248
    Rep Power
    37

    Re: how to create a simple ODBC database?

    Check attached script: it check if DB is already there if not, create it and add a new table with a primary key and 2 text fields
    Very interesting code and pretty easy to understand. Again learned some valueble information today

    But somehow I get this error when running the script: "ODBC Microsoft Access Driver Login Failed". Although the Thinbasic's ODBC examples to read a database are running fine. Any idea why I get this error? Am I missing a driver? Googled for it but couldn't find the right answer.

    Martin

  7. #7
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    54
    Posts
    8,636
    Blog Entries
    2
    Rep Power
    10

    Re: how to create a simple ODBC database?

    That error can be due to many factors: from invalid path to incorrect connection string, ....
    I wrote down script examples quite quickly so maybe I didn't add all necessary checks.

    When exactly do you get that error? In which script and at which line?
    If you debug your script (press F8 in thinAir) and execute it line by line (again use F8 key) you should be able to give me more info.

    Thanks
    Eros
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  8. #8
    Member
    Join Date
    Mar 2009
    Location
    Netherlands
    Age
    50
    Posts
    248
    Rep Power
    37

    Re: how to create a simple ODBC database?

    The error appears when the script hits line 5039 in the include-file:


    5036 '---Connects with the ODBC driver
    5037 print "... connecting to data: "
    5038 ConStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBName & ";UID=;PWD=;"
    5039 OdbcOpenConnection(hDbc, ConStr)
    

  9. #9
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    54
    Posts
    8,636
    Blog Entries
    2
    Rep Power
    10

    Re: how to create a simple ODBC database?

    Martin,

    I think the problem occurs if the path to the new DB contains spaces or is located in special folders. ODBC driver has problems parsing it.
    Please download again script from above post http://community.thinbasic.com/index...20768#msg20768
    and try again. I've changes to path to the file in order to add double quote around it.

    Eros
    www.thinbasic.com | www.thinbasic.com/community/ | psch.thinbasic.com
    Win10Pro 64bit - 8GB Ram - Intel i7 M620 2.67GHz - NVIDIA Quadro FX1800M 1GB

  10. #10
    Member
    Join Date
    Mar 2009
    Location
    Netherlands
    Age
    50
    Posts
    248
    Rep Power
    37

    Re: how to create a simple ODBC database?

    Yes Eros, now it works, done in 0.297 seconds !
    happy happy



Page 1 of 2 12 LastLast

Similar Threads

  1. Alternative for ODBC Database
    By martin in forum thinBasic General
    Replies: 34
    Last Post: 29-08-2009, 08:17

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •