0

I have the following problem:

I have 2 MySql DBs on 2 different servers with a single table, each with same table structure. These DBs tables have a number of BLOB fields in each record (voice recordings in WAV format), plus a numeric record id.

I am using classic ASP (VB) and can not change since the whole sistem is in classic asp (furthermore I do not know another language/system yet). I know it's old but now need to solve this problem.

I need to read a certain record from DB1 (let's say record with IdNum=100), with full data (including BLOB fields) ... and save (insert) the same full record in table of DB2 (including the BLOB fields).

I tried normal insert but get a "type mismatch" error in the sqlRec2 declaration line.

Anybody can help me about how to do this ?? THANKS !!

My code is below (recwav01, 02 and 03 are BLOB fields, "notes" is txt field):

Set my_conn = createobject("ADODB.Connection")
Set my_conn2 = createobject("ADODB.Connection")

my_conn.open = "DRIVER={MySQL ODBC 5.3 Unicode Driver};"_
& "SERVER=yyy.yyy.yyy.yyy;PORT=3306;"_
& "DATABASE=dbremote01;"_
& "UID=root;PWD=password01; OPTION=35;"

my_conn2.open = "DRIVER={MySQL ODBC 5.3 Unicode Driver};"_
& "SERVER=xxx.xxx.xxx.xxx;PORT=3306;"_
& "DATABASE=dbremote02;"_
& "UID=root;PWD=password02; OPTION=35;"

sqlRec = "SELECT * FROM table01 WHERE idnum>=100 AND idnum <=120;"
Set rs = my_conn.Execute(sqlRec)

DO WHILE NOT rs.EOF
  sqlRec2 = "INSERT INTO table02 (idnum,notes,recwav01,recwav02,recwav03) VALUES (" & _
  rs("idnum") & ", '" & rs("notes") & "', '" & rs("recwav01") & "', '" & rs("recwav02") & _
  "', '" & rs("recwav03") & "');"
  my_conn2.Execute(sqlRec2)
  rs.movenext
LOOP
O. Jones
  • 103,626
  • 17
  • 118
  • 172
Lawrence
  • 11
  • 7
  • 1
    You may need to save the BLOBs to files or insert as a hex string. http://stackoverflow.com/questions/10729824/how-to-insert-blob-and-clob-files-in-mysql – backpackcoder Jan 04 '17 at 21:56

1 Answers1

-1

Yipes! You have a hard problem. 21st-century languages and runtimes have a difficult time handling large BLOBs; they often need some sort of streaming protocol to transfer them from the server to program RAM. And you're dealing with a late 20th-century runtime that hasn't been updated in many years. Depending on the size of your BLOBs, the answer to your classic ASP question may turn out to be You Can't Do That™.

In the meantime, you might try using Base 64 encoding to turn your binary stuff into text strings. This pair of SQL statements might do the trick.

To read your table:

 sql1 = "SELECT  idnum, notes, TO_BASE64(recwav01) w1, TO_BASE64(recwav02) w2, TO_BASE64(recwav03) w3; "

Then, to INSERT rows, a statement like this.

sql2 = _
"INSERT INTO table02 (idnum, notes, recwav01, recwav02, recwav03) " & _
   "VALUES (" & rs("idnum") & ", '" & rs("notes") & "', " & _
            "FROM_BASE64('" &  rs("w1") & "'), " & _
            "FROM_BASE64('" &  rs("w2") & "'), " & _
            "FROM_BASE64('" &  rs("w3") & "')); "

(No warranty on the correctness of this concatenated text string. Warning that if any of your notes column values contain ' characters this will blow up in your face.)

The idea here is to convert your binary data to text strings on the way in from your DBMS, and convert those strings back to binary on the way out.

If it turns out this doesn't work because the BLOBs are too big for classic asp, you could try using mysqldump to extract your data to a text file, then to load it into the new database.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I'm working on the 2.nd option. I use mysqldump on first DB to save each BLOB field into a local file, then insert each saved file into a new record on the second DB. The first part is working fine. I can save the files and they are correct (can listen the saved wav). The problem is that I can not save them into the second DB ... I used the following command in the insert: INSERT INTO table2(idnum, note,recwav01,recwav02) VALUES(100,NULL,LOAD_FILE('c:/tempdir/recfile01.wav'), LOAD_FILE('c:/tempdir/recfile02.wav')); ... it creates the record, but does not save BLOB, the BLOB fields are empty. – Lawrence Jan 05 '17 at 01:10
  • What a load of .... this answer peddles! I've stored WAV files as blobs in a SQL Server database and retrieved them using `ADODB.Stream` it is entirely doable. – user692942 Jan 05 '17 at 02:02
  • 1
    suceeded !! just for everybody to know: When using load_file('c:/path/File.xxx'); The file that you are loading HAS to be on the machine the sql instance is running on (meaning the remote server). Also pay attention to set: max_allowed_packet=100M into my.ini (mysql config file). ;) – Lawrence Jan 05 '17 at 02:37
  • @Lawrence while that will work it's a lot of work for not a lot of gain. You want to try something like http://stackoverflow.com/a/5018462/692942 but instead of `LoadFromFile()` just point it at the database. No need for storing files on the server it's messy and unnecessary. – user692942 Jan 05 '17 at 14:41