Using cursor adaptor to update blob data in MySQL 2

DDPL · Tuesday at 2:10 PM

I have a MySQL database that I am uploaded data from a VFP database via a cursor adaptor, everything has been working fine, but now need a BLOB and MEMO field in one of the tables, didn't previously have any so was just do something simple like this;

Code:

SELECT &oSource
SCAN
    SELECT &oSource
    SCATTER MEMVAR
    SELECT &oTarget
    APPEND BLANK
    GATHER MEMVAR
ENDSCAN

Code still works but obviously doesn't upload the BLOB or MEMO, but changing SCATTER MEMVAR and GATHER MEMVAR to include the MEMO clause throws an error when it updates the record on the table with BLOB and MEMO fields, but is still ok on other tables that don't contain these field types.

In MySQL table the VFP BLOB field is matches with a LONGBLOB and the MEMO field with a TEXT. I have "fetch memo fields" ticked on the CursorAdaptor Builder.

The error tells me that I have an error in the SQL syntax, any ideas?

dylim · Tuesday at 2:42 PM

Hi DDPL,

Check out FILETOSTR() function. Also STRTOFILE(). You need them for Blobs.

DDPL · Tuesday at 2:57 PM

Hi Dylim,

I used FILETOSTR() to get the data into the BLOB field in the VFP table, the issue I am getting is copying the table record through the CursorAdaptor into the MySQL data table.

Chriss Miller · Tuesday at 7:23 PM

Have you added the field to the updatabelfieldlist of the CA? The cursorschema? Adding a field requires several changes in an existing CA.

Sometimes it can be the simplest to redo the CA (with the CA builder, for example).

Edit: There's nothing too special about any field/data type, that you need special treatment. The "fetch memo field" feature is not about fetching memo fields (and blobs, general, all alike) at all or not at all, when you untick it, it enables delayed fetching, but it doesn't suppress it, nor does it explain why you'd get SQL syntax errors. With or without delayed memo(blob/general fetching when you add a record and set the blob or replace the blob value of an existing record originally fetched from mySQL with its old or an empty value (when using the delayed fetch), itll count as new or updated value and be stored back when you do the TABLEUPDATE for sending back all buffered changes.

Well, maybe you're not at all using the CA as intended, you write very little about your general approach, besides having used the builder to create an updatable CA, I think.

One major thing to know, even if you don't want to update existing data and only use the CA cursor to feed in new data into MySQL the first step of using a CA and let it establish the mapping between MySQL and the cursor is to make a CursorFill call - in the dfaultt case automatic with the init of the CA, in other cases by an explicit call to CursorFill. To not fill it with any existing MySQL data you can set or modify the SelectCmd of the CA to include a WHERE 0=1, for example, so no record is fetched at all and then can fill the cursor in any way you want in buffered mode to finally send that data to MySQL with TABLEUPDATE.

Chriss Miller · Wednesday at 7:41 AM

Chriss Miller said:
the first step of using a CA and let it establish the mapping between MySQL and the cursor is to make a CursorFill call

To be more precise it's the simplest way to a) create the cursor as the cursoradapter needs it and b) have it associated with the CA and with updating the MySQL backend. You can also create an empty cursor and use CursorAttach to a CA that then makes the connection and communication with the backend, but then you can easily fail to have the cursor as necessary and that can lead to errors in the SQL generated by TABLEUPDATE. Well, and even the automatic mecahnisms you might use will forward data to MySQL with SQL generated towards it, neither the VFP process is hooked into MySQL nor MySQL is hooked into the VFP process and the only connection you have is via the ODBC layer and that communication goes by SQL, nothing else. VFP generates the SQL Inserts, Updates and Deletes necessary to forward what you did in the cursor, also when not buffering or only row buffering.

DDPL · Wednesday at 8:24 AM

Hi Chriss,

I am happy with the concept of the CA, have been using it for a while, you kindly offered advice a year or so ago in heling me set this up. Have taken what I believe to be the correct steps in added these two new fields into both tables, the VFP and MySQL.

I use the CA in different ways, I can clear the Target database and re-populate it from the Source database (works both ways round, VFP to MySQL and MySQL to VFP), I can also Synch the databases where it will push and pull data between the two depending on the most recent data, each field is DateTime stamps to allow this.

The CA has been working well, very robust and have learnt a certain amount about it's use over the last year or so.

When I added the new fields to both sets of tables I rebuilt the CA, to ensure that the new fields were included in the schema etc. the process fails when I use GATHER MEMVAR MEMO instead of GATHER MEMVAR.

I did some reading last night and there is some conflicting advice, but there does seem to be a consensus that the CA doesn't like GATHER MEMVAR MEMO and MEMO type fields in general, so will have a play today and see if I can update the data at a field level rather than a record level.

Darren

Chriss Miller · Wednesday at 9:01 AM

Sorry, I don't see why it would matter how you populate the cursor with data. The aspect of working with the cursor, also when it's a cursor attached to a CA, is merely a metter of VFP workings, not of MySQL. It doesn't change the final data forwarding to MySQL whether a memo field or blob field is populated with INSERT, APPEND BLANK and REPLACE or GATHER or any other mechanism, that solely affects the VFP cursor, you don't gather into a MySQL blob field when you gather into the CA cursor so the MySQL backend won't notice anything from that, it's solely fed from the CA by means of SQL generated when you TABLEUPDATE.

Again, from what you say I still don't know what mechanisms you're using, there are still several ways to use CA and the updatable view characteristic it has. I will need to know the technicalities, not your story. One major technical component to use is buffering. If you don't buffer the CA cursor, you're dealing with not well defined territory. That has about the same implications as an unbuffered DBF in an analogy of native DBF access without anything like a CA or view, merely opening a DBF in a workarea unbufferred. For example data will not be written instantly into a DBF just when you're doing GATHER, you always will need to leave the current record to trigger a DBF file updating. Am I confuding this with row buffering? No, I think not, even without row buffering you're not directly dealing with a DBF record and even less so with a MySQL backend table.

Using buffering is really very basic concept to use in every aspect of working with data, native or not, with free tables or any other backend, especially remote backends like database servers.

DDPL · Wednesday at 12:23 PM

Hi Chriss,

I get all of that, was trying to give you the technicalities, not a story.

I understand that the cursor has no relation to the MySQL database until you write back any changes to it, the mechanism I am using was demonstrated in the first post.

I establish a connection to the MySQL database, select the table I want to deal with, fetch a remote cursor, delete all the records in it and then upload all the records from the VFP table. I do this using the EXACT code below.

oSource = The Alias of the VFP table
oTarget = The Alias of the MySQL remote cursor

Code:

SELECT &oSource
SCAN
    SELECT &oSource
    SCATTER MEMVAR
    SELECT &oTarget
    APPEND BLANK
    GATHER MEMVAR
ENDSCAN

As I have Auto-update turned on each time it goes round the loop it moves on one record and automatically executes a TABLEUPDATE which generates the SQL script to push the data into the MySQL table via the CA.

I understand that using the GTHER MEMVAR or GATHER MEMVAR MEMO on the cursor is not doing anything to the MYSQL database, but when I use the latter and it gets to the bottom of the loop and the TABLEUPDATE is fired I get an error saying that there is an issue with the SQL. There are no other changes to any of the code, just the including of the MEMO clause before the update is fired.

I have just found a solution, but it doesn't make any sense to me. If I just use the GATHER MEMVAR, but then put a REPLACE fieldname WITH oSource.fieldname in the CA BeforeInsert method it works. The data in the cursor should be the same so the SQL statements being generated should be the same, but for some reason it work one way and not another.

Chriss Miller · Wednesday at 12:31 PM

What are your fieldnames?

It's better to use SCATTER MEMO NAME loRecord and GATHER MEMO NAME loRecord instead, or even INSERT INTO (oTarget) FROM NAME loRecord

Chriss Miller · Wednesday at 12:34 PM

DDPL said:
fetch a remote cursor, delete all the records in it..

When you fetch records and then delete them, that's also causing a lot of deletes in MySQL. I'm sure you also want that, but the straight forward way if you actually want to repopulate all data is to TRUNCATE the table and start empty on MYSQL already.

The way you do this the queries could cause all kinds of referencing errors when you insert data again. You shouldn't work this way or if so, plan closely in which order you do this to not interfere with integrity rules of relationships.

Chriss Miller · Wednesday at 12:54 PM

And in regard of technicalities:

As you have code in BeforeInsert and maybe more, that is important to know, don't you think? It would help if you finally post whther and what buffering you use, what code in general and your CA itself including all its code.

One thing that can easily catch you is you don't realize with single row or no buffering you're triggering sending the record you left, not the current record. That's a one off situation that may lead to errors you don't expect, as you assume you handle the current record.

The best control you can get is with table buffering und using an explicit TABLEUPDATE after the scan loop, also it would really be the simplest to insert all records into the cursor with an INSERT INTO target SELECT * from source. That will include memo and blob fields, or APPEND FROM source.DBF

markwoodwebdev · Wednesday at 12:56 PM

I ran into the same issue when trying to update BLOB or MEMO fields in MySQL using a remote cursor with a CursorAdapter. The GATHER MEMVAR MEMO command was causing SQL errors during TABLEUPDATE() — likely due to how VFP handles memo fields vs. how MySQL expects them.

I switched to using just GATHER MEMVAR (without the MEMO clause), then manually assigned the memo/blob fields in the CursorAdapter’s BeforeInsert method using REPLACE. This avoids VFP’s automatic memo assignment quirks and gives you control over how BLOB data is pushed into MySQL.

* In BeforeInsert of the CursorAdapter:
REPLACE blobfield WITH oSource.blobfield

This method made the TABLEUPDATE() generate valid SQL and worked reliably. Also, if you’re repopulating entire tables, consider running a TRUNCATE TABLE directly on the MySQL side instead of deleting all rows via VFP — it's faster and avoids unnecessary DELETE operations.

Hope this helps someone else avoid hours of debugging!

DDPL · Wednesday at 4:04 PM

As you have code in BeforeInsert and maybe more, that is important to know, don't you think?

I have never had any code in he CA, I entered that line of code in the BeforeInsert method today when trying to find a resolution"

What are your fieldnames?

The fields names that I have added are DContent and Notes, the first being the BLOB and the second being the MEMO field.

It's better to use SCATTER MEMO NAME loRecord and GATHER MEMO NAME loRecord instead, or even INSERT INTO (oTarget) FROM NAME loRecord

I will have a play with this, thank you for the suggestion

When you fetch records and then delete them, that's also causing a lot of deletes in MySQL. I'm sure you also want that, but the straight forward way if you actually want to repopulate all data is to TRUNCATE the table and start empty on MYSQL already.

The database content is created in VFP, when the creation is complete it is uploaded to MySQL (so this would be to an empty MySQL database at that point) then there is an ongoing synching process. There is a rare scenario where you want to re-create the data in the VFP database, at tis point you need to remove all the data from MySQL and re-copy over the VFP data.

I don't use any buffering, am dealing with the data on a record by record bases. It is a single user process, so no locking needed, the VFP database is on a PC and the MySQL on a RPi.

@markwoodwebdev, thanks for your reply. Sounds like exactly the same problem and found that the individual REPLACE for BLOB and MEMO fields seems to be the only way of resolving it that I can find.

Take on board both comments with regards to deleting and TRUNCATE TABLE, will also look at this so thanks both.

Chriss Miller · Wednesday at 7:36 PM

I guess you can solve this problem using a cursor schema, the mapping not only of field names but data types then can be fitted the best.

What you should also try is these settings for all workareas (that's done by using workarea 0 here, in the last parameter):

Code:

CURSORSETPROP('MapVarchar', .T., 0)
CURSORSETPROP('MapVarbinary', .T., 0)

I assume if a REPLACE in a BeforeInsert works, then you have a NULL value in the field you don't expect to have there and turn that to a value that doesn't cause an error. Anyway, check your data is as you want it and not missing the first or last record or having shifted values.

I can't for the sake of my life see how any mechanism you use to populate the cursor field would effect the transport of the blob from the cursor into mysql, so it can't for 100% certainty matter how it got there.

Chriss Miller · Wednesday at 8:05 PM

markwoodwebdev said:
I switched to using just GATHER MEMVAR (without the MEMO clause), then manually assigned the memo/blob fields in the CursorAdapter’s BeforeInsert method using REPLACE. This avoids VFP’s automatic memo assignment quirks and gives you control over how BLOB data is pushed into MySQL.

I can't support that idea. If you don't gather with the MEMO option you're skipping all FPT related fields, Memo, Blob, or General fields. Then it also is no wonder you need a replace.

Also, if you don't have MEMO in the SCATTER, you're not even scattering MEMO (FPT file) related fields into variables, so in that case GATHER with MEMO doesn't save the day, too.

Chriss Miller · Thursday at 8:48 AM

Besides, the whole SCATTER/GATHER shenanigans can be done with a single APPEND FROM dbffile (only requirement is cursor and table field names match) or INSERT INTO cursorname SELECT... (where you could match non identical field names in the SELECT fieldlist, if necessary), as I also already suggested. And then best do this with table buffering made and follow up with a TABLEUPDATE.

Chriss Miller · Friday at 10:09 AM

Here's all the ways you could copy data from a source dbf to a target cursor and you see your way doesn't copy values at all (the final browse of target1 will show the fields as "memo" and "blob", which means these fields are empty, wheeas all other browse windows will show "Memo" and "Blob" signalling these are filled with data,

And that's all to. You need to explicitly copy over the fields of FPTs "again" because you didn't copy them over with a SCATTER without MEMO option. That's all there is to it.

Code:

Create Cursor Source (mytext Memo, mybinary Blob)
Insert Into Source values ('Hello, world!',0he1103071d0)

Create Cursor Target1 (mytext Memo, mybinary Blob)
Create Cursor Target2 (mytext Memo, mybinary Blob)
Create Cursor Target3 (mytext Memo, mybinary Blob)
Create Cursor Target4 (mytext Memo, mybinary Blob)
Create Cursor Target5 (mytext Memo, mybinary Blob)
Create Cursor Target6 (mytext Memo, mybinary Blob)

* copy mechanism as used by you
oSource= 'Source'
oTarget= 'target1'
SELECT &oSource
SCAN
    SELECT &oSource
    SCATTER MEMVAR
    SELECT &oTarget
    APPEND BLANK
    GATHER MEMVAR
ENDSCAN

* working copy mechanism with scatter/gather
Select Source
Scan
   Scatter Memvar Memo
   Select Target2
   Append Blank
   Gather Memvar Memo
EndScan

* working copy mechanism with scatter/gather using record object
Select Source
Scan
   Scatter Memo Name oRecord
   Select Target3
   Append Blank
   Gather Memo Name oRecord
EndScan

* working copy mechanism with scatter to record object and insert from object
Select Source
Scan
   Scatter Memo Name oRecord
   Insert Into Target4 From Name oRecord
EndScan

* working copy mechanism with append
Select Target5
Append From Dbf("source")

* working copy mechanism with insert sql
Insert Into Target6 Select * From source

* looking at the results
For n=1 to 6
   cn=Transform(n)
   Select Target&cn
   Browse Nowait Name BrowseTarget&cn
   BrowseTarget&cn..top = (n-1)*100
   BrowseTarget&cn..height = 100
   BrowseTarget&cn..width = 200
EndFor n

side notes:

1. In a scan endscan loop you don't need to Select the iterated workarea, the endscan does not only skip 1 in the workarea you scan, it als makes it the selected workarea, which is demonstrated in the scan..endscan for Target2 and Target3.
2. You clearly are a legacy fox developer, but have forgotten important facts like how scatter and gather work regarding fpt file related fields, that's the main point.
Set a break poiint after the SCATTER in the Target1 example and see whether the scatter generated variables mytext and myblob. They don't exist! And you can' gather what you didn't even scatter.
3. Instead of Select &namevariable you can do SELECT (namevariable)
4. Even the second shortest form of copying by APPEND is something within the realm of legacy foxpro and you went the scatter/gather route. Why on earth are you doing this so overcomplicated? Also 3 is nothing that was only invented with VISUAL foxpro.
5. I used a source cursor. In case of having a source.dbf the APPEND solution would be written as APPEND FROM soure.dbf instead of Append From Dbf("source")The DBF() function is only necessary for a cursor source as appending from a dbf file requires the name of the file, not only the name of a workarea that has the file open.
6. The .. in the final loop is not a typo I made 3 times at the same place, one dot is for ending macro substitution and in normal contexts that's optional (like for Browse Nowait Name BrowseTarget&cn) and therefore less known, in this context it's absolutely necessary, as the second point is necessasary to address the top,hieght, and width properties. In legacy foxpro you would need arrow notation: &cn.->top, for example. Though I doubt legacy foxpro allowed BROWSE NAME.

Chriss Miller · Friday at 12:15 PM

Last not least, here's a demo that will show GATHER MEMO works in conjunction with a CA, when done right (i.e. also SCATTER MEMO):

Code:

#Define CONNECTIONSTRING [your connection string here]
Clear

h=Sqlstringconnect(CONNECTIONSTRING)
Text To lcSQL NoShow
   Create Or Replace Database CursorAdapterDemo;
   Use CursorAdapterDemo;
 
   Create Or Replace Table MyTarget(
     Id MEDIUMINT NOT NULL AUTO_INCREMENT,
     MyText LONGTEXT NOT NULL,
     MyBlob Blob NOT NULL,
     Primary Key (Id)
   );
EndText
SQLExec(h,lcSQL)

Create Cursor Source (MyText Memo, MyBlob Blob)
Insert Into Source values ('Hello, world!',0he110)

CursorSetProp("Buffering",5,0)
oCA = CreateObject("democa")
oCA.CursorFill()

* Insert Data into MySQL
cSource = 'Source'
cTarget = 'CrsTarget'

Select (cSource)
SCAN
    SCATTER MEMVAR MEMO
    Select (cTarget)
    APPEND BLANK
    GATHER MEMVAR MEMO
EndScan
TableUpdate(.t.,.t.,"crsTarget")
oCA=.null.
Use In source

* Verify
oCA = CreateObject("democa")
oCA.CursorFill()
? crsTarget.MyText
? crsTarget.MyBlob

DEFINE CLASS democa AS cursoradapter
    SelectCmd = "Select Id, MyText, MyBlob from MyTarget"
    CursorSchema = "ID I, MYTEXT M, MYBLOB W"
    Alias = "crsTarget"
    DataSourceType = "ODBC"
    Flags = 0
    CompareMemo = .F.
    FetchAsNeeded = .F.
    FetchMemo = .F.
    Prepared = .F.
    BatchUpdateCount = 5
    WhereType = 1
    KeyFieldList = "ID"
    Tables = "MyTarget"
    UpdatableFieldList = "MYTEXT, MYBLOB"
    UpdateNameList = "ID MyTarget.Id, MYTEXT MyTarget.MyText, MYBLOB MyTarget.MyBlob"
    MapVarchar = .T.
    MapBinary = .T.
    UseCursorSchema = .T.

    PROCEDURE Init
        *** Setup code: DO NOT REMOVE
        local llReturn
        do case
            case not pemstatus(This, '__VFPSetup', 5)
                This.AddProperty('__VFPSetup', 0)
            case This.__VFPSetup = 1
                This.__VFPSetup = 2
            case This.__VFPSetup = 2
                This.__VFPSetup = 0
                return
        endcase
        set multilocks on
        llReturn = dodefault()
        *** End of Setup code: DO NOT REMOVE
        *** Select connection code: DO NOT REMOVE

        ***<DataSource>
        This.DataSource = sqlstringconnect(CONNECTIONSTRING+'DB=CursoradapterDemo;')
        ***</DataSource>
        *** End of Select connection code: DO NOT REMOVE

        *** Setup code: DO NOT REMOVE
        if This.__VFPSetup = 1
            This.__VFPSetup = 2
        endif
        return llReturn
        *** End of Setup code: DO NOT REMOVE
    ENDPROC

    PROCEDURE AutoOpen
        *** Setup code: DO NOT REMOVE
        if not pemstatus(This, '__VFPSetup', 5)
            This.AddProperty('__VFPSetup', 1)
            This.Init()
        endif
        *** End of Setup code: DO NOT REMOVE
    ENDPROC
ENDDEFINE

Notes:

1. It should be obvious, but at the #DEFEINE where it says [your connection string here] put in your connection string
2. Ensure you don't have a db called CursoradapterDemo in your database server as that gets overwritten!

The CA here was done with the help of the native CA builder after having run the part that creates the MySQL DB and table. Notice while the builder puts code in AutoOpen, the demo code still runs CursorFill, and that's because the autoopen mechanism only fires when a CA becomes part of a data environment and that has AutoOpenTables set .T.

At the place where I put the ammended SCAN loop with both SCATTER and GATHER MEMO rather than without MEMO you could experiment. Removing MEMO from SCATTER already leaves you with an empty reord that's due to APPEND BLANK and due to GATHER not gathering anything, no matter if you stick to GATHER MEMO or also remove MEMO from there. At this place you could also put in the APPEND FROM DBF("source") to see it works even in conjunction of having a target cursor associated with the cursoradapter that has the ID keyfield on top of the source fields. It would become problematic, indeed, of the source data would have an id field with values as that's not an updatable field, primar key fields are db gnerated and only in rare cases like GUID could also be set from outside, if allowed as updatable field.

I think that wraps it up also for people finding this thread and coming to the wrong conclusion CAs don't work in conjunction with GATHER. Besides not needing this even in legacy FoxPro, that's just a damn lie.

Chriss Miller · Friday at 1:29 PM

DDPL said:
hanging SCATTER MEMVAR and GATHER MEMVAR to include the MEMO clause throws an error when it updates the record on the table with BLOB and MEMO fields,

To also react to that implicitly. Unfortunately you don't specify what error you see. If I do my demo code and record the queries in a log of MySQL I get
1. a PREPARE of an INSERT INTO TargetTable (MyText, MyBlob) Values (?,?)
2. an INSERT of the record with values
3. No error

If I instead remove the MEMO and have empty records, an AERROR points out the error:
"Cannot insert an empty row from a view or CursorAdapter into its base table(s)."

That could change, if the table definition would not state NOT NULL, perhaps, though I don't send over NULLs but empty values. That's a bit strange, but not what you report. I don't see the problem you report and I definitely only see the problem of data copying into the cursor adapter cursor. If you would set breakpoints and use the debugger, you'll see for yourself that you can't gather what you didn't scatter into variables.

Besides all that taking the big picture you see you gave very little information to go on about and I think as the case is solved for you and you're now sure you found a GATHER MEMO bug of VFP and the workaround in replace, I also only write this for future readers. You came to wrong conclusions, your experience is unfortunately not reproducible even with general query logging and thus it remains a secret, but the demo code proves you can also copy over data with SCATTER/GATHER, provided you do it correctly, even in one of three ways with memvars, a record object and half sql whn using INSERT FROM NAME.

The impatience and imprecision is unbearable, but I will still be here for people that constructively work together with the forum members to get things going forward.

Chriss Miller · Friday at 2:18 PM

This is what actually comes from the general query log:

Including the query to get the log. VFP is failing to show the binary value correctly as the code used here doesn't actually encode it in binary but as an escaped MySQL strring that doesn't transport well into ANSI, but it is the same binary value, as the demo code shows with ? crsTarget.MyBlob

I did one last experiment without any buffering and then I also don't get any data in the MySQL table in the end, there's nothing happening with data added into crsTarget associated with the CA. So to finally get a reposnse and analysis you would need to post more details about your setup.

Instead using record buffering, I get this:

First, I added two rows of data here and changed the code to set row buffering right after preparing the source data:

Code:

Create Cursor Source (MyText Memo, MyBlob Blob)
Insert Into Source values ('Hello, world!',0he110)
Insert Into Source values ('Foo Bar',0hf00baa)

CursorSetProp("Buffering",3,0)

I also added an extra Skip in crsTarget after the ENDSCAN to make sure the second record also gets over.

It turns out when doing this in single row buffering mode, it just takes more prepares and executions, one for each record:

Still no error.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Using cursor adaptor to update blob data in MySQL 2

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor