how to create a simple ODBC database?

[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/smffo...hp?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...
[code=thinbasic]


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

[/code]

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?

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

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