B4J Question Using MySql LOAD_FILE to insert Blob Image

[Swissmade]

Hi All,

Mysql has a nice function to load a file and insert this in a field in the table.
LOAD_FILE.
Why is this not working when I use this
This a part of the SQL Query called with

    "', UserImage = LOAD_FILE('" & Functions.ImageFileName.Replace("\", "/") & "')"
Functions.SQLDB.ExecNonQuery(tmpSQL)

Full Insert or Update Query Working With MySql Query builder.

        tmpSQL = "INSERT INTO axusers SET Name = '" & txtName.Text _
            & "', Password = '" & txtPassword.Text _
            & "', UserName = '" & txtName.Text _
            & "', Comment = '" & txtName.Text _
            & "', UserLevel = '" & tmpLevel _
            & "', Client = '" &  txtClient.Text _
            & "', UserCard = '" & txtTag.Text  _
            & "', Departement = '" & txtDepartement.Text _
            & "', Location = '" & txtUserLocation.Text _
            & "', UserImage = LOAD_FILE('" & Functions.ImageFileName.Replace("\", "/") & "')"  _
            & ";"
                Functions.ConnectToDB
                Functions.SQLDB.ExecNonQuery(tmpSQL)
'

All Fields are inserted but not the Image.

 

[Swissmade]

Hi All,

Mysql has a nice function to load a file and insert this in a field in the table.
LOAD_FILE.
Why is this not working when I use this
This a part of the SQL Query called with

    "', UserImage = LOAD_FILE('" & Functions.ImageFileName.Replace("\", "/") & "')"
Functions.SQLDB.ExecNonQuery(tmpSQL)

Full Insert or Update Query Working With MySql Query builder.

        tmpSQL = "INSERT INTO axusers SET Name = '" & txtName.Text _
            & "', Password = '" & txtPassword.Text _
            & "', UserName = '" & txtName.Text _
            & "', Comment = '" & txtName.Text _
            & "', UserLevel = '" & tmpLevel _
            & "', Client = '" &  txtClient.Text _
            & "', UserCard = '" & txtTag.Text  _
            & "', Departement = '" & txtDepartement.Text _
            & "', Location = '" & txtUserLocation.Text _
            & "', UserImage = LOAD_FILE('" & Functions.ImageFileName.Replace("\", "/") & "')"  _
            & ";"
                Functions.ConnectToDB
                Functions.SQLDB.ExecNonQuery(tmpSQL)
'

All Fields are inserted but not the Image.

This is Solved after 2 days

 

[Erel]

It is better to post the solution in such cases.

 

[Swissmade]

This is Solved after 2 days

with

'First read the Image from a ImageView to a buffer
Dim OutputStream1 As OutputStream
OutputStream1.InitializeToBytesArray(10000)
imgUser.GetImage.WriteToStream(OutputStream1)
Dim Buffer() As Byte 'declares an empty array
Buffer = OutputStream1.ToBytesArray
OutputStream1.Close
'Now the Image is in the buffer
'Second we save this to Mysql.

Functions.ConnectToDB
Functions.SQLDB.ExecNonQuery2("UPDATE axusers SET UserImage = ? WHERE ID = '" & txtUserID.text & "' ", ArrayAsObject(Buffer))
Functions.SQLDB.close
'After you have save all the entry's of your choice you have to start a second Query to save the Image.
'Blob has to be define in the MYSQL Table as Longblob.
'Image can not be too big or you have to set some variable in the MYSQL Database.

I hope this will explain this more

 

[吳界明]

strSQL = "INSERT INTO student SET name = '" & b _
& "', age = " & c _
& ", address = '" & d _
& "', photo = LOAD_FILE('" & File.Combine(File.DirRootExternal, "pic1.jpg") & "')" _
& ";"

Who can tell me what's wrong above? I want to insert the image .jpg file into MySQL ,AND all are insert ok ,BUT photo is not ok
WHY? I use then imageView component that can display the photo correct, showed as follows
inPhoto.Bitmap=LoadBitmap(File.DirRootExternal,"pic1.jpg")

 

[Swissmade]

Best way to do this.

Dim Buffer() As Byte 'declares an empty array
        Dim InStream As InputStream
        InStream = File.OpenInput(File.DirRootExternal,"pic1.jpg")
        Dim OutputStream1 As OutputStream
        OutputStream1.InitializeToBytesArray(100000)
        File.Copy2(InStream, OutputStream1)
        Buffer = OutputStream1.ToBytesArray
        OutputStream1.Close

With a Imageview you can take the Image direct from the Imageview.

        Dim Buffer() As Byte 'declares an empty array
        Dim OutputStream1 As OutputStream
        OutputStream1.InitializeToBytesArray(10000)
        [Your image View].GetImage.WriteToStream(OutputStream1)
        Buffer = OutputStream1.ToBytesArray
        OutputStream1.Close

Now you have your Image in a Byte Buffer.
Change your Query as following.
Your Query

Dim strSQL As String = "INSERT INTO student SET name = '" & b _
& "', age = " & c _
& ", address = '" & d _
& "', photo = ?;"

ConnectToDB 'This is your Connection
SQLDB.ExecNonQuery2(strSQL, Array As Object(Buffer))
SQLDB.Close

I hope this helps.

 

[吳界明]

Thanks a lot!
But my database connection is MySQL not SQLLite, I have no Execnonquery2 method
SQLDB.ExecNonQuery2(strSQL, ArrayAs Object(Buffer)), the statement will be error after compiled
and if I add directly strSQL var. that will a message of "
Object converted to String. This is probably a programming mistake.

 

[Swissmade]

My Database is Mariadb and this is also MySQL.
You need the SQL Library for this JSQL.jar for B4J
This is working for me.

Private SQLDB As SQL
Private SQLDriver As String
Private SQLJDBC As String
SQLDriver = "com.mysql.jdbc.Driver"
SQLJDBC = "jdbc:mysql://"

Private Sub ConnectToDB
    If SQLDB.IsInitialized = False Then
        SQLDB.Initialize2(SQLDriver, SQLJDBC & " localhost:3306/student?characterEncoding=utf8","Your User", "Your Password")
    End If
End Sub

I always use connections in a SUB.
Hope this helps better.

 

[吳界明]

Thanks, Why I haven't the "SQLDB.Initialize2" method, I have been used the SQL Cipher

 

[Swissmade]

SQL Cipher

I don't use SQL Cipher see post https://www.b4x.com/android/forum/threads/sql-cipher.38677/

I assume that you are working in B4J and not B4A
You can use a lot of things from B4A in B4J but not all.

 

[吳界明]

I am working in B4A not B4J

 

[吳界明]

Now I just only use JSQL lib. and disabled the MYSQL, SQL ,SQLCipher lib. then
it can work correct, yes
thanks everyone

 

[Swissmade]

it can work correct

Happy Coding