Insert Into mySQL Database

[Mickbw]

Hi,

I have a large amount of information from a 2.x fox database that I want to scan & insert into a mysql database so that I have more control over the results than through the sqldump. I also hoped to be able to use the same process when bringing in daily activity.

The steps I have taken so far to bring me to the edge of my emotional cliff.
1. I have brought in the data into a Fox Cursor in VFP8 SP1.
2. I have also created a CursorAdapter to the mySQL table which does display the existing data using the builder. The code generated in the init method follows:

[COLOR=green]
local llReturn
do case
	case not pemstatus(This, '__VFPSetup', 5)
		This.AddProperty('__VFPSetup', 0)
	case This.__VFPSetup = 2
		This.__VFPSetup = 0
		return
endcase
llReturn = dodefault()
*** Setup code: DO NOT REMOVE
***<SelectCmd>
text to This.SelectCmd noshow
select lagency.* from lagency
endtext
***</SelectCmd>
***<KeyFieldList>
text to This.KeyFieldList noshow
AGENCYID
endtext
***</KeyFieldList>
***<UpdateNameList>
text to This.UpdateNameList noshow
AGENCYID lagency.AGENCYID, AGENCYNUMBER lagency.AGENCYNUMBER, AGENCYNAME lagency.AGENCYNAME, TRANDATE lagency.TRANDATE, ACTIVE lagency.ACTIVE, USERID lagency.USERID
endtext
***</UpdateNameList>
***<UpdatableFieldList>
text to This.UpdatableFieldList noshow
AGENCYNUMBER, AGENCYNAME, TRANDATE, ACTIVE, USERID
endtext
***</UpdatableFieldList>
*** End of Setup code: DO NOT REMOVE
if This.__VFPSetup = 1
	This.__VFPSetup = 2
endif
return llReturn
[/color]

As I said the CursorAdapter cursor is displayed.
I then scan the Fox Cursor with the following code

[COLOR=green]
USE lAgency
	SCAN
		INSERT INTO caAgency (AgencyNumber, ;
			AgencyName, ;
			TranDate, Active) ;
			VALUES (lAgency.AgencyNumber, ;
			lAgency.AgencyName, ;
			TTOD(lAgency.TranDate),1)
		SELECT lAgency
	ENDSCAN
[/color]

The TranDate

[COLOR=purple](Date)[/color]

and Active

[COLOR=purple](TinyInt)[/color]

fields are updated correctly but the Character fields agencyNumber & agencyname

[COLOR=purple](VarChar)[/color]

do not get updated.
The schema for the CA is as follows:

[COLOR=purple]
AGENCYID I, AGENCYNUMBER C(4), AGENCYNAME C(60), TRANDATE D, ACTIVE I, USERID I
[/color]

I have tried changing the Character fields to different types (char, tinytext, medtext, text) with no change in the results.

If I look at the cursor for the CursorAdaptor I am allowed to enter values in the AgecnyNumber and AgencyName fields but when I move out of the field the changes are lost. On the Trandate, Active and UserID fields my changes stick.

I honestly thought I might have trouble converting the date but text fields? Any ideas or code samples would be greatly appreciated.

Sincerely,

Michael







Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[mm0000]

Since both the fields that fail updates have field names more than 10 characters could you try and change the fieldnames in the cursor to <= 10 characters and then see if it works.

 

[Mickbw]

Thanks mm0000,

That wasn't the problem but it started me on the right path to figuring it out.

Even though I had defined the field size of the Character fields, I had to insert a initial record where the AgencyNumber field had the required 4 characters and the AgencyName field had the maximum 60 characters.

I also set Pad Char to full length and Return Matching Rows in the ODBC Data Source Config for the mySQL ODBC driver.

It works now.

Maybe I will write a FAQ (it would be my first) showing step by step how to create a cursoradapter to mySQL.

Thanks for the help and have a nice weekend,



Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[wgcs]

amount of information from a 2.x fox database that I want to scan & insert into a mysql database

If this is a one-time import you're talking about, you might find a much easier way to be using the MySql &quot;LOAD DATA&quot; command, which is essentially like VFP's &quot;APPEND FROM&quot;, and can import data from a text file local to MySql.

 

[Mickbw]

Hi,

These particular imports will be done only a few times but I have others that will be done on a regular basis so I thought I could experiment with the process now (though I didn't think it would take as long at the beginning).

It also served as a good entry point to the frustrating world of cursor adapters. I do see the benefits of them now.

Michael

Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury