Solved

mySQL - Store files on mySQL databases

Posted on 2003-11-02
4
436 Views
Last Modified: 2011-10-03
Hello, I am trying to find some code that will allow me a way to have a small file manager on Visual Basic connected to a mySQL server.

In other words..  I want to store a file on a database table on line.  This database I want to be able to re-call that file.
Can someone show me some example code?
0
Comment
Question by:clear100-com
  • 3
4 Comments
 
LVL 1

Accepted Solution

by:
eScapePro earned 30 total points
ID: 9678062
You can get similar code from Microsoft's website. You will need to create a binary type field in the database which will hold the file data.

Basically, you will need to create the table. I suggest having two tables. Many users will advise you not to store files straight into a database but I have done this many times and it is very useful.

Have a table called FileIndex, where you can store file.primary.key, filename, date/time, user, other information. Then have a table, like FileData, which has file.primary.key, filebinarydata as binary. This way you can search the index table quickly and then just jump to the second table using the samy primary key. This will speed stuff up!

This link shows how to read and write BLOB (binary large object ) to and from Access using DAO. The same code (nearly) will work with ADO and mySQL over OLEDB/ODBC:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q210/4/86.asp&NoWebContent=1
0
 

Author Comment

by:clear100-com
ID: 9684413
Im lost when it comes to the ADO stuff I use ADODB

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
----------------------------------------------------------------------

Is there a way you can convert it with my string?  When i try the following, it says the db is read only. .. And yes my right are ok.


Private Sub Form_Load()
CopyFile "scrapfile.ppt", "doublescrapfile.ppt"
End Sub

'**************************************************************
' SUB: CopyFile
'
' PURPOSE:
'   Demonstrates how to use ReadBLOB() and WriteBLOB().
'
' PREREQUISITES:
'   A table called BLOB that contains an OLE Object field called
'   Blob.
'
' ARGUMENTS:
'   Source - The path and filename of the information to copy.
'   Destination - The path and filename of the file to write
'                 the binary information to.
'
' EXAMPLE:
'   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
'**************************************************************
Sub CopyFile(Source As String, Destination As String)
    Dim BytesRead As Variant, BytesWritten As Variant
    Dim Msg As String
    Dim db As DAO.Database
    Dim T As DAO.Recordset

    ' Open the BLOB table.
   
    Set db = OpenDatabase("", dbDriverComplete, False, "DRIVER={MySQL ODBC 3.51 Driver}" & _
                        ";SERVER=" & "localhost" & _
                        ";DATABASE=" & "Test" & _
                        ";UID=" & "mysys" & _
                        ";PWD=" & "wd006451" & _
                        ";OPTION=2")
   
    Set T = db.OpenRecordset("MyTest", dbOpenTable)
    ' Create a new record and move to it.
    T.AddNew
    T.Update
    T.MoveLast

    BytesRead = ReadBLOB(Source, T, "MyTest")

    Msg = "Finished reading """ & Source & """"
    Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
    MsgBox Msg, 64, "Copy File"

    BytesWritten = WriteBLOB(T, "MyTest", Destination)

    Msg = "Finished writing """ & Destination & """"
    Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
    MsgBox Msg, 64, "Copy File"
End Sub





0
 

Author Comment

by:clear100-com
ID: 9686862
Well I did figure it out, I am giving the points however to eScapePro because he showed me that it was part of blob.  But i found a site on mySQL that shoed me how to do this and it worked great!!!

http://www.vbmysql.com/articles/blobaccessvb.html#sample1
0
 

Author Comment

by:clear100-com
ID: 9689270
Hey: eScapePro

I just wanted to say thank you again, and thanks for the idea of using a seprate table or database, I am designing a case system and once a case is moved from one techs bin to another i need that file kept in there, and i don't want to have the user download the file and upload it again just to move it.  So,  yes, great idea.

Bill.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question