Comments for Martin's MYSQL Blog http://pirringers.com/mysqlblog VFP -> MYSQL migration Thu, 17 Nov 2011 14:57:28 +0000 http://wordpress.org/?v=2.9.2 hourly 1 Comment on Transferring VFP Table Structures to MYSQL by Rainer R. Greim http://pirringers.com/mysqlblog/?p=17&cpage=1#comment-602 Rainer R. Greim Thu, 17 Nov 2011 14:57:28 +0000 http://pirringers.com/mysqlblog/?p=17#comment-602 Hi Martin, great postings thx for that. Im also migrating my vfp container to other backends. And I was searching and trying tools for that. But there is a easier way. Get a Omnis 5 Standard Edition for MySQL ( http://www.Tigerlogic.com) Get the ODBC-driver for FoxpPro Setup a ODBC-DSN for your Foxpro db Start Omnis and setup 2 sessions in the session manager Session 1: odbc to the vfp dsn Session 2: to the Mysql db via MySQL Dam see also : http://www.tigerlogic.com/tigerlogic/omnis/technotes/tnsq0012.jsp Then open both sessions Now drag the vfp tables to the MySql session and do some finetuning Example memo fields shout be varchar, byte a... Index on deleted will not run, delete... RRG http://www.greimconsulting.de/node/116 Hi Martin,
great postings thx for that.
Im also migrating my vfp container to other backends. And I was searching and trying tools for that.
But there is a easier way.

Get a Omnis 5 Standard Edition for MySQL ( http://www.Tigerlogic.com)
Get the ODBC-driver for FoxpPro
Setup a ODBC-DSN for your Foxpro db
Start Omnis and setup 2 sessions in the session manager
Session 1: odbc to the vfp dsn
Session 2: to the Mysql db via MySQL Dam see also : http://www.tigerlogic.com/tigerlogic/omnis/technotes/tnsq0012.jsp
Then open both sessions
Now drag the vfp tables to the MySql session and do some finetuning
Example memo fields shout be varchar, byte a…
Index on deleted will not run, delete…

RRG
http://www.greimconsulting.de/node/116

]]> Comment on Converting and optimizing SQL Statements by George http://pirringers.com/mysqlblog/?p=32&cpage=1#comment-595 George Mon, 07 Nov 2011 09:18:30 +0000 http://pirringers.com/mysqlblog/?p=32#comment-595 Why is this query giving a error message ? select 1 where exists (select * from tablename where condition) If I use select 1 From table name1 where exists(select * from tablename2 where condition) IT'S WORKING but this doesn't help. I don't want a FROM in the first part of the query because I'm importing these from VFP where they are accepted and it's impossible to change them all. I'm using ANSI mode. Could this be the problem ? Thanks. Why is this query giving a error message ?

select 1 where exists (select * from tablename where condition)

If I use
select 1 From table name1 where exists(select * from tablename2 where condition)
IT’S WORKING but this doesn’t help.

I don’t want a FROM in the first part of the query because I’m importing these from VFP where they are accepted and it’s impossible to change them all.

I’m using ANSI mode. Could this be the problem ?

Thanks.

]]> Comment on Using MYSQL to maintain the VFP frontend. by Susana http://pirringers.com/mysqlblog/?p=59&cpage=1#comment-328 Susana Wed, 06 Oct 2010 23:33:38 +0000 http://pirringers.com/mysqlblog/?p=59#comment-328 I have 4 connections created from a class as objects. Every connection open a database. My problem is the fact that I lost connection to one server for some reason, which I have no idea what it is. It could be working fine for days and then I had a lot of problems for the lost connection. I read in some pages about it and I found it was the error 1466. I read that I can test the connection so I used: oException = SQLGETPROP(lnHandleGeneral, "Asynchronous") oException = SQLSETPROP(lnHandleGeneral, "Datasource") But I that creates me more errors and I can't force the connection again... What should I do? I have 4 connections created from a class as objects. Every connection open a database. My problem is the fact that I lost connection to one server for some reason, which I have no idea what it is. It could be working fine for days and then I had a lot of problems for the lost connection. I read in some pages about it and I found it was the error 1466. I read that I can test the connection so I used:

oException = SQLGETPROP(lnHandleGeneral, “Asynchronous”)
oException = SQLSETPROP(lnHandleGeneral, “Datasource”)

But I that creates me more errors and I can’t force the connection again… What should I do?

]]> Comment on First Contact – VFP/MYSQL interaction by Raymond L. http://pirringers.com/mysqlblog/?p=11&cpage=1#comment-327 Raymond L. Sat, 02 Oct 2010 05:02:56 +0000 http://pirringers.com/mysqlblog/?p=11#comment-327 Looks like the error checking function's code above got mangled. Basically, check value returned by SQLSTRINGCONNECT. If less than 1, there's an error. Use AERROR() function to retrieve an array that includes error details. Look up AERROR in VFP's help file for some guidance there if you need it. Once you have that information you can pass it on to the user, and exit. Looks like the error checking function’s code above got mangled. Basically, check value returned by SQLSTRINGCONNECT. If less than 1, there’s an error. Use AERROR() function to retrieve an array that includes error details. Look up AERROR in VFP’s help file for some guidance there if you need it. Once you have that information you can pass it on to the user, and exit.

]]> Comment on First Contact – VFP/MYSQL interaction by Raymond L. http://pirringers.com/mysqlblog/?p=11&cpage=1#comment-326 Raymond L. Sat, 02 Oct 2010 04:57:28 +0000 http://pirringers.com/mysqlblog/?p=11#comment-326 I was fumbling with this myself, and found excellent information about the various ODBC command options you can use with MySQL here: http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html You can use DSN-less connections by setting up and storing a connection string such as: m.cConnStr = "Driver={MySQL ODBC 5.1 Driver};Server=www.yourserver.com;Port=3306;Database=yourdatabase;User=yourmysqluser;Password=yourmysqlpassword;Option=67108864" and the last parameter, "Option" you would set to a number that is the sum of the values of each option you want to enable (enumerated on the above link). Once you have your SQL connection string correct and including the options you want, simply pass that connection string to VFP's SQLSTRINGCONNECT function like so: m.iMySQL = SQLSTRINGCONNECT(m.cConnStr,.t.) As long as the value returned by SQLSTRINGCONNECT is greater than 0, you are successful, and can use that return value as your "connection handle" for subsequent interactions w/MySQL in your VFP program. If you get a return value of 0 (or less?), you should retrieve the error message. I do it like this: m.iMySQL = SQLSTRINGCONNECT(m.cConnStr,.t.) IF m.iMySQL 0 m.iErrorNo = aSQLError[1,1] m.cErrorMsg = aSQLError[1,3] MESSAGEBOX("SQL Error #" + ALLTRIM(STR(m.iErrorNo)) + ":" + CHR(13) + CHR(10) + CHR(13)+CHR(10) + m.cErrorMsg, 16, 'SQL Error') ENDIF RETURN (m.iCnt > 0) ENDFUNC I was fumbling with this myself, and found excellent information about the various ODBC command options you can use with MySQL here:
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

You can use DSN-less connections by setting up and storing a connection string such as:
m.cConnStr = “Driver={MySQL ODBC 5.1 Driver};Server=www.yourserver.com;Port=3306;Database=yourdatabase;User=yourmysqluser;Password=yourmysqlpassword;Option=67108864″

and the last parameter, “Option” you would set to a number that is the sum of the values of each option you want to enable (enumerated on the above link).

Once you have your SQL connection string correct and including the options you want, simply pass that connection string to VFP’s SQLSTRINGCONNECT function like so:
m.iMySQL = SQLSTRINGCONNECT(m.cConnStr,.t.)

As long as the value returned by SQLSTRINGCONNECT is greater than 0, you are successful, and can use that return value as your “connection handle” for subsequent interactions w/MySQL in your VFP program. If you get a return value of 0 (or less?), you should retrieve the error message. I do it like this:

m.iMySQL = SQLSTRINGCONNECT(m.cConnStr,.t.)
IF m.iMySQL 0
m.iErrorNo = aSQLError[1,1]
m.cErrorMsg = aSQLError[1,3]
MESSAGEBOX(“SQL Error #” + ALLTRIM(STR(m.iErrorNo)) + “:” + CHR(13) + CHR(10) + CHR(13)+CHR(10) + m.cErrorMsg, 16, ‘SQL Error’)
ENDIF
RETURN (m.iCnt > 0)
ENDFUNC

]]> Comment on Outgrowing Visual Foxpro by admin http://pirringers.com/mysqlblog/?p=3&cpage=1#comment-323 admin Fri, 20 Aug 2010 13:05:12 +0000 http://pirringers.com/mysqlblog/?p=3#comment-323 if you use sqlexec or sqlprepare you are limited to 256 characters. To work around it put the statement into a string first likes sql = "Update ...." Sqlprepare(test, sql) etc if you use sqlexec or sqlprepare you are limited to 256 characters. To work around it put the statement into a string first likes

sql = “Update ….”
Sqlprepare(test, sql)

etc

]]> Comment on Outgrowing Visual Foxpro by jmason http://pirringers.com/mysqlblog/?p=3&cpage=1#comment-314 jmason Wed, 26 May 2010 19:12:44 +0000 http://pirringers.com/mysqlblog/?p=3#comment-314 Admin (large state agency) created new policy that all would use only MS Office for all tasks. No more FoxPro! I explained that all the systems I had written would not translate to ACCESS. No matter, they said: just rewrite everything. I said, "You dumb bastards rewrite it all, if you ever learn to write." Then went to another agency offering 50% better pay to do less analysis. They sent some chump over 6 months later to get my help on a massive yearly report... I LAUGHED for a WHILE. Admin (large state agency) created new policy that all would use only MS Office for all tasks. No more FoxPro!
I explained that all the systems I had written would not translate to ACCESS.
No matter, they said: just rewrite everything.
I said, “You dumb bastards rewrite it all, if you ever learn to write.” Then went to another agency offering 50% better pay to do less analysis.
They sent some chump over 6 months later to get my help on a massive yearly report… I LAUGHED for a WHILE.

]]> Comment on Outgrowing Visual Foxpro by admin http://pirringers.com/mysqlblog/?p=3&cpage=1#comment-310 admin Tue, 11 May 2010 10:49:31 +0000 http://pirringers.com/mysqlblog/?p=3#comment-310 Difficult to say w/o seeing the rest try = AERROR(la_err) And then examinge the arra - it should give you a better answer on what the error is. Could be as simple as a mismatched variable. I do not use sqlprepare. I use straight passthrough. That Way I can better control the SQL. Difficult to say w/o seeing the rest

try
= AERROR(la_err)

And then examinge the arra – it should give you a better answer on what the error is. Could be as simple as a mismatched variable.

I do not use sqlprepare. I use straight passthrough. That Way I can better control the SQL.

]]> Comment on Outgrowing Visual Foxpro by hansem http://pirringers.com/mysqlblog/?p=3&cpage=1#comment-309 hansem Tue, 11 May 2010 03:12:37 +0000 http://pirringers.com/mysqlblog/?p=3#comment-309 if I used foxpro database there're no limit column when I save the records more than 50 column but if I used mysql server to save from foxpro there are limit column and then show warning "command contain unrecognized phrase/keyword" like code below : SQLEXEC(test,"LOCK TABLE LocalOnLine.vouchers WRITE") SQLPREPARE(test,"UPDATE LocalOnLine.vouchers SET division=?v_dv,agent=?v_ag,guide=?v_gd,invoice=?v_iv,month=?v_mt,years=?v_yr,voucher=?v_vc,; bill=?v_vl,cgroup=?v_gr,client=?v_cl,client1=?v_c1,client2=?v_c2,remarks=?v_rk WHERE month=?v_mt and years=?v_yr and voucher=?v_vc and totalrp+totalus>0") server=SQLEXEC(test) SQLEXEC(test,"UNLOCK TABLE") IF server<0 MESSAGEBOX('Update voucher '+v_vc+' '+v_vd+' '+ALLTRIM(v_nm)+' failed',16,'Warning') ENDIF what wrong with my code above and there are limit column to save from foxpro to mysql server ? please help me, what can I do to resolve that trouble. if I used foxpro database there’re no limit column when I save the records more than 50 column but if I used mysql server to save from foxpro there are limit column and then show warning “command contain unrecognized phrase/keyword” like code below :
SQLEXEC(test,”LOCK TABLE LocalOnLine.vouchers WRITE”)
SQLPREPARE(test,”UPDATE LocalOnLine.vouchers SET division=?v_dv,agent=?v_ag,guide=?v_gd,invoice=?v_iv,month=?v_mt,years=?v_yr,voucher=?v_vc,; bill=?v_vl,cgroup=?v_gr,client=?v_cl,client1=?v_c1,client2=?v_c2,remarks=?v_rk WHERE month=?v_mt and years=?v_yr and voucher=?v_vc and totalrp+totalus>0″)
server=SQLEXEC(test)
SQLEXEC(test,”UNLOCK TABLE”)

IF server<0
MESSAGEBOX('Update voucher '+v_vc+' '+v_vd+' '+ALLTRIM(v_nm)+' failed',16,'Warning')
ENDIF

what wrong with my code above and there are limit column to save from foxpro to mysql server ?

please help me, what can I do to resolve that trouble.

]]> Comment on Transferring VFP Table Structures to MYSQL by Elvir http://pirringers.com/mysqlblog/?p=17&cpage=1#comment-308 Elvir Sat, 08 May 2010 10:07:25 +0000 http://pirringers.com/mysqlblog/?p=17#comment-308 Good article. I like this . In meantime I did similar app in order to solve this transition. Keep posting. Good article. I like this . In meantime I did similar app in order to solve this transition. Keep posting.

]]>