Mysql Database

[westongoodwin]

Here is the connection info that I use to connect to a mysql
database:

SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm =
"ConnectString='DSN=test;UID=test;PWD=test;Provider=MSDataShape.1;
Driver={MySQL ODBC
3.51Driver};Server=192.168.1.254;Database=test'"

The problem I'm having is that I can connect to the database
and input data just fine, but when I try to retrieve data
from the database using a datawindow it only gives me the
first character of each column. Now if I do an SQL statment
in powerscript it will return all the data in each column.
Any suggestions?

[Matthew Chestnut]

Here are some MySQL items to look out for:

MySQL v. 5.0.1 or later required for "create view ..." support

Connection parameters:
DisableBind=1 required so datawindow retrieval works correctly with
retrieval arguments of "long" data type
OJSyntax='ANSI' required so the {oj ... } escape syntax is not used.

"InnoDB" required database type to support transactions

Set "ansi" as MySQL .INI option to use ANSI syntax. Basically, this
means white-space characters won't mess things up, e.g. this SQL
"select count ( *) from nxscuste where 1 =2" will not execute unless
"ansi" mode is chosen.

Added following to pbodb100.ini:
[MySQL]
PBTrimCharColumns='YES'

[westongoodwin]

I am little confused as to what I need to do. Have a few
questions for you.

Where do I configure/place the connection parameters you
gave me?
Where do I tell pb to use the innodb for transactions?
How/Where do I set "ANSI" as MYSQL.ini options?

[giob...@yahoo.gr]

Here is my personal experience about working with mySQL connections:

1.Define on your DSN (Connector/ODBC) as an initial statement (2nd
tabpage) "set session sql_mode='ansi'" (withaout the double quotes).
This will make the stetement select * from "table" equivalent with
select * from `table`. (I don't know after which mySQL version this
works). If this is not possible you can execute the statement on the
open event of your application with execute immediate.

2.Again on Connector/ODBC on 3rd tabpage select "Safe" on tab Flags1
and "Ignore Spaces After Function Name" on Flags3. These could be
entered on ConnectString as well if you add "Options=135168" somewhere
after {DRIVER}

Hope that I helped

[giob...@yahoo.gr]

[westongoodwin]

Thanks