A little more help with ODBC

[marcuslee]

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 ;)

[ErosOlmi]

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

[marcuslee]

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/smfforum/index.php?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 8)

[Michael Hartlef]

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

[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

[marcuslee]

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:



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


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.



'---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


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 ???

[Petr Schreiber]

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:


'----------------------------------------------------------------------------
'----------------------------------------------------------------------------
' 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


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


OdbcBindCol(hStmt, 1, %SQL_C_LONG , VARPTR(lAuId) , 0 , cbbytes)


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

[marcuslee]

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

[Petr Schreiber]

Thanks :)


Petr