Source language: Translate to:

Copy a BLOB-field from one table to an other table

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Copy a BLOB-field from one table to an other table

Postby Stefan » Mon May 26, 2008 1:58 am

Hi
I have two mysql-databases.
One is local on my pc, the other one ist on a server by my provider.

Now i create and copy all tables and her records from my local database to the other database.

All works, except the BLOB-Fields with the pictures! I can't copy them:
- With the dbpRecordToVar and dbpVarToRecord the software doesn't copy blob-fields
- With the dbpExecSQL-Command, the string ist not long enough to copy the whole picture, only 40bit 8a portion of the image will be copy!
- The dbpExportTo CSV doesn't copy BLOB-Fields

Have someone an idee to copy Pictures from one BLOB-Field to an other in a other databas/table?

Stefan
Stefan
 
Posts: 35
Joined: Mon Dec 03, 2007 7:00 am

Postby smartmedia » Mon May 26, 2008 2:29 am

Hi...

Unfortunately DBPro not support mass blob extraction.
But you can work around the problem with a simple procedure.

1) Create a relationship between the two tables
So when you move to next record in table 1 automatically you are moved to next record in table 2

2) Goto first record with dbpFirst

3) Create a loop according to your query number
So when your query has return 50 records the loop will run 50 times

4) Inside the loop enter the following code
Use dbpExportBlob to export the blob from table 1 and use setvar to table 2 to import the blob.

5) Use dbpNext inside the loop afterwards and you are ready.

Hope i help..
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Stefan » Mon May 26, 2008 2:44 am

Thanks for your tipp!

But i work with the same table in two several databases, so the command with the Relationshop doesn't work.

Stefan
Stefan
 
Posts: 35
Joined: Mon Dec 03, 2007 7:00 am

Postby smartmedia » Mon May 26, 2008 3:03 am

Hi...

No problem.
If the tables are identical and have an primary key is easy to do it.

Just make a query go to first record take the primary key run a query with the primary key in second table and follow the rest of the steps.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Stefan » Mon May 26, 2008 3:51 am

Hi

Now i have made the code, but it only export the blob-field to a file, but it doesn't save the file in the blob-field in the other database/table.
Have i made a mistake?

- If i have several tables to update, the actual table is in the variable [TabelleMySQL]
- mysql_gsc is my connection-id of my local MySQL
- partner is my connection -id of a MySQL-Database on a Server

While "[zaehler]" "<=" "[mysql_gsc.[TabelleMySQL].$RecCount]"
.insert records
dbpRecordToVar "mysql_gsc" "[TabelleMySQL]" "[record]" "Delimiter=,;IncludeHidden=No;ForceQuotes=No"

dbpAddRecord "partner" "[TabelleMySQL]"
dbpVarToRecord "partner" "[TabelleMySQL]" "[record]" "Delimiter=,;IncludeHidden=No"

dbpExportBlob "mysql_gsc" "[TabelleMySQL]" "bild" "[PubDir]bildexport.gif"

SetVar "[partner.[TabelleMySQL].bild]" "[PubDir]bildexport.gif"

dbpSaveEdits "partner" "[TabelleMySQL]"

dbpNext "mysql_gsc" "[TabelleMySQL]"
SetVar "[zaehler]" "[zaehler]+1"
EndWhile

Stefan
Stefan
 
Posts: 35
Joined: Mon Dec 03, 2007 7:00 am

Postby Gaev » Mon May 26, 2008 7:20 am

Stefan:

Your code looks logical enough ... but you might check/try the following ...

a) Tempoorarily ... just before the command ...

While "[zaehler]" "<=" "[mysql_gsc.[TabelleMySQL].$RecCount]"

... insert ...

dbpGetFieldDefs "partner" "[TabelleMySQL]" "[#13]" "[FieldInfo]"
StrReplace "[FieldInfo]" "[#13]" "!|" "[FieldInfo]"
AlertBox "FieldInfo" "[FieldInfo]"


... this will confirm that your target table has the "bild" field defined as a Blob type.

b) Temporarily ... after the command ...

dbpExportBlob "mysql_gsc" "[TabelleMySQL]" "bild" "[PubDir]bildexport.gif"

... insert ...

PopUpImage "-1" "-1" "[PubDir]bildexport.gif" "0" "None" "0"
AlertBox "Exported" "[zaehler]"


... this will confirm that each record's Blob field contents is correctly being exported to the [PubDir] folder.

c) Assuming you can confirm both items above, I am guessing that there might be some confusion due to the fact that the file being exported from every record is called exactly the same i.e. "[PubDir]bildexport.gif" ... perhaps, when you do the subsequent SetVar command, NeoBook looks at the file name ... and since it did not change ... assumes that there was no change to the variable "[partner.[TabelleMySQL].bild]" ... and hence does not update the contents of the "bild" field ... although it should have done so for the very first record.

You could check out this theory by temporarily doing ...

dbpExportBlob "mysql_gsc" "[TabelleMySQL]" "bild" "[PubDir]bildexport[zaehler].gif"
SetVar "[partner.[TabelleMySQL].bild]" "[PubDir]bildexport[zaehler].gif"
PopUpImage "-1" "-1" "[partner.[TabelleMySQL].bild]" "0" "None" "0"
AlertBox "Imported" "[zaehler]"


... and see if this does the trick ... afterwards, you might also verify that the gif files can be correctly viewed using your default Image Viewer program.

d) If (c) does not help ... just temporarily try and replace the command ...

SetVar "[partner.[TabelleMySQL].bild]" "[PubDir]bildexport[zaehler].gif"

... with ...

SetVar "[partner.{actual table name}.bild]" "[PubDir]bildexport[zaehler].gif"

... where {actual table name} is hard coded to be what ever value you are using for [TabelleMySQL] for the test ... perhaps, the double substitution in [partner.[TabelleMySQL].bild] is causing NeoBook to then not treat this as a database field ... and thus not do the required import of the file's contents.

Otherwise, I am all out of suggestions.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Stefan » Mon May 26, 2008 8:23 am

Hi Gaev

Thank you very much for your ideas!
I have tried everything - but with no sucess!

The export of the images are done very well, but only the import doesn't go!

Stefan
Stefan
 
Posts: 35
Joined: Mon Dec 03, 2007 7:00 am

Postby Gaev » Mon May 26, 2008 9:23 am

Stefan:
The export of the images are done very well, but only the import doesn't go!
So am I correct in assuming that ...

PopUpImage "-1" "-1" "[partner.[TabelleMySQL].bild]" "0" "None" "0"

... displays the images you were expecting for each record ?


Could it be that there is a problem updating records on your remote machine ... either due to large record sizes or lack of support for this kind of content ... etc. etc. ... perhaps you can try and run your routine using a database that is located on your local machine ... see if that works.

You might also post details of the command(s) used to create the target database table(s) ... so we can look at the field definitions being used for the picture field ... the version of MySQL for the target database would also help.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Stefan » Mon May 26, 2008 10:39 pm

The problem is dissolved!

After hours of trying i have found the solution!
The code for copy data and picture is:

While "[zaehler]" "<=" "[mysql_gsc.[Tabelle].$RecCount]"
.insert Record
dbpAddRecord "partner" "[Tabelle]"

.copy data and save data
dbpRecordToVar "mysql_gsc" "[Tabelle]" "[record]" "Delimiter=,;IncludeHidden=No;ForceQuotes=No"
dbpVarToRecord "partner" "[Tabelle]" "[record]" "Delimiter=,;IncludeHidden=No"
dbpSaveEdits "partner" "[Tabelle]"

.copy picture and save
SetVar "[partner.[Tabelle].bild]" "[mysql_gsc.[Tabelle].bild]"
dbpSaveEdits "partner" "[Tabelle]"

dbpNext "mysql_gsc" "[Tabelle]"
SetVar "[zaehler]" "[zaehler]+1"
EndWhile

So, the solution is, that we must make the dbpSaveEdits command 2x! Without two Save Commands it doesn't work!

I hope, that's useful for all !
Stefan
Stefan
 
Posts: 35
Joined: Mon Dec 03, 2007 7:00 am


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest