Results 1 to 9 of 9

Thread: A little more help with ODBC

  1. #1
    Member marcuslee's Avatar
    Join Date
    Sep 2008
    Location
    Kansas, USA
    Age
    42
    Posts
    222
    Rep Power
    38

    A little more help with ODBC

    ThinBasic is so complex. There is so much to it ... that the help file seems to be missing somethings. Understandably so! There is too much to keep up with. If it weren't for these forums and the super nice people who peruse, it would be hopeless for someone to learn much of anything.

    There doesn't seem to be much info on ODBC. There are a couple of sample scripts, and these are great. But, it would be nice to have a least a little more. Perhaps a description and example use of some of the major functions included in the library. How to interact with MS Access would be nice (beyond what the sample scripts show).

    If you can't tell, I am looking into all sorts of ways to mantain a database. That's why I have asked about the Dictionary Module and have been interested in RAF in the past. I haven't decided what path to choose. I may never get this game created or I may never use what I learn here, but it is fun exploring, and if it helps someone else ... all the better.

    Mark

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

    Re: A little more help with ODBC

    Mark,

    I think all languages are difficult at first especially if you start from the help.

    In my experience, the best way to learn a new programming language is to have a precise little problem to solve. Not big projects, not too much complex programs. Just one little everyday problem to be solved (it depends in which field you are interested in). I've always done in that way to learn all the programming languages I know.

    Of course you first need to know if the programming language you are trying to learn has at least the basis for the problem you are facing. So if you are interested in DBMS, be sure the language has direct access to the DBMS you need or at least ODBC interaction.

    Why so little examples and documentation on ODBC in thinBasic?
    2 reasons: time and interest.
    We usually develop more what people ask. Why loosing our time in developing and documenting things we do not need and no-one asked for it? As soon as there is some interest in an argument, we are very happy to go deeper in it.
    Regarding ODBC documentation, the library we use as wrapper, follows almost 1 to 1 ODBC standards. Writing a documentation on ODBC would require months. So we prefer to reply to single precise requests or problems here in forum when someone needs more info.

    Anyhow, at http://www.jose.it-berater.org/odbc/iframe/index.htm you can already find a lot of info.
    The few thinBasic examples you can find in \thinBasic\SampleScripts\ODBC\ should be enough to start (open ODBC driver, conenct to a MSACCESS DB, make queries, ..., close)

    If you need more info, let me know.
    Eros

    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  3. #3
    Member marcuslee's Avatar
    Join Date
    Sep 2008
    Location
    Kansas, USA
    Age
    42
    Posts
    222
    Rep Power
    38

    Re: A little more help with ODBC

    Quote Originally Posted by Eros Olmi
    I think all languages are difficult at first especially if you start from the help.
    This is true, though some HELPs are better than others. Except for some missing examples, ThinBasic's HELP is pretty good.

    In my experience, the best way to learn a new programming language is to have a precise little problem to solve. Not big projects, not too much complex programs. Just one little everyday problem to be solved (it depends in which field you are interested in). I've always done in that way to learn all the programming languages I know.
    Well, I do have big things in mind, but I go about putting those together with one thing in mind at a time. But, I understand what you mean. I will probably never will be able to punch out code like y'all can, but it is fun trying to do something that I otherwise couldn't.

    Why so little examples and documentation on ODBC in thinBasic?
    2 reasons: time and interest.
    We usually develop more what people ask. Why loosing our time in developing and documenting things we do not need and no-one asked for it? As soon as there is some interest in an argument, we are very happy to go deeper in it.

    This makes complete sense. I wouldn't expect you to put something together that no one appreciates. Afterall, you do so much already!

    Regarding ODBC documentation, the library we use as wrapper, follows almost 1 to 1 ODBC standards. Writing a documentation on ODBC would require months. So we prefer to reply to single precise requests or problems here in forum when someone needs more info.
    That's good to know. I have been Googling ODBC to learn more, and a lot of the info is over my head in the sense that I can't do what they say. Either that or they are speaking of C or C++ or Visual Basic, none of which I understand very well at all. That's why I stick to the Basics. (Yes, slight pun there!)

    I did find some descriptions on Jose's forums.

    http://www.jose.it-berater.org/smffo...hp?topic=303.0

    I will continue looking. Do you or anyone else reading this post know of any sites that discuss in Basic style syntax how to interact with MS Access using ODBC?

    Anyhow, at http://www.jose.it-berater.org/odbc/iframe/index.htm you can already find a lot of info.
    As far as I can tell, none of it relates to MS Access but SQL.

    The few thinBasic examples you can find in \thinBasic\SampleScripts\ODBC\ should be enough to start (open ODBC driver, conenct to a MSACCESS DB, make queries, ..., close)
    That might be enough. I'm not sure. I will have to study the samples a little more. I also need to look into how to make queries beyond the one in the examples.


    If you need more info, let me know.
    Eros
    Always! But, seriously, don't go out of your way. I'm only exploring. The question I asked earlier would be helpful if answered. (The one in red!)

    Mark

  4. #4

    Re: A little more help with ODBC

    Hi Mark, I have MS Access at work and will see if I can get a sample up for you today. Press your thumbs that it will be a quite day at work.

    Michael

  5. #5

    Re: A little more help with ODBC

    Hi Mark,

    not sure what you mean with "interacting with MS Access". If you ment "how to open a MS ACCESS database and read the content" then please look at the samples. They show just that. It also helps to read tutorials about SQL in general. With this and an existing MS ACCESS database, you can do everything I can imagine.

    Or did you ment "Control ACCESS in a remote kind of way"?

    Michael

  6. #6
    Member marcuslee's Avatar
    Join Date
    Sep 2008
    Location
    Kansas, USA
    Age
    42
    Posts
    222
    Rep Power
    38

    Re: A little more help with ODBC

    Quote Originally Posted by Michael Hartlef
    Hi Mark,

    not sure what you mean with "interacting with MS Access". If you ment "how to open a MS ACCESS database and read the content" then please look at the samples. They show just that. It also helps to read tutorials about SQL in general. With this and an existing MS ACCESS database, you can do everything I can imagine.

    Or did you ment "Control ACCESS in a remote kind of way"?

    Michael
    The sample scripts are a good start. What I would like to do is dump the contents of a database into an array or set of arrays. I don't want to just print them on the screen once I get a hold of them.

    Use an array like this one, possibly:

    [code=thinbasic]
    Type MyType
    aut_id As String
    author As String
    year_born As String
    End Type

    Dim ax(20) As MyType '---Used to store database elements
    [/code]

    Then, in this block of code, instead of filling the database stuff into columns, put the data into the array of MyType. I don't know how to do that, though.

    [code=thinbasic]
    '---Binds the columns ...
    '---First declare some variable able to receive binded DB colums ...
    dim cbbytes AS LONG
    dim lAuId AS LONG
    dim szAuthor AS ASCIIZ * 256
    dim iYearBorn AS INTEGER

    '---than use OdbcBindCol giving column number, type, ...
    OdbcBindCol(hStmt, 1, %SQL_C_LONG , VARPTR(lAuId) , 0 , cbbytes)
    OdbcBindCol(hStmt, 2, %SQL_C_CHAR , VARPTR(szAuthor) , SIZEOF(szAuthor), cbbytes)
    OdbcBindCol(hStmt, 3, %SQL_C_SHORT , VARPTR(iYearBorn) , 0 , cbbytes)

    '---Set cursor type
    OdbcSetKeysetDrivenCursor hStmt
    '---Set optimistic concurrency
    OdbcSetOptimisticConcurrency hStmt

    '---Generates a result set
    OdbcExecDirect hStmt, "SELECT TOP 20 * FROM Authors ORDER BY Author"
    IF OdbcError THEN
    console_writeline(OdbcGetStatementErrorInfo(hStmt))
    Terminate
    END IF

    DO
    OdbcFetch hStmt
    IF ISTRUE(OdbcError) THEN EXIT DO

    console_write ( using$("#### ", lAuId) )
    console_write ( using$("\ \ ", szAuthor) )
    console_writeline ( using$("####", iYearBorn) )

    LOOP
    [/code]

    I'm sorry if I am trying to do something over my head. I have a tendency to do that. I'm not even sure I understand how the computer knows what to console_write on the screen. Does the data come in a certain order? So, the Aut_ID is first, so it is put where lAuId is? It is hard to change the code when I don't understand it exactly. A little help in this area would be nice ... when you get the chance. Don't get fired on my account.

    Going to work myself now ...

    Mark ???

  7. #7
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,128
    Rep Power
    732

    Re: A little more help with ODBC

    Hi Mark,

    I am not expert on ODBC, but here comes modification of sample code to retrieve data to array first, and then print it:
    [code=thinbasic]
    '----------------------------------------------------------------------------
    '----------------------------------------------------------------------------
    ' This script will demonstrate how to:
    ' - create a new ODBC environment
    ' - connect to a DB
    ' - perform SQL queries
    ' - retrieve data fetching records
    ' - bind script variables to data fields
    '----------------------------------------------------------------------------
    ' 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"

    ' -- User defined type
    Type MyType
    aut_id As long
    author As asciiz * 256
    year_born As integer
    End Type

    dim i as long

    '---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 an environment handle
    hEnv = OdbcAllocEnv
    IF OdbcError THEN
    console_writeline("Script aborted. It was not possible to create an environment handle.")
    Terminate
    end if

    '---Allocates the connection handle
    hDbc = OdbcAllocConnect(hEnv)
    IF ISFALSE hDbc THEN
    console_writeline("Script aborted. It was not possible to allocate the connection handle.")
    Terminate
    end if

    '---Connects with the ODBC driver
    ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;"
    OdbcOpenConnection(hDbc, ConStr)
    IF OdbcError THEN
    console_writeline(OdbcGetConnectionErrorInfo(hDbc))
    Terminate
    END IF

    '---Allocates a statement handle
    hStmt = OdbcAllocStmt(hDbc)

    '---Binds the columns ...
    '---First declare some variable able to receive binded DB colums ...
    dim cbbytes AS LONG
    dim lAuId AS LONG
    dim szAuthor AS ASCIIZ * 256
    dim iYearBorn AS INTEGER

    '---than use OdbcBindCol giving column number, type, ...
    OdbcBindCol(hStmt, 1, %SQL_C_LONG , VARPTR(lAuId) , 0 , cbbytes)
    OdbcBindCol(hStmt, 2, %SQL_C_CHAR , VARPTR(szAuthor) , SIZEOF(szAuthor), cbbytes)
    OdbcBindCol(hStmt, 3, %SQL_C_SHORT , VARPTR(iYearBorn) , 0 , cbbytes)

    '---Set cursor type
    OdbcSetKeysetDrivenCursor hStmt
    '---Set optimistic concurrency
    OdbcSetOptimisticConcurrency hStmt

    '---Generates a result set
    OdbcExecDirect hStmt, "SELECT TOP 20 * FROM Authors ORDER BY Author"
    IF OdbcError THEN
    console_writeline(OdbcGetStatementErrorInfo(hStmt))
    Terminate
    END IF

    '---Parse the result set.
    '---Automatically binded variables will be filled with relevant data

    ' -- Our array to hold information
    Dim MyData(20) As MyType
    DIM ItemNumber as long

    ' -- Fill array with table data
    DO
    OdbcFetch hStmt
    IF ISTRUE(OdbcError) THEN EXIT DO

    ' -- Increment index for array
    ItemNumber = ItemNumber + 1

    ' -- Check if we have enough room in array, if not - REDIM
    if ItemNumber > ubound(MyData) then
    redim preserve MyData(ItemNumber+20) ' -- Make array bigger, with some extra space
    end if

    ' -- Assign retrieved data
    MyData(ItemNumber).aut_id = lAuId
    MyData(ItemNumber).author = szAuthor
    MyData(ItemNumber).year_born = iYearBorn

    LOOP

    PRINTL "Printing from my array, just some info"

    for i = 1 to ItemNumber
    printl "Author " + MyData(i).author + " was born on " + IIF$(MyData(i).year_born = 0, "... well, don't know", FORMAT$(MyData(i).year_born))
    next

    Terminate

    console_waitkey



    '----------------------------------------------------------------------------
    function Terminate()
    '----------------------------------------------------------------------------

    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

    'stop

    end function
    [/code]

    I think the binding of ODBC data to temporary variable is done like this:
    [code=thinbasic]
    OdbcBindCol(hStmt, 1, %SQL_C_LONG , VARPTR(lAuId) , 0 , cbbytes)
    [/code]

    You can see the odbc command here specifies which column to read (1), using which datatype (%SQL_C_LONG, derived from C data type name ) and pointer to variable which receives the data ( done by VARPTR(lAuId) )


    Hope it helps,
    Petr
    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 10 64bit - Intel Core i5-3350P @ 3.1GHz - 16 GB RAM - NVIDIA GeForce GTX 1050 Ti 4GB

  8. #8
    Member marcuslee's Avatar
    Join Date
    Sep 2008
    Location
    Kansas, USA
    Age
    42
    Posts
    222
    Rep Power
    38

    Re: A little more help with ODBC

    Quote Originally Posted by Psch
    I am not expert on ODBC, but here comes modification of sample code to retrieve data to array first, and then print it:
    Yes, that is what I was looking for. Simple enough, but I didn't understand how the binding command worked.

    I think the binding of ODBC data to temporary variable is done like this:

    You can see the odbc command here specifies which column to read (1), using which datatype (%SQL_C_LONG, derived from C data type name ) and pointer to variable which receives the data ( done by VARPTR(lAuId) )


    Hope it helps,
    Petr
    Yes, this helps very much so. I didn't realize that the column refered to in the command was from the database. Yes, stupid of me, I was thinking that had to do with writing the info to the console later. I didn't even realize!

    For such a clear and easy explanation (as well as answering my question), I award you.

    Mark ;D ;D ;D

  9. #9
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,128
    Rep Power
    732

    Re: A little more help with ODBC

    Thanks


    Petr
    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 10 64bit - Intel Core i5-3350P @ 3.1GHz - 16 GB RAM - NVIDIA GeForce GTX 1050 Ti 4GB

Similar Threads

  1. ODBC getting specific row
    By martin in forum ODBC
    Replies: 22
    Last Post: 21-08-2009, 11:48

Members who have read this thread: 0

There are no members to list at the moment.

Posting Permissions

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