MySQL - ODBC Connection [Tutorial] Help needed

[Dwza]

Hi, i tried to establish a connection to a MySQL DB... but it always fails and i hoped someone in here could give me something like a tutorial

What i already did:
Installes MySQL-ODBC-Driver 5.2 Ansi/Unicode
I created a MySQL DB called: Centura
Added a User...
username: gupta
password: 123456
entered all informations in the mysql-odbc-connector (MOC) to user and system and tested the connection. MOC sayes the connection was successful.
no i want to test the connection in my app.
included a library with a function called mysql.init (that passes the login informations) and a function called connect that calls the mysql_real_connect with the informations from init. but it always says the connection was not possible...
is it so difficult tu set up a mysql connection ?

i will try to update my post here if i get some useful informations so i can create a tutorial for people who has the same problems.

[clemdoc]

Hi!

Assuming that you want to connect from a TD App (your post doesn't mention this explicitly), you can just use SqlConnect.
First off all, you need to load the ODBC-Router (sqlodb32) in your SQL.INI. It needs to be loaded before sqlws32, so that could look like this:

Code: Select all

[win32client.dll]
;ODBC
comdll=sqlodb32
;Native SQLBase
comdll=sqlws32

Then, you need to inform the ODBC-Router about your databases. This is done also in SQL.INI (section odbcrtr)

Code: Select all

[odbcrtr]
remotedbname=<your_db_name_in_td>,DSN=<your_dsn_name>,server=<your_mysql_server>

<your_db_name_in_td> is a name you can pick freely (more or less). This will be the name to use as SqlDatabase.
<your_dsn_name> is the name of your ODBC-DSN.
<your_mysql_server> is the address of your mysql server.

Then, you can connect from a TD application by setting the usual Sql-Variables and calling SqlConnect:

Code: Select all

Set SqlDatabase = <your_db_name_in_td>
Set SqlUser = "gupta"
Set SqlPassword = "123456"
Set bConnected = SqlConnect( hMySql )

That should be all. Use SqlPrepare[AndExecute] etc. on hMySql and you should be all set.
Also have a look at the documentation ccod.pdf delivered with TD.

hth, clem

[Dwza]

it would be fine if it work like this but in my case it sadly doesn't

My Setups:

running:
OS: windows 7
Apache: xampp 1.8.1
IDE: TD6.2

Created:
DB: Centura
Table: Test (with 3 fields, filled with 2 rows data)
User: gupta
Password: 123456
Permissions: ALL PRIVILEGS

Adds to sql.ini:
[win32client.dll]
;ODBC
comdll=sqlodb32
comdll=sqlws32
;comdll=sqlapipe


[odbcrtr]
odbctrace=off
odbctracefile=sql.log
remotedbname=Centura,DSN=A_MySQL_Centura,server=localhost

Added MySQL-ODBC Driver to MyODBCConnector:
User-DNS
Name: A_MySQL_Centura | Driver: MySQL ODBC 5.2 ANSI Driver Connection successful
Name: U_MySQL_Centura | Driver: MySQL ODBC 5.2 Unicode Driver Connection successful

System-DNS
Name: A_MySQL_Centura | Driver: MySQL ODBC 5.2 ANSI Driver Connection successful
Name: U_MySQL_Centura | Driver: MySQL ODBC 5.2 Unicode Driver Connection successful

Source:
Main-Window

Code: Select all

On SAM_CreateComplete
   ! ODBC-Data
   Set SqlDatabase = "centrua"
   Set SqlUser = "gupta"
   Set SqlPassword = "123456"

Button-Click

Code: Select all

On SAM_Click
   If SqlConnect( MySQL_handle )
      ! (connection established)
      Call DebugMessage( "Verbindung wurde hergestellt" )
   Else
      ! (connection failed)
      Call DebugMessage( "Verbindung leider fehlgeschlagen"

All what i get is connection failed and sqlbase window pops up and says:
Centura/gupta
cannot open database
number: 401
Reason:

FOR SQLBASE: The specified database cannot be found. SQLBase cannot find the file named "x:\dbdir\dbname\dbname.DBS" where
x:\dbdir is either the default, c:\SQLBASE, or modified with the DBDIR SQL.INI configuration keyword.
In a multiuser network configuration, this error indicates that your network is working correctly, but the database system was unable to
locate the specified database filename.

FOR NON-SQLBASE DATABASES: This problem can also occur with a SQLGateway when leaving out the protocol type in the SERVERNAME
parameter that the client uses to communicate with the gateway (like SQLNBIOS). For example,

SERVERNAME=SERVER33,SQLQUEUE
DBNAME=DB2DBMS, SQLQUEUE, SQLNBIOS

will nott allow a remote client process (using SQLNBIOS on the LAN to communicate with the SQLGateway machine) to connect to the SQLGateway machine.

For SPX connectivity from DOS or MS Windows to a Unixware SQLBase Server check for the omission of the "serverpath=..." parameter in the SQL.INI file under the client section.


Remedy: Verify the database file exists. The default drive letter and dbdirname is c:\SQLBASE unless overridden with a DBDIR SQL.INI
configuration keyword parameter. Verify the DBDIR keyword is not missing or pointing to a wrong database directory. Verify
the DBNAME keyword is specified for the named database. Verify the SERVER keyword is not missing or conflicting with other
network server names. In your CONFIG.SYS file, verify at least 40 files set with the FILES=40 parameter. If the server was
being initialized while the connection was tried, retry the connection after the server has initialized. If all of the
above fails, try using a different database name or try connecting to the database in single user mode at the same
machine. If you can connect with a local engine it probably indicates a network configuration error exists. If you can
connect with a new database name it probably indicates a previously named database was never properly initialized.

Option to Continue or Stop DB

[clemdoc]

Set SqlDatabase = "centrua"

Typo?

[Dwza]

Set SqlDatabase = "centrua"

Typo?

the text wasn't c&p i wrote it by hand... so in my source its alright.
by the time i have established a connection to the db
since that a have new problems like: i would like to have the selection result in an array and stuff like this
i tried to include a dll and use some mysql functions... than i won't get any connection any more...
still practicing

[clemdoc]

since that a have new problems like: i would like to have the selection result in an array and stuff like this

Off the top of my head (and this is normal stuff, not related to MySQL at all):

Code: Select all

Set nCount = 0 
Call SqlConnect( hMySql )
Set sSql = "select ID, TEXT into :naID[nCount], :saText[nCount] from TESTTABLE"
Call SqlPrepareAndExecute( hMySql , sSql )
While SqlFetchNext( hMySql , nFetch )
  If NOT ( nFetch = FETCH_Ok )
    Break
  Else
    Set nCount = nCount + 1

MySql is not that much different from SqlBase in terms of what you can do in TD. There are pitfalls with log bind variables (maybe have a look at SqlSetLongBindDatatype) and I think I had some problems with inserting integers but basically, you can treat it like every other DB connection.

hth, clem

[fl91]

by the time i have established a connection to the db

what did you do to make it work ?

[Dwza]

sorry for posting late. by the time i don't know anymore but there are samples you can find by google. some kind of pdf files