Wiki Home

VFP my SQL Linux P 1

Namespace: WIN_COM_API
Jason Nance and William Sanders will be presenting two WNL sessions on Client/Server application development using VFP and Linux-hosted FREE SQL databases. Part 1 [2001/02/14] is VFP and mySQL. Part 2 [2001/02/21] is VFP and Post Gre Sql.
We'll be showing client/server application development techniques with VFP and My Sql. Discussion of sqltables() and sqlcolumns(), some simple class libraries to transform vfp tables into My Sql tables, a bit on identity keys, sql-pass-through techniques, remote views, etc.. Please get yourself one of the database engines and the odbc driver in our links section first.
Links section:
download site for My Sql: to get a database and the MyODBC drivers.
odbc things:
  • Just mark the 'Don't optimize column width' column in the MyODBC connect screen.
  • If you don't have the above marked, then MyODBC will return the QUERY result column width instead of the column width, which probably confuses VFP.
    It works great, but there are a few little gotchas to watch for. First off, no Logical fields for the FoxPro tables. Not hard to program around unless your trying to integrate with lots of pre-existing code.

    You can just use the ENUM type instead. It takes a little bit of hacking, but not biggie. -- j
    Tim Says: when using VFP and SQL there are special keywords for logical types... ie true is '.T.' (sans quotes) or thereabouts, and for dates use {} braces around the values.

    Get some release notes.
    Get the feature list.

    [20:59] {CindyWinegarden} Tonight's chat is Client / Server with VFP and My Sql

    [21:00] {CindyWinegarden} Given by Bill Sanders and Jason Nance.

    [21:00] {CindyWinegarden} The My Sql database is downloadable from and there are Windows ODBC drivers for My Sql at

    [21:00] {CindyWinegarden} Bill Sanders has over 12 years of experience in micro-computer technology and has spent his time designing hybrid networks, writing code in various xBase languages and integrating xBase systems into fax and email retrieval systems. Currently Bill is involved at the Electronic Filing Group with marketing, technical needs analysis, and xBase development. He was the principal architect for the Electronic Filing Software, and is currently designing the WWW versions of the software. Bill was also one of the Talon geeks from Eagle.

    [21:03] {BillSanders} Greetings to the room...

    [21:03] {CharlieBlakey} Hi

    [21:03] {BillSanders} I'd like you to meet my cohort Mr. Jason Nance...

    [21:03] {JasonNance} Hello

    [21:04] {BillSanders} Jason is a mondo cool linux god who puts out My Sql database systems...

    [21:05] {BillSanders} I've been working with VFP and My Sql for a bit now, but i've been doing loads of stuff with vfp and Ms Sql, Oracle, informix....

    [21:06] {JasonNance} Jason Nance's specialization area is Linux and Network Security. Since 1997 he has been working in Linux to provide secure solutions to businesses without the hefty licensing costs. Some of his strong areas include networking, file servering (Samba), firewall/IP masquerading, VPN - Virtual Private Networking, web server (Apache), and sql server (MySQL). He enjoys writing Visual FoxPro and website front-ends on top of MySQL servers.

    [21:06] {BillSanders} just in case - y'all wanna make sure that you at least have the odbc drivers for My Sql - there's links to it in the wiki site...

    [21:06] {BillSanders}

    [21:07] {BillSanders} one of the cool things I've learned over the past year or so is how to sling stuff with sql pass through technologies...

    [21:07] {CindyWinegarden} The My Sql database is downloadable from and there are Windows ODBC drivers for My Sql at

    [21:07] {BillSanders} the big thing usually is to have some kind of odbc driver available that handles the talking between YOUR workstation and the BACKEND database server.

    [21:08] {BillSanders} there are odbc drivers for Ms SQL, oracle, et. al...

    [21:08] {BillSanders} a few years back the some developers that were using My Sql started to experiment with the 16 bit MS odbc framework

    [21:08] {BillSanders} and they came up with something that worked, sorta...

    [21:08] {JasonNance} yeah... sorta...

    [21:09] {JasonNance} Since then MyODBC as it is know, has evolved greatly.

    [21:09] {BillSanders} currently the odbc driver for My Sql is fairly stable, and it LOOKS like it's 32 bit - or at least the 32 bit odbc manager for Win9*/NT will see it as 32 bit

    [21:10] {BillSanders} there was a lot of other development work going on over the past few years too...

    [21:10] {JasonNance} For all practical purposes, it's 32-bit.

    [21:10] {BillSanders} the My Sql database itself became cross platform - other O/S's could now run it...

    [21:10] {BillSanders} different variants of Unix can run it too :)

    [21:10] {JasonNance} Even Win32.

    [21:11] {BillSanders} One of the big reasons I look at My Sql is due to purchase cost

    [21:11] {BillSanders} ya just can't get any cheaper than FREE

    [21:11] {JasonNance} And speed.

    [21:11] {DenisChasse} How can you compare its speed with SQL Server?

    [21:11] {JasonNance} With tables that have less than 500,000 records, you can't hardly beat MySQL

    [21:11] {BillSanders} the 2nd reason I look at it is the LICENSING COST PER USER seat.... there AIN'T ONE..

    [21:11] {JasonNance} Over 500,000, it starts to lack a bit.

    [21:13] {BillSanders} so if you are just starting to jump into client/server app dev and don't much $$ floating around, or if MSDE just doesn't do it for you - My Sql is an inexpensive way to go to help with your learning curve for Client/Server app development.

    [21:13] {JasonNance} Another great feature about MySQL is it's permission system.

    [21:13] {JasonNance} You can assign permissions based on host, user, database....

    [21:13] {BillSanders} and - there are many many many many web servers out there that serve up data to web pages using My Sql [but we won't talk too much about that here]

    [21:14] {JasonNance} Columns, function, even tables

    [21:14] {JasonNance} And all permissions are stored in a database.

    [21:15] {JasonNance} So it is easy to backup and/or replicate them.

    [21:15] {BillSanders} ok - quick show of hands - how many of you are connected to the internet on your win9X/NT box and can setup or have setup the My Sql odbc driver?

    [21:16] {JasonNance} I think everyone here has an internet connection....

    [21:16] {JasonNance} Maybe...

    [21:16] {BobLee} me

    [21:16] {VinceTeachout} Set it up, but don't know how to run it. Duh.

    [21:16] {BillSanders} ya - but there may be some linux geeks doing term to...

    [21:16] {DenisChasse} I can't set it up. Insufficient mememory error message :-(

    [21:17] {JasonNance} doh.

    [21:17] {BillSanders} Denis - be careful - one of the caveats here is to have at least MDAC 2.1 installed on your WIN boxen

    [21:17] {JasonNance} On the ODBC or server?

    [21:17] {BillSanders} if you don't have that, then you won't see mondo cool 32 bit driver stuff on your box

    [21:18] {BillSanders} but, for what it's worth, My Sql does come with a 16 bit odbc manager, just in case you have NOTHING

    [21:19] {JasonNance} Alright. So do we need to go through how to setup the DSN for MySQL?

    [21:19] {BillSanders} Jason - sounds like we have a quorum so to do :)

    [21:19] {JasonNance} Alright. Here we go.

    [21:20] {JasonNance} During the install, you ahve the option to setup the DSN, if you did great, if you didn't, that's fine too.

    [21:20] {JasonNance} Everyone head to the control panel.

    [21:20] {VinceTeachout} there

    [21:20] {JasonNance} For you Win9x/NT/ME people, open up the ODBC panel

    [21:21] {JasonNance} For you Win2k people, the ODBC panel is inside of Administrative tools.

    [21:21] {JasonNance} Click on ADD

    [21:21] {JasonNance} Then select MySQL. It'll be near (or at) the bottom.

    [21:21] {JasonNance} If it isn't there, you didn't run the install for the ODBC.

    [21:22] {BillSanders} be sure to unzip that first, if you have not done so already :)

    [21:22] {BillSanders} then click on 'setup' and watch er go!

    [21:22] {JasonNance} You will be presented with a window asking for the DSN, host, database name, username, password, port, and a ton of other options.

    [21:23] {JasonNance} The DSN can be whatever you want. Just remember what you pick.

    [21:23] {JasonNance} I'm calling my DSN 'mind'

    [21:23] {JasonNance} you'll know why in a sec (or if you read the web page)

    [21:23] {JasonNance} the host is (note: it is no longer up.)

    [21:24] {BillSanders} notice that the tcp/ip PORT NUMBER will default to 3306. MSSQL slings stuff on port 1433

    [21:25] {JasonNance} we are using the default port

    [21:25] {BillSanders} you can leave that part blank for now :)

    [21:25] {JasonNance} and you don't need to run and SQL commands on connect (leave blank)

    [21:25] {BillSanders} DO CLICK ON 'Don't optimize column width' or you will see garbage come back

    [21:26] {JasonNance} then that's it

    [21:26] {BillSanders} All of the work we are doing here tonight is based on a TCP/IP protocol. HOWEVER!!

    [21:26] {JasonNance} click okay and you are golden.

    [21:26] {JasonNance} (UDP)

    [21:27] {BillSanders} if you install the NT version of this software and don't use TCP/IP - you will want to use 'FORCE USE OF NAMED PIPES'

    [21:27] {BillSanders} but don't use it for jason's dsn settings - it will barf and you won't go anywhere..

    [21:27] {JasonNance} BSOD

    [21:27] {JasonNance} =)

    [21:27] {JasonNance} okay, not really, but close.

    [21:27] {JasonNance} Alright. Do are there any DSN questions so far?

    [21:28] {JasonNance} Everyone type their DSN in here.

    [21:28] {JasonNance} =)

    [21:28] {VinceTeachout} MyODBCTest

    [21:28] {BobLee} no.... only that I want to use DSN less connection for applications..

    [21:28] {BillSanders} mine is called 'JASON'

    [21:28] {CharlieBlakey} MySql_ut

    [21:29] {MarkusVoellmy} "mySQLLecture"

    [21:29] {JasonNance} alrighty.

    [21:29] {BillSanders} Bob - that will certainly work... you want to use a connection string in a database container? or just a connection string?

    [21:29] {JasonNance} You may feel free to connect to this server during the lecture, and for a little while afterwards.

    [21:29] {BobLee} Connection String. Seems more programmable that way to me..

    [21:29] {JasonNance} Then it will be closed.

    [21:29] {JasonNance} If you wish to test MySQL further, contact me.

    [21:30] {JasonNance} I will provide you with a temporary server for testing.

    [21:30] {BillSanders} ok Bob - I promise to put up the connection string info tommorrow night on the wiki sight, ok dokie??

    [21:30] {BobLee} :)

    [21:30] {JasonNance} actually, it's on the wiki now.

    [21:30] {JasonNance}

    [21:31] {JasonNance} So now, everyone go into VFP

    [21:31] {JasonNance} grab your command window

    [21:31] {JasonNance} and type:

    [21:31] {JasonNance} oCon = NEWOBJECT( 'ADODB.Connection' )

    [21:31] {JasonNance} then, type:

    [21:32] {JasonNance} oCon.Open( 'yourdsn' )

    [21:32] {JasonNance} Anyone get an error?

    [21:33] {JasonNance} good.

    [21:33] {JasonNance} heh

    [21:33] {JasonNance} From here you have several options.

    [21:33] {JasonNance} Truthfully, you have several options when it comes to connecting, but... yeah...

    [21:33] {VinceTeachout} I got an ole exception password.. :-(

    [21:34] {JasonNance} Now, oCon is your connection object to the MySQL server.

    [21:34] {BillSanders} ok - I'm gonna talk about using the dsn you just created to do some stuff, so please destroy your ADODB connection

    [21:34] {BillSanders} [my apologies for our confusion :)]

    [21:34] {BillSanders} ok here goes a fun one....

    [21:34] {BillSanders} key this in in your command windows :

    [21:34] {BillSanders} lha = sqlconnect('yourdsnname','UTTest','9NotHere9')

    [21:35] {BillSanders} ? lha

    [21:35] {JasonNance} by the way

    [21:35] {BillSanders} if everything worked right, you should have a POSITIVE value for lha

    [21:35] {JasonNance} a clean exit of and ADO connection goes like this:

    [21:35] {JasonNance} oCon.Close

    [21:35] {JasonNance} release oCon

    [21:35] {BillSanders} lha = sqlconnect('yourdsnname','UTTEST','9NotHere9')

    [21:35] {JasonNance} That was the ADO way of connecting. Which is a PITA.

    [21:36] {BillSanders} or try that one if you dinna get a positive value for lha

    [21:36] {JasonNance} Since you stored the uname and pass in the DSN, you don't need to type them here.

    [21:36] {BillSanders} Jason - what Bob Lee was referring to was a connection string, which sometimes isn't ADO, btw :)

    [21:36] {JasonNance} lha = sqlconnect( 'DSN' ) works.

    [21:37] {JasonNance} ah

    [21:37] {BillSanders} ya - i include them for completeness :) old habits die tommorrow :)

    [21:37] {JasonNance} i'll stop procrastnating tomorrow

    [21:37] {BillSanders} I've put up a table on jason's box.. called d_import...

    [21:37] {BillSanders} but assume for the moment YOU don't know anything about the database domain called UTTest...

    [21:37] {BillSanders} and you do want to know something about it...

    [21:37] {BillSanders} issue this command:

    [21:37] {JasonNance} like the table names

    [21:38] {BillSanders} =sqltables(lha)

    [21:38] {BillSanders} and then study the resulting cursor....

    [21:38] {JasonNance} hit brow when you do that

    [21:38] {BillSanders} browse that baby...

    [21:38] {BobLee} works this way only I put my username and password in the DSN.

    [21:39] {BillSanders} Bob Lee - ya - there's an option that is set to FORCE the username/password login - it's a small PITA to change, but it IS CHANGEABLE.

    [21:39] {BillSanders} so if you are browsing :) you see a table name in there...

    [21:39] {BillSanders} if we had loaded that db up with loads of stuff - it would have many more tables listed in the cursor.

    [21:39] {BillSanders} anyone NOT got a cursor result from the sqltables command?

    [21:40] {JasonNance} good...

    [21:40] {BillSanders} now lets try another sql_ command....

    [21:41] {BillSanders} close the browse window...

    [21:41] {BillSanders} issue:

    [21:41] {BillSanders} =sqlcolumns(lha,'d_import','NATIVE')

    [21:41] {BillSanders} and you get another resultant cursor that has the column names of the table 'd_import'

    [21:42] {BillSanders} it also has the column properties - data type, length, nullable, etc...

    [21:42] {JasonNance} NOTE:

    [21:42] {JasonNance} MySQL uses VarChar and Char

    [21:42] {JasonNance} But...

    [21:43] {JasonNance} It only creates a Char if your field length is 1

    [21:43] {JasonNance} anything more than that, you get VarChar

    [21:43] {JasonNance} which is smart anyway.

    [21:43] {JasonNance} so enjoy it.

    [21:43] {JasonNance} so make sure you ALLTRIM those strings =)

    [21:44] {BobLee} having troublegettin=sqltables() to work.

    [21:44] {JasonNance} error?

    [21:44] {BillSanders} hokay - anyone here not get a resultant cursor after issuing the sqltables to work?

    [21:44] {BillSanders} Bob - what's the value of lha?? issue '? lha' from the command window

    [21:44] {BobLee} lots, function name missing. looks like I need the connection handle.

    [21:45] {BillSanders} bob - do this one really quick - lha=sqlconnect('yourdsnname','UTTEST','9NotHere9')

    [21:45] {BillSanders} and let me know what the value of lha is..

    [21:45] {BillSanders} bob - did you set up the system dsn, btw ?

    [21:46] {BobLee} oops missed one lineof the chat sorry... I see my error.

    [21:46] {BillSanders} okdokie.. Bob has a connection handle from his workstation to WAY OVER THE WORLD to Jason's linux server boxen, serving up My Sql...

    [21:46] {VinceTeachout} FINALLY. Played catch-up - Case error in user ID. Got both cursors ok.

    [21:47] {BillSanders} yeeeeeee haaaaaaaa!!!

    [21:47] {BillSanders} oh! yes :) most of that stuff for My Sql IS case sEnSiTiVe...

    [21:47] {BobLee} got it.. working fine now. forget to get the connectio handle ..

    [21:47] {JasonNance} that's a unix/linux thing

    [21:48] {VinceTeachout} Love it. Now, if only I wasn't dyslexic... :-)

    [21:48] {BillSanders} ok dokie.. the connection handle is your friend.. you need to have that baby around for the rest of our chat...

    [21:48] {DenisChasse} You say most stuff is case sensitive. What is not case sensitive?

    [21:48] {JasonNance} numbers

    [21:48] {DenisChasse} :-D

    [21:48] {BillSanders} now we are gonna issue a simple select command... here goes...

    [21:48] {JasonNance} and keywords

    [21:48] {BillSanders} =sqlexec(lha,"select * from d_import","jason1")

    [21:48] {JasonNance} SELECT == Select == select

    [21:49] {VinceTeachout} I'm good.

    [21:49] {BobLee} so far so good.

    [21:49] {JasonNance} the ADO way to do that:

    [21:49] {BillSanders} oh!! forgot to mention this - you all will grin :) the box i am using RIGHT now to load and test jason's server only runs VFP5.0 - so all of this sql-pass-through stuff works with VFP 5.0 too :)

    [21:50] {JasonNance} loRs = oCon.Execute( 'Select .... ' )

    [21:50] {BillSanders} now you have a cursor called jason1 with 501 records.. yes?

    [21:50] {JasonNance} ADO returns record sets, not cursors.

    [21:50] {VinceTeachout} yup. Didn't like the ADO stuff, but I don't care.

    [21:50] {CharlieBlakey} Still waiting

    [21:50] {JasonNance} ADO is a PITA

    [21:51] {BillSanders} Charlie - it might take a bit, depending on your line speed...

    [21:51] {JasonNance} By the way... I am watching all of you.... =)

    [21:51] {BillSanders} eek

    [21:51] {JasonNance} Yeah, someone had to put 501 records in.

    [21:51] {CharlieBlakey} Did you see that!

    [21:52] {BillSanders} charlie - you about done?? got you a cursor???

    [21:52] {CharlieBlakey} Not yet

    [21:52] {BillSanders} ah -- charlie - is there a wait window with numbers a rolling?

    [21:52] {JasonNance} to give you an idea of how efficient MySQL is...

    [21:52] {JasonNance} the machien you are connected to is a PIII 450

    [21:52] {VinceTeachout} What's the deal with it apparently letting me change data ( a refresh showed it didn't). Shouldn't I have gotten a "Can't update cursor" message?

    [21:52] {CharlieBlakey} Here it is

    [21:53] {JasonNance} when all of you connected and got data, the load average stayed at zero

    [21:53] {BillSanders} Vince - nope... but it really won't 'COMMIT' the changes to the db just yet...

    [21:53] {VinceTeachout} ok

    [21:53] {JasonNance} this is S-P-T

    [21:53] {BillSanders} charlie - mondo cool, sir...

    [21:53] {VinceTeachout} s-p-t?

    [21:53] {BillSanders} hokay....

    [21:54] {JasonNance} SQL Pass Thru

    [21:54] {BillSanders} s-p-t = sql pass through ..

    [21:54] {JasonNance} kinda like EPT, only you hope for a + instead of a -

    [21:54] {VinceTeachout} klar. gracci

    [21:54] {BillSanders} ok dokie.... let's try a table creation....

    [21:55] {BillSanders} oh hang on a sec - lemme put in a shameless plug as to WHY you would want to have a setup like this....

    [21:55] {BillSanders} say you have a client that sells things on the net and they need an invoicing program to print invoices...

    [21:56] {BillSanders} RAD with VFP and the report designer all the way.. you make them an executable with the reports and the associated programs, and BOOM! you're done...

    [21:56] {BillSanders} course - there COULD BE a lot more to it, but you get the general idea...

    [21:57] {BillSanders} report generation to laser is a PITA - you gotta have a lot more tools to do it properly in Linux...

    [21:57] {BillSanders} but you already have the tool to do it with VFP :)

    [21:57] {BillSanders} hokay... on to table creation...

    [21:58] {BillSanders} the concept here is that you are issuing a create table command with the column names and column data types....

    [21:58] {BillSanders} YOU make up your own tablename for now :)

    [21:58] {BillSanders} here goes...

    [21:58] {JasonNance} the MySQL column types that is...

    [21:58] {JasonNance} there are some differences

    [21:59] {JasonNance} the biggest being... no logical type

    [21:59] {MarkusVoellmy} BIT?

    [21:59] {VinceTeachout} char?

    [21:59] {JasonNance} well, use whatever you want. but you stil have to do some processing.

    [22:00] {JasonNance} I use ENUM('True','False') or ('Yes','No')

    [22:00] {JasonNance} you can't insert a logical type directly into an enum field.

    [22:00] {JasonNance} or char... i think...

    [22:01] {BillSanders} =sqlexec(lha,"create table yourtablename (cdata1 varchar(12), cname varchar(20), ivar int, caddr1 varchar(25), caddr2 varchar(25), ccity varchar(20), cstate varchar(2), czip varchar(9) )","")

    [22:01] {BillSanders} and if you wanted to capture the pass/fail state of the table creation - you could have keyed in:

    [22:01] {BillSanders} liresult=sqlexec(lha,"create table yourtablename (cdata1 varchar(12), cname varchar(20), ivar int, caddr1 varchar(25), caddr2 varchar(25), ccity varchar(20), cstate varchar(2), czip varchar(9) )","")

    [22:02] {SaroshWadia} thanks

    [22:02] {BillSanders} and check the value of liresult

    [22:02] {VinceTeachout} So, we just created a bunch of tables on YOUR server?

    [22:02] {BillSanders} vince - yup :) they're all on Jason's server now...

    [22:02] {BillSanders} ok dokie...

    [22:02] {BillSanders} time for a quick sqltables now...

    [22:03] {VinceTeachout} I have visions of a patient dying in a hospital because a table disappeared...

    [22:03] {BillSanders} ok - there are 3 tables in the db now...

    [22:03] {BillSanders} anyone else a key pounding ?

    [22:03] {BobLee} using my own My Sql server tonight for the test.

    [22:03] {BillSanders} vince - hmmmmm we haven't talked about security at all, but it's IN THERE...

    [22:04] {BillSanders} ok - up to 4 tables now..

    [22:04] {BillSanders} cool...

    [22:04] {BobLee} what about 'upsizing'... is there any good conversation tools out there. ?

    [22:04] {BillSanders} now - here's a good test....

    [22:04] {JasonNance} check out the Wiki Upsizing wizard

    [22:04] {BillSanders} you have the table created - but there's no data in it... and that's ok :)

    [22:04] {BillSanders} jason :)

    [22:04] {BillSanders} issue:

    [22:05] {BillSanders} =sqlexec(lha,"select * from mytablename","ccurs")

    [22:05] {BillSanders} and ccurs, though empty - will have some info that you can use...

    [22:05] {VinceTeachout} HA! beat you to it!

    [22:05] {BillSanders} you can browse it.. of course, it's empty..

    [22:06] {BillSanders} but you can also issue "list structure" at the command prompt.

    [22:06] {BillSanders} and remember - you each made those commands travel WIDE AND FAR across the internet, just to see the resultant cursor..

    [22:07] {VinceTeachout} On the Unix Side, what kind of tables are these? Not DBF, I'm guessing?

    [22:07] {BillSanders} ok... quick break time for questions///

    [22:07] {JasonNance} nope.

    [22:07] {JasonNance} they are MySQL tables. =)

    [22:07] {BillSanders} Vince - they are My Sql tables...

    [22:07] {VinceTeachout} Doh. Right. Guess I need to find the mysql docs...

    [22:07] {VinceTeachout} Hence the need for odbc. duh.

    [22:08] {JasonNance}

    [22:08] {JasonNance} there is a VFP ODBC driver. =)

    [22:08] {BillSanders} Ah - documentation :) there are sets of documentation available on the net.... see the url jason just referenced seconds ago :)

    [22:08] {JasonNance} Also, O'Reilly published a wonderful book

    [22:08] {VinceTeachout} Went there last night, Grabbed the full install, but only installed it moments before this meeting.

    [22:08] {BillSanders} ah - jason - good point - and that moves data INTO mysql from the opposite direction :)

    [22:08] {JasonNance} MySQL & mSQL

    [22:09] {JasonNance} Randy Jay Yarger, George Reese & Tim King

    [22:09] {JasonNance} I have the book withISBN if anyone wants it.

    [22:09] {BillSanders} ok dokie... any questions so far?? anyone having major problems??

    [22:10] {BillSanders} anyone see any new implementations of remote data manipulation??

    [22:10] {BillSanders} I hope Jason and myself have opened your mind a bit to using My Sql...

    [22:11] {BobLee} Many.

    [22:11] {JasonNance} and i hope that bit is a 1

    [22:11] {VinceTeachout} Definitely, if you have a client using My Sql

    [22:11] {BillSanders} I have some questions that were mailed in from Evan Delay from yesterday...

    [22:11] {BobLee} thats not necessary.

    [22:11] {JasonNance} you could always pose MySQL as a solution to a small business with no budget.

    [22:11] {BobLee} My Sql is now really simple to install.

    [22:12] {BillSanders} this was his first one: Why would you choose MySQL over MS SQL Server or Oracle? Are there any

    [22:12] {BillSanders} feature or performance advantages?

    [22:12] {BillSanders} my answer typically starts with money, or the clients' lack thereof, but he did touch on a coupla things....

    [22:12] {MarkusVoellmy} because you get some admin tools for free ;)

    [22:13] {JasonNance} There is a bigger perfomance issue here.....

    [22:13] {BillSanders} Ms Sql will SCALE upwards , to a point...

    [22:13] {BillSanders} but usually the performance issues that are brought into play with MS SQL Server and Oracle are usually solved by throwing BIGGER IRON at the datacenter.

    [22:14] {BobLee} you can buy a server appliance for 1100 bucks, with Mysql installed, or lease space on various ISP's web sites with My Sql already running.

    [22:14] {BillSanders} and you will see longer query times with My Sql..

    [22:14] {BillSanders} but! as Bob Lee points out - ISP's running Linux will almost always have support for My Sql in their server farms...

    [22:14] {RodRepp} Say you already had a requirement for Oracle because a third party vendor developed on it, and used some collaboritve pricing, would you still spin off MySQL for in house apps or just add databases into the Oracle?

    [22:14] {BillSanders} Oracle - you have to colocate your own box..

    [22:15] {JasonNance} And... If you are running a data driven website... MySQL loves PHP (and vice versa)

    [22:15] {BillSanders} RedRepp - the answer is - it depends, usually on your management - but you can develop your db schema with My Sql then migrate it into oracle.

    [22:16] {JasonNance} There are two main drawbacks to MySQL

    [22:16] {BillSanders} RodRepp - but if you are using VFP as a front end for client access - that backend won't matter too much, right???

    [22:16] {JasonNance} First... No stored procs.

    [22:16] {JasonNance} Why, you ask?

    [22:17] {JasonNance} Because the SQL RFC doesn't define Stored Procs.

    [22:17] {RodRepp} to the admin holding the machine up it would! :-)

    [22:17] {BobLee} No Transaction support yet.

    [22:17] {BillSanders} yup - no stored procedures - for some folks that really sucks.. other folks don't miss it at all...

    [22:17] {JasonNance} Bob Lee : actually... yes.

    [22:17] {JasonNance} There is now transaction support.

    [22:17] {BillSanders} Bob Lee - ya - there is a group outta Berkely that made transaction support for My Sql

    [22:18] {JasonNance} as long as you have the Berkeley DB 3 libraries installed.

    [22:18] {BillSanders} Jason - what's the 2nd drawback?

    [22:18] {JasonNance} beta transaction support.... hehehe

    [22:18] {CharlieBlakey} Thanks Bill and Jason. Really good lecture. Gota go now unfortunately

    [22:18] {BobLee} Mind if I ask a question about installing a client ?

    [22:18] {JasonNance} i think the transaction support is out of beta now though...

    [22:19] {BillSanders} Bob - sure...

    [22:19] {JasonNance} go for it

    [22:19] {BillSanders} but here's another Evan question:

    [22:19] {BillSanders} ODBC

    [22:19] {BillSanders} - OLEDB

    [22:19] {BillSanders} - ADO

    [22:19] {BillSanders} - SQL Pass Thru

    [22:19] {BillSanders} - stored procedures

    [22:19] {BillSanders} - COM/DCOM

    [22:19] {RodRepp} but yes, the frontend would stay the same to the user either way, I'm just trying to see which would be the "safer" route from the admin's point of view

    [22:19] {BillSanders} are these supported with My Sql??

    [22:19] {BobLee} what do you do to install the odbc ? on a client workstation. Make them download and install it themselve ?

    [22:19] {BillSanders} rodrepp - safe = admin lockdown - and it could be done :)

    [22:19] {JasonNance} and easily

    [22:20] {BillSanders} there is not ole-db support at this time - but there IS odbc support. and ADO support

    [22:20] {BillSanders} we've touched on SQL Pass through, and there ain't no stored procedures :(

    [22:20] {JasonNance} Okay, here's how you install the MyODBC...

    [22:20] {JasonNance} First, you grab the MyODBC zip file (not the self-extractor) from

    [22:21] {BillSanders} com/dcom - you could surely write a vfp com object that pulls stuff in/out of mysql

    [22:21] {JasonNance} There are 2 files in there (2 dlls)

    [22:21] {BobLee} ok

    [22:21] {BillSanders} but!! there's not a native COM interface to the database at all...

    [22:21] {JasonNance} those go in windows\system or system32

    [22:21] {BillSanders} you have to roll your own or switch to PHP for NT

    [22:22] {JasonNance} then, you need to run a little prg.

    [22:22] {JasonNance} that configs the DSN

    [22:22] {VinceTeachout} Have to go. Will catch the remainder on the Logger. Thanks!

    [22:22] {BobLee} you saiying if I put the file in the proper directory, and confirure it, its registarted }?

    [22:23] {BillSanders} Bob Lee - you DON'T have to make them download it... you can bundle it up with YOUR installation set for YOUR program, and then have a post-setup executable that sets up the config settings for YOUR My Sql database :)

    [22:23] {BobLee} thanks..

    [22:23] {JasonNance} It's a DLL, so you don't need to register it.

    [22:23] {RodRepp} well, my question stems from the fact that I'm going to end up with at least 2 software systems that require their own database's, and if I want to develop something in shop, should I bother trying another machine to handle that or just graft into something exisiting

    [22:23] {BobLee} Have you tried Nusphere.? just got it myself, Great package.

    [22:23] {BillSanders} Jason - OMIGOD!! I forgot about that part - Bob - he's right - you don't have to register it...

    [22:24] {JasonNance} The program to register the DSN is only a few lines long...

    [22:24] {BillSanders} Rod - that is a nasty question that usually smells of a capital expenditure [readin your manager signs off on that purchase] so if you want to stay with oracle [which is NOT a bad thing, btw] then stay with oracle.

    [22:25] {BobLee} if you have a smple, I would love to look it over... you know, cut an paste it)... ha

    [22:25] {JasonNance} And I can't seem to find it right now.... grr....

    [22:25] {JasonNance} Yeah, i'll send it to you.

    [22:25] {JasonNance} If you send me an email

    [22:25] {JasonNance}

    [22:25] {JasonNance} Cause I looked forever for how to do it.

    [22:25] {BillSanders} Evan's last question was: Can MySQL output XML natively ?

    [22:25] {BillSanders} and I will defer this one to Jason ..

    [22:26] {JasonNance} the simple answer is.....

    [22:26] {JasonNance} yes! next question!

    [22:26] {BobLee} done.

    [22:26] {BillSanders} Ok - I think we're done...

    [22:26] {MarkusVoellmy} Jason natively? How this?

    [22:26] {BobLee} ha... like that answer... yes.. ? simple as ms-sql server... with a switch. ?

    [22:26] {BillSanders} Jason - r we done???

    [22:27] {BobLee} yea.. getting late. here in NY.

    [22:27] {JasonNance} actually, i don't know. i was just teasing.

    [22:28] {BillSanders} uh - ok - i'll tackle the xml question...

    [22:28] {JasonNance} hahaha

    [22:28] {bbroyles} PMFJI ...Thanks for letting me rubberneck, I learn alot by lurking, and tonights been great...

    [22:28] {BobLee} :)

    [22:28] {CindyWinegarden} Jason and Bill, thanks for coming and speaking tonight. ** applause **

    [22:28] {BobLee} thought I was chatting with Rick Stahl for a minute.

    [22:28] {BillSanders} there are wrapper-like processes that you can build into My Sql - either as external processes OR as part of a re-compile, that WILL allow you to output XML natively...

    [22:28] {BobLee} thanks.. ! a mil.

    [22:28] {CarlosCarrillo} thank you very much

    [22:28] {MarkusVoellmy} Great job from both of you. thx

    [22:29] {BillSanders} but you have to build it in on your own linux box..

    [22:29] {BillSanders} Bob ? wow - which one of us, this time ? :))

    [22:29] {BobLee} no you don't...

    [22:29] {MarkusVoellmy} yep the NT version is somehow limited ;)

    [22:31] {BillSanders} Jason - MONDO THANKS for settup up your boxen :)

    Contributors: William Sanders, Jason Nance, Evan Delay, Cindy Winegarden
    Category Wednesday Night Lectures
  • ( Topic last updated: 2004.02.08 09:27:13 PM )