MySQL and Clarion 5.5 EE 
Author Message
 MySQL and Clarion 5.5 EE

I am trying to develope an application that access a database created using
MySQL on a Linux file server.  I can make everything work but, I want to
control the user access authentication to the server and database with out
having them have direct access to the MyODBC interface (I want to keep it
simple).  Clarion accesses the file just fine when I login through the odbc
interface but when I embed string parameters in the Owner line of the data
dictionary, I can't seem to get the dialog to work right.  I have used the
format "DNS=<my dns title>;USER=<username>;PASSWORD=<my password>;" but this
doesn't seem to work right.  I have tried several combinations that I have
read from other web pages where I could find them but nothing that I have
yet tried seems to work.  It is as though Clarion is corrupting the data
string before it gets to the ODBC driver.  The funny thing is that when I
enter the parameters directly into the Owner field and browse the file, the
parameters are accepted, the ODBC screen never appears and the browser
displays the data without any problem.

Can any of you offer any suggestions?

Thank,

Joseph A. Seabolt



Tue, 19 Jul 2005 04:40:26 GMT  
 MySQL and Clarion 5.5 EE
I nominate this for the bestest, longest, most complete, informative and
helpful post so far this year!

Well done Steven :-)


Quote:

> Hi Joseph,

> Create several global variables and put them into the correct places in
the
> dictionary:
> GLO:DriverOptions   Driver property
> GLO:ConnectString   Owner property
> GLO:FileName_xxx   Name property (where xxx is the label/name of the
table)

> (In case you handcode this file, here's how it would look like :

DRIVER('ODBC',GLO:DriverOptions),OWNER(GLO:ConnectString),NAME(GLO:FileName_
Quote:
> xxx)

> GLO:DriverOptions you can use to set all sorts of stuff (look in the
> helpfiles). We use it to tell what type of JOIN we want with MySQL. Heads
> Up: we noticed that the 3.51.xx driver won't join correctly. Perhaps the
> latest driver does better (caching resultsets on the server should work
with
> the latest version as well I heard), we stick with the 2.50 driver for
now.
> Our setting : /JOINTYPE=DB2

> GLO:ConnectString is the one you asked for ;-)
> Possible options to put into this variable are: "Driver", "Server",
> "Database", "Uid", "Pwd" and the "Port".
> Note: there are more options if you want, but these are the ones you
*need*
> to connect.
> So the initialisation for GLO:ConnectString could be something like this :
> GLO:ConnectString =  'driver={{MySQL};server=<ip-address or DNS resolvable
> name>;database=<databasename>;uid=<username>;pwd=<password>;port=3306'
> Note the double accolade "{{" in the Driver part. Clarion normaly uses
this
> character to repeat characters in a string (i.e. MyVar = '#{10}' would
lead
> the MyVar to contain 10 # signs) so you need to tell Clarion to stop doing
> that and this is how.
> A more real world example of a GLO:ConnectString:
> GLO:ConnectString =

'driver={{MySQL};server=24.212.67.33;database=headoffice;uid=master;pwd=tops
Quote:
> ecret;port=3306'

> GLO:FileName_xxx we use to tell each and every table in the dictionary
what
> it's called and more importantly, we tell it this way what database to
use.
> Let's say you have a table called Customer. Normaly you would have to
enter
> "headoffice.customer" in the Name property in the dictionary. But when you
> use a variable you can fill this in there, like this:

GLO:FileName_Customer

- Show quoted text -

Quote:
> =  'headoffice.customer'.
> Nothimg gained you might say, but what if I want to switch database? What
if
> I use a different database than the live one to develop, just so that I
> don't bother the office people? Or what if a customer calls and asks you
to
> see the problem for yourself and don't feel like the drive to see it? Then
> you can load another database name into this field, append the table name
as
> it should be and bingo. Something like this : GLO:FileName_Customer =
> 'anotherdatabase.customer'. Now together with the GLO:ConnectString you
can
> see where I'm going I would think. Supply these values to the variable in
> your program via INI files and you can swith servers, users, ports and
> databases at will. Cool, eh?
> I must admit that when you have lots of tables it's a pian to declare all
> the GLO:FileName variables and to set them at runtime. Enter the
wonderfull
> world of Templates<vbg>! We wrote a template that does all of this
> automaticly and it took me 3 hours to do it (counting the edit in the
> dictionary I had to do on 80 tables to change the table names to these
> variable counterparts). Easy.
> To give you an idea what you _could_ do I've included this template. It's
> part of our template chain (ain't going to send that;-)) so you need to
> create your own template chain or incorporate it into your existing one. I
> would suggest you create a new one though. Oh, and it's Legacy (ok, ok,
> Clarion, sigh) not ABC, so you are either lucky or in for a rewrite<g>.
> Makes a good starting point though,I think.

> Make sure that all fields in all the tables have external names. We tend
to
> do it like this: Suppose that this table called Customer has a prefix
called
> CUS and a field called ID (containing the unique number for that record).
> When accessing this field in Clarion you would write something like this
> CUS:ID = 100. It would be tempting to create an external fieldname like
"ID"
> or perhaps "CUS:ID" just so that it looks exactly the same as in SQL code
as
> in your Clarion code. Njetsky for us. We do use the prefix bet we lose the
> colon. It looks like this "cus_id". Advantages are: easily recognizable
> fieldnames, no fieldname conflicts, etc. We here think it's good practice
> and it has helped us on several occasions where we would otherwise have
> naming conflicts. SQLQueries can be shorter as well because there's not a
> chance in hell a fieldname called "cus_id" would exist twice or more in
the
> same dictionary, Clarion won't allow it, simple. Name the external field
> "id" and you get into trouble if naming conventions are inforced in your
> environment and naming it "cus:id" would lead to possible problems, if not
> with MySQL then another SQL engine might.

> One last thing: lower case, lower case, lower case. Always expect you
MySQL
> engine to run in {*filter*} places and that these environments tend to be case
> sensitive. Database names, Table names and Column names, make 'm all lower
> case, just to be on the safe side.

> --
> Cheers,
> Steven Spierenburg



> > I am trying to develope an application that access a database created
> using
> > MySQL on a Linux file server.  I can make everything work but, I want to
> > control the user access authentication to the server and database with
out
> > having them have direct access to the MyODBC interface (I want to keep
it
> > simple).  Clarion accesses the file just fine when I login through the
> odbc
> > interface but when I embed string parameters in the Owner line of the
data
> > dictionary, I can't seem to get the dialog to work right.  I have used
the
> > format "DNS=<my dns title>;USER=<username>;PASSWORD=<my password>;" but
> this
> > doesn't seem to work right.  I have tried several combinations that I
have
> > read from other web pages where I could find them but nothing that I
have
> > yet tried seems to work.  It is as though Clarion is corrupting the data
> > string before it gets to the ODBC driver.  The funny thing is that when
I
> > enter the parameters directly into the Owner field and browse the file,
> the
> > parameters are accepted, the ODBC screen never appears and the browser
> > displays the data without any problem.

> > Can any of you offer any suggestions?

> > Thank,

> > Joseph A. Seabolt

> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system ( http://www.*-*-*.com/ ).
> Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/03



Tue, 19 Jul 2005 09:01:10 GMT  
 MySQL and Clarion 5.5 EE
Thanks Willie and it's been my pleasure.

--
Cheers,
Steven Spierenburg



Quote:
> I nominate this for the bestest, longest, most complete, informative and
> helpful post so far this year!

> Well done Steven :-)



> > Hi Joseph,

> > Create several global variables and put them into the correct places in
> the
> > dictionary:
> > GLO:DriverOptions   Driver property
> > GLO:ConnectString   Owner property
> > GLO:FileName_xxx   Name property (where xxx is the label/name of the
> table)

> > (In case you handcode this file, here's how it would look like :

DRIVER('ODBC',GLO:DriverOptions),OWNER(GLO:ConnectString),NAME(GLO:FileName_

- Show quoted text -

Quote:
> > xxx)

> > GLO:DriverOptions you can use to set all sorts of stuff (look in the
> > helpfiles). We use it to tell what type of JOIN we want with MySQL.
Heads
> > Up: we noticed that the 3.51.xx driver won't join correctly. Perhaps the
> > latest driver does better (caching resultsets on the server should work
> with
> > the latest version as well I heard), we stick with the 2.50 driver for
> now.
> > Our setting : /JOINTYPE=DB2

> > GLO:ConnectString is the one you asked for ;-)
> > Possible options to put into this variable are: "Driver", "Server",
> > "Database", "Uid", "Pwd" and the "Port".
> > Note: there are more options if you want, but these are the ones you
> *need*
> > to connect.
> > So the initialisation for GLO:ConnectString could be something like this
:
> > GLO:ConnectString =  'driver={{MySQL};server=<ip-address or DNS
resolvable
> > name>;database=<databasename>;uid=<username>;pwd=<password>;port=3306'
> > Note the double accolade "{{" in the Driver part. Clarion normaly uses
> this
> > character to repeat characters in a string (i.e. MyVar = '#{10}' would
> lead
> > the MyVar to contain 10 # signs) so you need to tell Clarion to stop
doing
> > that and this is how.
> > A more real world example of a GLO:ConnectString:
> > GLO:ConnectString =

'driver={{MySQL};server=24.212.67.33;database=headoffice;uid=master;pwd=tops

- Show quoted text -

Quote:
> > ecret;port=3306'

> > GLO:FileName_xxx we use to tell each and every table in the dictionary
> what
> > it's called and more importantly, we tell it this way what database to
> use.
> > Let's say you have a table called Customer. Normaly you would have to
> enter
> > "headoffice.customer" in the Name property in the dictionary. But when
you
> > use a variable you can fill this in there, like this:
> GLO:FileName_Customer
> > =  'headoffice.customer'.
> > Nothimg gained you might say, but what if I want to switch database?
What
> if
> > I use a different database than the live one to develop, just so that I
> > don't bother the office people? Or what if a customer calls and asks you
> to
> > see the problem for yourself and don't feel like the drive to see it?
Then
> > you can load another database name into this field, append the table
name
> as
> > it should be and bingo. Something like this : GLO:FileName_Customer =
> > 'anotherdatabase.customer'. Now together with the GLO:ConnectString you
> can
> > see where I'm going I would think. Supply these values to the variable
in
> > your program via INI files and you can swith servers, users, ports and
> > databases at will. Cool, eh?
> > I must admit that when you have lots of tables it's a pian to declare
all
> > the GLO:FileName variables and to set them at runtime. Enter the
> wonderfull
> > world of Templates<vbg>! We wrote a template that does all of this
> > automaticly and it took me 3 hours to do it (counting the edit in the
> > dictionary I had to do on 80 tables to change the table names to these
> > variable counterparts). Easy.
> > To give you an idea what you _could_ do I've included this template.
It's
> > part of our template chain (ain't going to send that;-)) so you need to
> > create your own template chain or incorporate it into your existing one.
I
> > would suggest you create a new one though. Oh, and it's Legacy (ok, ok,
> > Clarion, sigh) not ABC, so you are either lucky or in for a rewrite<g>.
> > Makes a good starting point though,I think.

> > Make sure that all fields in all the tables have external names. We tend
> to
> > do it like this: Suppose that this table called Customer has a prefix
> called
> > CUS and a field called ID (containing the unique number for that
record).
> > When accessing this field in Clarion you would write something like this
> > CUS:ID = 100. It would be tempting to create an external fieldname like
> "ID"
> > or perhaps "CUS:ID" just so that it looks exactly the same as in SQL
code
> as
> > in your Clarion code. Njetsky for us. We do use the prefix bet we lose
the
> > colon. It looks like this "cus_id". Advantages are: easily recognizable
> > fieldnames, no fieldname conflicts, etc. We here think it's good
practice
> > and it has helped us on several occasions where we would otherwise have
> > naming conflicts. SQLQueries can be shorter as well because there's not
a
> > chance in hell a fieldname called "cus_id" would exist twice or more in
> the
> > same dictionary, Clarion won't allow it, simple. Name the external field
> > "id" and you get into trouble if naming conventions are inforced in your
> > environment and naming it "cus:id" would lead to possible problems, if
not
> > with MySQL then another SQL engine might.

> > One last thing: lower case, lower case, lower case. Always expect you
> MySQL
> > engine to run in {*filter*} places and that these environments tend to be
case
> > sensitive. Database names, Table names and Column names, make 'm all
lower
> > case, just to be on the safe side.

> > --
> > Cheers,
> > Steven Spierenburg



> > > I am trying to develope an application that access a database created
> > using
> > > MySQL on a Linux file server.  I can make everything work but, I want
to
> > > control the user access authentication to the server and database with
> out
> > > having them have direct access to the MyODBC interface (I want to keep
> it
> > > simple).  Clarion accesses the file just fine when I login through the
> > odbc
> > > interface but when I embed string parameters in the Owner line of the
> data
> > > dictionary, I can't seem to get the dialog to work right.  I have used
> the
> > > format "DNS=<my dns title>;USER=<username>;PASSWORD=<my password>;"
but
> > this
> > > doesn't seem to work right.  I have tried several combinations that I
> have
> > > read from other web pages where I could find them but nothing that I
> have
> > > yet tried seems to work.  It is as though Clarion is corrupting the
data
> > > string before it gets to the ODBC driver.  The funny thing is that
when
> I
> > > enter the parameters directly into the Owner field and browse the
file,
> > the
> > > parameters are accepted, the ODBC screen never appears and the browser
> > > displays the data without any problem.

> > > Can any of you offer any suggestions?

> > > Thank,

> > > Joseph A. Seabolt

> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system ( http://www.*-*-*.com/ ).
> > Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/03

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ( http://www.*-*-*.com/ ).
Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/03


Tue, 19 Jul 2005 17:33:42 GMT  
 MySQL and Clarion 5.5 EE
Since we're on the topic.  I am considering switching to MySQL.   I have
about 200 customers.  Most are small 2 to 3 stations.  One is about 20
workstations with a few at 10 workstations.  Currently using Topspeed files
and Clarion templates in 5.5 EE. I had heard that MySQL was free.  But when
I checked their website they say its free if you give away your software.
Should I consider switching????
How difficult is it to support topspeed and MySQL databases on one app????
Thanks for any advise.


Sat, 30 Jul 2005 06:20:33 GMT  
 MySQL and Clarion 5.5 EE
Heavenes Dennis -

Drop by www.icetips.com. There is an SQL area that will give you lots of
information. And there are some templates available that will take your
dictionary and build SQL creation scripts.

You only have to buy licenses if you are going to sell your product
commercially. I purchased one license which lets me run one windows host and
one Linux host.

Licenses are fairly cheap, check back on www.mysql.com.

Also drop by www.capesoft.com , check out FM3 and Replicate.

Hope That Helps.
Enjoy!
Roberto


Quote:
> Since we're on the topic.  I am considering switching to MySQL.   I have
> about 200 customers.  Most are small 2 to 3 stations.  One is about 20
> workstations with a few at 10 workstations.  Currently using Topspeed
files
> and Clarion templates in 5.5 EE. I had heard that MySQL was free.  But
when
> I checked their website they say its free if you give away your software.
> Should I consider switching????
> How difficult is it to support topspeed and MySQL databases on one app????
> Thanks for any advise.



Sat, 30 Jul 2005 06:59:27 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Clarion 5.5 EE using the Clarion Templates - No Take New Selection Embed

2. Error Clarion 5.5 ee and ODBC

3. Clarion 5.5 EE RC2

4. E-Mail in Clarion 5.5 ee

5. Need Serial Number for Clarion 5.5 EE

6. Clarion 5.5 EE not compatible with Windows WinXP

7. Clarion 5.5 EE does not start in ME

8. Clarion 5.5 ee & Internet

9. Graphical problems with CW 5.5 EE beta

 

 
Powered by phpBB® Forum Software