how to create a simple ODBC database?

[martin]

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

[ErosOlmi]

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 (http://www.jose.it-berater.org/smfforum/index.php?board=291.0), 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.



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


Reference:
http://support.microsoft.com/kb/149558

[martin]

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.

[ErosOlmi]

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


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




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 :D ).

[ErosOlmi]

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

[martin]

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 :D

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

[ErosOlmi]

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

[martin]

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)

[ErosOlmi]

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.php?topic=2747.msg20768#msg20768
and try again. I've changes to path to the file in order to add double quote around it.

Eros

[martin]

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

[martin]

Hello Eros,

It goes very well with setting up a database but somehow I can't update a record with OdbcUpdateRecord.
The biggest chance is that I am doing something wrong (well, I followed this example: http://www.jose.it-berater.org/smfforum/index.php?topic=307.0). Although it's so simple that I almost couldn't do anything wrong. So I was thinking there's maybe a bug in ThinBasic itself. If you have some time, could you please check it out for me...?

Thanks in advance,

Martin

[martin]

PROBLEM SOLVED!
I'm using OdbcSetabsolutePosition before OdbcUpdateRecord and it works fine now!

[Michael Hartlef]

Martin,

If you have time for it, could you post a sample which includes creating the database, writing and reading records?

Michael

[martin]

Sure, I modified a little bit the scripts earlier posted by Eros.

It shows how to:
-create a database
-create a new table and fields
-add records
-read a record
-update a record
-delete all records

see attachment....

[Michael Hartlef]

Thank you :)

[Petr Schreiber]

Thanks,

very practical example

[JohnP]

Hi all,
The ODBC examples that come with thinBASIC show only how to use it with an MS Access database.

I have an existing, functional, MySQL database on my Windows 7 desktop, which I would like to use with thinBASIC.

I have tried to adapt the examples, but without success because, I suspect, I am not using the correct syntax to access the MySQL ODBC driver.

I have installed the MySQL ODBC 5.1 driver, which I obtained from the MySQL website.
I can see the driver in the Control Panel/Administrative Tools/Data Sources (ODBC) window, so it appears to have installed correctly. I don't have MS Excel, so I can't easily test if the driver is working or not.

I used the same syntax as shown in the examples, but simply changed the name of the driver to that of the MySQL one.
Here is a snippet of code to show what I did...




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

'---Connects with the ODBC driver
'Immediately below is the original version of the ConStr for the MS Access driver...
'ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;"
'Immediately below is my new version of the ConStr string....
ConStr = "DRIVER={MySQL ODBC 5.1 Driver};DBQ=databasename;UID=localhost;PWD=password;"
OdbcOpenConnection(hDbc, ConStr)
IF OdbcError THEN
console_writeline(OdbcGetConnectionErrorInfo(hDbc))
Terminate
Else
PrintL "ODBC connection handle created OK.":PrintL
END IF



Of course, the 'databasename' and 'password' placeholders have been amended to hide the real versions.
I get the following result...

>>Environment handle created OK

>>Connection handle created OK

>>SqlState: IM008
>>Native error: 0
>>[MySQL][ODBC 5.1 Driver]Invalid window handle

Could any kind person, experienced in using MySQL, please help? :unguee:

TIA
John

[ErosOlmi]

I've never used MySQL ODBC connection but checking at http://www.connectionstrings.com/mysql
under "MySQL Connector/ODBC 5.1" and comparing to your connection string you specified "DBQ=" while in the example is used "Database="

So connection string can looks like
"Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3;"

Try to change it and see if it makes any difference.

Eros

[JohnP]

Eros,

Very many thanks for your prompt response...very impressive!

I tried out your suggested connection string and .... it worked right away !! :occasion:

You have opened up a whole new world to me now.
I can now explore my data in a much more flexible way than using just SQL alone.

I am really grateful.
Long live thinBASIC and Eros!

Thank you

JohnP

[ErosOlmi]

Great.
I just guessed :D