[chargeable] MSMySQL - Yet another MySQL-Library (but a FAST one :-))

maurizio barbera · Feb 3, 2016

ExecutePeparedStatement
PeparedStatement (sql As String)
SetPeparedBlob (parameterIndex As Int, imagepath As String)
SetPeparedInt (parameterIndex As Int, x As Int)
SetPeparedLong (parameterIndex As Int, x As Long)
SetPeparedString (parameterIndex As Int, x As String)

how these commands? sample code?
Thanks

DonManfred · Feb 3, 2016

maurizio barbera said:
yes... receive e mail... thanks... but what's the password?

Look in the email

DonManfred · Feb 3, 2016

maurizio barbera said:
how these commands? sample code?

at the present state these methods does not work;

- i already have changed the library in the meantime to fix this; but sadly it is not tested as yet. And due to this it is not released as yet.

Will do an update in the next days and also provide an example for using prepared statements.

For now you will need to use normal queries but not prepared ones...

maurizio barbera · Feb 4, 2016

DonManfred said:
at the present state these methods does not work;

- i already have changed the library in the meantime to fix this; but sadly it is not tested as yet. And due to this it is not released as yet.

Will do an update in the next days and also provide an example for using prepared statements.

For now you will need to use normal queries but not prepared ones...

thank you, when you update the library ... I can use the link you sent me by email ?? lifetime upgrade ??

DonManfred · Feb 4, 2016

maurizio barbera said:
I can use the link you sent me by email ?? lifetime upgrade ??

Yes

maurizio barbera · Feb 4, 2016

DonManfred said:
Yes

thank's

hookshy · Feb 9, 2016

I need to make my first example work and I have problems runing the code

here is my sql test database

B4X:

db.Initialize("MySQL","sql2.freemysqlhosting.net","sql2105643","uZ6zY9","sql2105643")

Here is the query result
data.size is fireing a null pointer exception
my database has 3 elements ...with id and name as keys

Query done find is working but data.size is rising a null pointer exception ... what to do next ?

B4X:

Sub MySQL_QueryResult(data As List, meta As Map)
    'Dim m As Map = meta
    'Log("MySQL_QueryResult("&meta&")")
'ToastMessageShow(meta.Get("RecordCount") & " rows retrieved in " & meta.Get("ms") & " milliseconds", True)
    ToastMessageShow("QUERY DONE FIND",True)
  
    For i=0 To data.Size-1
'        'Log("r:"&data.Get(i))

'Dim COL As Map
'COL=data.Get(i)
'Log(COL.Get("id"))
'Log(COL.Get("nume"))
    Next
  
End Sub

here is the error ... a null pointer exception due to data.size ... toastmessage with data.size does not work do not understand how to start and make my example working for the first time

B4X:

 //BA.debugLineNum = 79;BA.debugLine="For i=0 To data.Size-1";
{
final int step26 = 1;
final int limit26 = (int) (_data.getSize()-1);
for (_i = (int) (0); (step26 > 0 && _i <= limit26) || (step26 < 0 && _i >= limit26); _i = ((int)(0 + _i + step26))) {

DonManfred · Feb 9, 2016

hookshy said:
db.Initialize("MySQL","sql2.freemysqlhosting.net","sql2105643","uZ6zY9","sql2105643")

Tried with heidiSQL. This is the error i got

I tried it again with msMySQL lib (and added logoutput when a connection error raised...

** Activity (main) Create, isFirst = true **
Can not connect to the MySQL Database Server. Please check your configuration.
Hostname: sql2.freemysqlhosting.net
Username: sql2105643
Error: Access denied for user 'sql2105643'@'ip-88-153-212-1.hsi04.unitymediagroup.de' (using password: YES)
Raising.. mysql_status
not Connected to Database
** Activity (main) Resume **
MySQL Database not connected!
MySQL_Status(false,false,0)
** Activity (main) Pause, UserClosed = false **
** Activity (main) Resume **

Can you recheck your credentials please?

It is either
- not allowed to access this database from "outside"
or
- the credentials you provided are wrong

hookshy · Feb 9, 2016

DonManfred said:
Can you recheck your credentials please?

fixed : the pasword was wrong

B4X:

db.Initialize("MySQL","sql2.freemysqlhosting.net","sql2105643","uZ6*zY9*","sql2105643")

Q : since a data base is not allways about strings in a table ..how do you deal with large files, pictures ...and so on
I heard about transfer the files with ftp to your server then I must get the url back and store it to a key in the database ...or build a new table with ids of pictures and urls with ids coreponding to the id of the post

Is there a solution to convert the picture to byte array or encode to base64 ? but will it be fast enough to get 100 pictures and convert them from byte array ?
Then there is problem loading it to a list which from previous experience is a problem dealing with out of memory issues when loading large lists with a buch of pictures

if you store the pictures with an base64 encoding string then from the server the pictures are not availlable to view or edit ... and so on
the previous library I have used the pictures were stored as objects and each object had a individual url

I need to make my scenario work with a basic news feed ... one posting pictures and articles ..others viewing the content in a list
So : How do you do it , dealing with files and pictures ?

DonManfred · Feb 9, 2016

hookshy said:
Is there a solution to convert the picture to byte array or encode to base64 ?

Not as feature of this lib. BU you can use byte-arrays for blobs...
The Northwind-Example i used use this technique....

hookshy said:
So : How do you do it , dealing with files and pictures ?

Here a snippet from my example whic reads the blob and show the picture inside a listview

B4X:

Sub MySQL_QueryResult(data As List, meta As Map)
    'MySQL_QueryResult2((MyMap) {ColumnCount=3, RecordCount=5, ms=32})
    Log("MySQL_QueryResult(Columns "&meta.get("ColumnCount")&", Records " _
    &meta.Get("RecordCount")&","&meta.Get("ms")&"ms,"&meta.Get("TaskID"))
    ToastMessageShow("MySQL retrieved "&meta.Get("RecordCount")&" records in "&meta.Get("ms")&"ms",True)
    lv2.Clear
    For i=0 To data.Size-1
        If meta.Get("TaskID") = "Categories" Then
            Dim cur As Map = data.Get(i)
            Dim bmp As Bitmap
            Dim In1 As InputStream
          Dim buffer() As Byte           
            buffer = cur.Get("Picture")
            In1.InitializeFromBytesArray(buffer,0,buffer.Length)
            bmp.Initialize2(In1)           
            lv2.AddTwoLinesAndBitmap(cur.Get("Description"),cur.Get("CategoryName"),bmp)
            'Log("r:"&data.Get(i))
        Else If meta.Get("TaskID") = "Employees" Then
            Dim cur As Map = data.Get(i)
            Dim bmp As Bitmap
            Dim In1 As InputStream
          Dim buffer() As Byte           
            buffer = cur.Get("Photo")
            In1.InitializeFromBytesArray(buffer,0,buffer.Length)
            bmp.Initialize2(In1)           
            lv2.AddTwoLinesAndBitmap(cur.Get("FirstName")&" "&cur.Get("LastName"),cur.Get("Title"),bmp)
            'Log("r:"&data.Get(i))
        Else
            'Log("r:"&data.Get(i))
        End If
    Next   
End Sub

To write blobdata you need to use prepared statements... But there was a problem in the past... I think i fixed it but i had not the tie as yet to buid an example for it

hookshy · Feb 10, 2016

DonManfred said:
To write blobdata you need to use prepared statements... But there was a problem in the past... I think i fixed it but i had not the tie as yet to buid an example for it

Ok , looking forward to hearing from you ..Good luck

maurizio barbera · Feb 12, 2016

hi, can i use 'AS' in query... example: select id as code from table... i try but no result return

can you help me

thanks

DonManfred · Feb 13, 2016

code is a reserved keyword in mysql. It will not work!
Try use another name

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

maurizio barbera · Feb 14, 2016

sorry ... it was an example. another example: SELECT description AS My_Name FROM TABLE

DonManfred · Feb 14, 2016

maurizio barbera said:
another example: SELECT description AS My_Name FROM TABLE

You are right... It did not work in the version you have.

V1.0.8 gives this result

B4X:

    db.QueryASync("select Description as My_Name from Categories;","test")

** Activity (main) Create, isFirst = true **
Raising.. mysql_status
Connected to Database
** Activity (main) Resume **
Activity_Resume()
MySQL_Status(true,false,0)
MySQL_QueryResult(Columns 1, Records 11,77ms,test
(MyMap) {My_Name=Soft drinks, coffees, teas, beers, and ales}
(MyMap) {My_Name=Sweet and savory sauces, relishes, spreads, and seasonings}
(MyMap) {My_Name=Desserts, candies, and sweet breads}
(MyMap) {My_Name=Cheeses}
(MyMap) {My_Name=Breads, crackers, pasta, and cereal}
(MyMap) {My_Name=Prepared meats}
(MyMap) {My_Name=Dried fruit and bean curd}
(MyMap) {My_Name=Seaweed and fish}
(MyMap) {My_Name=null}
(MyMap) {My_Name=null}
(MyMap) {My_Name=null}
** Activity (main) Pause, UserClosed = false **
** Activity (main) Resume **
Activity_Resume()

hookshy said:
Ok , looking forward to hearing from you ..Good luck

I´ve tested it with V1.0.8 and it worked with this code

B4X:

    db.PeparedStatement("Insert into Categories SET CategoryName=?, Picture=?;")
    Dim b() As Byte = Bit.InputStreamToBytes(File.OpenInput(File.DirAssets,"main.bal"))
    db.SetPeparedBytes(2,b)
    db.setPeparedString(1,"TEST")
    db.ExecutePeparedStatement

V1.0.8 can be downloaded from my Dropbox-Share

I send out a Mail to all donators with the new Sharelink

maurizio barbera · Feb 16, 2016

info... on 'Initialize' command found new parameters 'UseStrictMode' and 'UseDebugOutput'... how do they work?

DonManfred · Feb 17, 2016

You need a description on how to use TRUE or FALSE?

maurizio barbera · Feb 17, 2016

DonManfred said:
You need a description on how to use TRUE or FALSE?

yes... thanks

DonManfred · Feb 17, 2016

True = YES
FALSE = NO

maurizio barbera · Feb 17, 2016

DonManfred said:
True = YES
FALSE = NO

I'm sorry, I explained bad, I wanted to know what are those new parameters and how to use them ... what if I put true or false?
i know true/false yes/no 1/0 etc tc

[chargeable] MSMySQL - Yet another MySQL-Library (but a FAST one :-))

Member

Expert

Expert

Member

Expert

Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Member

Expert

Member

Expert

Member

Expert

Member

Expert

Member

Privacy & Transparency

Privacy & Transparency