executing *.sql file for mysql

[chandler]

I'm writing a routine to upload a .dbf table to a mySql table. Using some code I found on these forums, I'm able to generate a .sql script. What I'm stuck on is executing the .sql script in foxpro. The issue is that I can't get multiple commands to execute from a single script. Only a single command from a single script. What's the trick?

(I've verified that my connection works and I can execute queries to the mysql server.)

lcServer = '***'
lcDatabase = "***"
lcUser = '***'
lcPassWord = '***'
sqlfile = FILETOSTR("r:\imb\imbsetup.sql")
?sqlfile

* run .sql script
GetConnHandle = SQLCONNECT(lcServer,lcUser,lcPassWord)
?GetConnHandle

IF GetConnHandle > -1
   = SQLPREPARE(GetConnHandle, sqlfile)
   GetQuery  = SQLEXEC(GetConnHandle)
   IF GetQuery> -1
      SELECT SqlResult
      COUNT TO lnActiveCards
      
   ENDIF
   SQLDISCONNECT(GetConnHandle) && Close the ODBC connection
   
  else 
   AERROR(laError)
   MESSAGEBOX(laError[1,2])
  
   
ENDIF

And the .sql contents:

SET FOREIGN_KEY_CHECKS=0;
USE `production`;

DROP TABLE IF EXISTS `imbserial`;    

#
# Structure for the `imbserial` table :
#

CREATE TABLE IF NOT EXISTS `imbserial` (
    `ASSIGNID` int (6),
    `SNSTART` int (9),    
    `SNEND` int (9),    
    `JOBNUM` char (7),    
    `SHELLNUM` char (20),    
    `REQDATE` date,    
    `DATESEED` date
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Data for the `imbserial` table :
#

INSERT INTO `imbserial` (`ASSIGNID`,`SNSTART`,`SNEND`,`JOBNUM`,`SHELLNUM`,`REQDATE`,`DATESEED`) VALUES
    (1     ,100000000,100590489,'0000000','0000000',"20100401","20100401"),    
    (2     ,100590499,100597571,NULL,'531859A',"20100414","20100401"),    
    (3     ,100597581,100598330,NULL,'532171A',"20100415","20100401"),    
    (4     ,100598340,100665249,NULL,'IADTR05A',"20100415","20100401"),    
    (5     ,100665259,100732168,NULL,'IADTR05A',"20100415","20100401"),    
    (6     ,100732178,100745725,NULL,'531978A_F',"20100419","20100401"),    
    (7     ,100745735,100768629,NULL,'532166ABF',"20100419","20100401"),    
    (8     ,100768639,100781394,NULL,'532166CDE',"20100419","20100401"),    
    (9     ,100781404,100794951,NULL,'531978A_F',"20100419","20100401"),    
    (10    ,100794961,100817855,NULL,'532166ABF',"20100419","20100401");

COMMIT;

Chandler
I ran over my dogma with karma!

 

[Mike Lewis]

Have you tried putting the entire SQL script into a variable, and sending it with a single SQLEXEC() - in other words, without the SQLPREPARE()?

Something like this (off the top of my head):

TEXT TO lcCommand NOSHOW
SET FOREIGN_KEY_CHECKS=0;
USE `production`;

etc etc.

COMMIT;
ENDTEXT

GetConnHandle = SQLCONNECT(lcServer,lcUser,lcPassWord)
IF GetConnHandle > 0
  SQLEXEC(GetConnHandle, lcCommand)
ENDIF

SQLDISCONNECT(GetConnHandle)

I've left out the error-handling and other details, but this should give you the general idea.

Of course, you could also use FILETOSTR() to get the script into a file, as per your original code. My main point is to not do the Prepare, but to send the entire script at once. SQLPREPARE() is only relevant where you are executing the same command repeatedly.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business