ADORecordset & ADOConnection Library for MySQL

kuntasev · Dec 1, 2012

Dont work

Nothing.
I tried with targetSdkVersion 8 and targetSdkVersion 17.
In both cases I just always returns AdoConn.State = -1
The parameters are correct.
I connect seamlessly with the MySQL library with jdbc :
http://www.b4x.com/forum/additional...ates/22291-mysql-library-jdbc.html#post129399

Someone else has worked?
Some real example working?

thx

gerrymen · Dec 6, 2012

Has anyone got a working example of this ....

sigster · Dec 19, 2012

Hi

I have sample where I read from my Mysql database and safe it to sqlite on the mobile, on my Galaxy s 3 it is ok

but two friends have Android mobile with version 2.2.1
and it is not working

do this libraries don't work on older version of android
or is it the sqlite

Regards
Sigster

laguilar · Dec 20, 2012

AdoConnection.State exception?

I seem to be getting this error when connecting to MySQL on on my lg optimus v. however exact same apk works just fine on my Toshiba thrive AT100. same server/credentials/adodb library from here and the mysql-connector-java-5.1.22-bin.jar on both devices. when it errors on the lg device it only logs the output I have below and exits completely. this error happens whenever I call for the AdoConnection.State value. any ideas? thanks.

B4X:

Could not find method java.sql.Connection.isValid, referenced from method telesoft.b4a.mysql.AdoConnection.getState
VFY: unable to resolve interface method 11174: Ljava/sql/Connection;.isValid (I)Z

laguilar · Dec 20, 2012

sigster said:
Hi

I have sample where I read from my Mysql database and safe it to sqlite on the mobile, on my Galaxy s 3 it is ok

but two friends have Android mobile with version 2.2.1
and it is not working

do this libraries don't work on older version of android
or is it the sqlite

Regards
Sigster

oddly the LG Optimus V I have runs 2.2.1. MySQL works just fine but when I call for the AdoConnection.State value it errors. are you maybe calling for this value too? try commenting out any lines checking for that and see if it works for you. I hope this can get fixed so we can get this value on all devices as its very important to know if a connection is open or not before performing a query. in the case of an app im building I keep the connection open all the time. now and then roaming from one wifi access point to another causes a disconnect. so before each call to my sub running my queries I check the AdoConnection.State. If not open then I reconnect and continue with the query. normally I would open and close the connection as needed but opening and closing the connection all the time sometimes takes up valuable time in a business setting.

laguilar · Dec 23, 2012

might we see a fix for the AdoConnection.State for some devices? I posted my issue quite a few days ago and nobody has said anything about the situation. I really need this value to work properly before we can release our application. i'm sure I am not the only person needing this fixed. getting the state of the connection is a pretty important part of any sort of connection to a server.

Peter Simpson · Dec 25, 2012

If anybody's having issues getting this MySQL library to work. I would suggest using the following library. The link below works great with android:targetSdkVersion="8"

http://www.b4x.com/forum/showthread.php?t=22291

Sent from my Galaxy Nexus using Tapatalk 2

sigster · Dec 26, 2012

Sample

Hi

I was making sample with ADORecordset & ADOConnection Library for me to learn this is the result

the SQL file is just a random names I zip the file with the sql file

1. call data to Listview
2. select Listview I use InputList to select if I want to Edit or Delete record
3. Menu "Add New" to add new record

the question
how can I open the database only once in module for all form

B4X:

   AdoConn.ConnectionString("192.168.43.111","3306"," UTF-","demo","demo","demo")

Regards
Sigster

laguilar · Dec 27, 2012

AdoConnection.State workaround

this fix I came up with queries the uptime status variable of the server as a "test" to see if the connection is alive. it tries 3 times and if it fails then it alerts then exits the application, otherwise it sets the connected variable to true and continues. I placed this in the very beginning of a doQuery(query As String) sub I use. the commented code is what I was having a problem with prior to coming up with this workaround. the ConnectSQL is another sub I use to create the connection for the adoConn variable. hope its useful.

B4X:

Dim connected As Boolean = False
   Dim tries As Int = 1
   Do While tries <=3 AND connected = False
      Dim adoRS As AdoRecordset
      adoRS.InitializeEvent
      adoRS.Connection = adoConn
      adoRS.SqlStatement = "SHOW STATUS LIKE 'Uptime'"
      adoRS.MaxRecords = 1
      adoRS.Open
      If adoRS.RecordCount > 0 Then
         connected = True
      End If
      If connected = False Then
         If tries = 3 Then
            Msgbox("The connection to the server failed after 3 attempts." & CRLF & CRLF & "Please verify connectivity and re-run the application.", "Connection Error")
            ExitApplication
            Return
         Else
            ConnectSQL
            tries = tries + 1
         End If
      End If
   Loop
   'Code below caused errors for me on froyo
   'If adoConn.State = -1 OR adoConn.State = 0 Then
   '   ConnectSQL
   'End If

marco.canta · Dec 27, 2012

Hello, I have a problem with the ADO connection. The DB connects, and is open, but can not find Records. The recordcount is always "-1" even if there are more than 300 records in DB.
I use "DB4free.net" for my db "MySQL".

B4X:

Sub Button1_Click

   AdoConn.ConnectionString("88.198.43.24","3306","utf-8","db_gst","MyUserName","MyPassword")
   AdoConn.ConnectionTimeout=1
   AdoConn.LoginTimeout=1
   AdoConn.SocketTimeout=1

   AdoConn.InitializeEvent
   AdoConn.Open

   AdoRec.InitializeEvent
   AdoRec.Connection=AdoConn
   AdoRec.SqlStatement="select * from Elenco"
   AdoRec.Open

If AdoRec.isClosed Then
   ListView1.AddSingleLine("Ado.Rec = Close")
Else
   ListView1.AddSingleLine("Ado.Rec = Open")
End If

ListView1.AddSingleLine(AdoRec.RecordCount)

AdoRec.MoveFirst

ListView1.AddSingleLine(AdoRec.Field("Campo1"))
ListView1.AddSingleLine(AdoRec.Field("Campo2"))

If AdoRec.RecordCount>0 Then

Dim n As Int
n = 0
Do While Not(AdoRec.EOF)
   Log(AdoRec.Field("Cliente"))
   AdoRec.MoveNext
   n = n + 1
   
Loop
End If

   ListView1.AddSingleLine("Record " & AdoRec.MaxRecords)

AdoRec.Close

End Sub

Who can help me? Any suggestions?

THANKS!
Marco

laguilar · Dec 28, 2012

I don't see why it shouldn't work. a few differences between your code and mine (that works well)... UTF-8 is capitalized in my code, on the AdoRec object I set MaxRecords = 1000 (sufficient but not overwhelming for the device) before calling AdoRec.Open

marcoffio said:

Hello, I have a problem with the ADO connection. The DB connects, and is open, but can not find Records. The recordcount is always "-1" even if there are more than 300 records in DB.
I use "DB4free.net" for my db "MySQL".

B4X:

Sub Button1_Click

   AdoConn.ConnectionString("88.198.43.24","3306","utf-8","db_gst","MyUserName","MyPassword")
   AdoConn.ConnectionTimeout=1
   AdoConn.LoginTimeout=1
   AdoConn.SocketTimeout=1

   AdoConn.InitializeEvent
   AdoConn.Open

   AdoRec.InitializeEvent
   AdoRec.Connection=AdoConn
   AdoRec.SqlStatement="select * from Elenco"
   AdoRec.Open

If AdoRec.isClosed Then
   ListView1.AddSingleLine("Ado.Rec = Close")
Else
   ListView1.AddSingleLine("Ado.Rec = Open")
End If

ListView1.AddSingleLine(AdoRec.RecordCount)

AdoRec.MoveFirst

ListView1.AddSingleLine(AdoRec.Field("Campo1"))
ListView1.AddSingleLine(AdoRec.Field("Campo2"))

If AdoRec.RecordCount>0 Then

Dim n As Int
n = 0
Do While Not(AdoRec.EOF)
   Log(AdoRec.Field("Cliente"))
   AdoRec.MoveNext
   n = n + 1
   
Loop
End If

   ListView1.AddSingleLine("Record " & AdoRec.MaxRecords)

AdoRec.Close

End Sub

Who can help me? Any suggestions?

THANKS!
Marco

marco.canta · Dec 28, 2012

I did some more testing, but it does not work. recordcount always equal to -1. the problem may be that I use samsung s3 Android 4.1? or maybe my mysql server (db4free.net)?

Thanks Marco

marco.canta · Jan 3, 2013

:sign0085:

I have done tests but the program still does not work. the database is connected and open but the recordcount is always "-1" and therefore I can not read the record.
I tried to make a program in visual basic to read the mysql and the data is read.
Who can help me?

THANKS!
Marco

monchanch · Jan 27, 2013

Hello,
I have used this connection string in my vb6 code to connect to mysql database:
"DSN=SystemDSN;Driver=MySQL ODBC 3.51 Driver;Server=127.0.0.1;UID=**;PWD=**"
how should i write in this case?
I tried :AdoConn.ConnectionString("DSN=SystemDSN;Driver=MySQL ODBC 3.51 Driver;Server=127.0.0.1","3306","UTF-","databasename","user","pass")
and even tried this: AdoConn.ConnectionString("myipaddress","3306","UTF-","databasename","user","pass")
Though i dint get any error but recordcount is -1 in both the cases.

Heppy · Mar 17, 2013

Hi.

Please, sample how to INSERT INTO in the BD?

Connection and Select OK.

Thanks.

SOLVED, thanks

AdRec.AddNew

Sotiris · Apr 7, 2013

marcoffio said:
I have done tests but the program still does not work. the database is connected and open but the recordcount is always "-1" and therefore I can not read the record.
I tried to make a program in visual basic to read the mysql and the data is read.
Who can help me?

THANKS!
Marco

I had similar problem and I found the solution for me.
Follow the below steps:

1. First of all, check if the charset/collation of your Mysql database is "UTF-8" for sure.
2. Use uppercase characters for UTF-8 and check if your connection string is written correctly.
In my case the problem was an empty space (between " and UTF-8) in the connection string:
AdoConn.ConnectionString("192.168.1.3","3306"," UTF-8","database","user","pass")

3. Edit the manifest code (from menu > Project > Manifest Editor) delete all lines there and copy-paste the following:

B4X:

'This code will be applied to the manifest file during compilation.
'You do not need to modify it in most cases.
'See this link for for more information: http://www.b4x.com/forum/showthread.php?p=78136
AddManifestText(
<uses-sdk android:minSdkVersion="4" android:targetSdkVersion="8"/>
<supports-screens android:largeScreens="true" 
    android:normalScreens="true" 
    android:smallScreens="true" 
    android:anyDensity="true"/>)
SetApplicationAttribute(android:icon, "@drawable/icon")
SetApplicationAttribute(android:label, "$LABEL$")
SetApplicationAttribute(android:theme,"@android:style/Theme.Holo")
'End of default text.

If you have exactly the above code in your manifest then change android:targetSdkVersion="8" to android:targetSdkVersion="9".

giga · May 1, 2013

:icon_clap: Thanks Sigster for the helpful demo. Also Sotiris the SPACE before the UTF was a major hold up. MUCH RESPECT.

skywalker34 · May 10, 2013

Hi all,

I am trying this nice lib
my pb is when running I got this message : "A referenced library is missing: mysql-connector-java-5.1.22-bin" :sign0137::sign0085:

I am using windows seven 32bits

Thanks for any help

andre.astafieff · Jun 6, 2013

Strange, my code does not insert records in the database ...

B4X:

   AdoConn.ConnectionString(TxtServer,"3306","UTF-8",DataBaseOpen,"bebi","123456")
   AdoConn.ConnectionTimeout=1
   AdoConn.LoginTimeout=1
   AdoConn.SocketTimeout=1
   AdoConn.InitializeEvent 
   AdoConn.Open
   AdoRec.InitializeEvent
   AdoRec.Connection=AdoConn
   AdoRec.SqlStatement="INSERT INTO usuarios(usuario_emailgoogle) VALUES ('andre.astafieff@gmail.com');"
   AdoRec.Open         
   AdoRec.Update

what could be wrong?

Sytek · Jun 20, 2013

Sotiris said:
I had similar problem and I found the solution for me.
Follow the below steps:

1. First of all, check if the charset/collation of your Mysql database is "UTF-8" for sure.
2. Use uppercase characters for UTF-8 and check if your connection string is written correctly.
In my case the problem was an empty space (between " and UTF-8) in the connection string:
AdoConn.ConnectionString("192.168.1.3","3306"," UTF-8","database","user","pass")

3. Edit the manifest code (from menu > Project > Manifest Editor) delete all lines there and copy-paste the following:

B4X:

'This code will be applied to the manifest file during compilation. 'You do not need to modify it in most cases. 'See this link for for more information: http://www.b4x.com/forum/showthread.php?p=78136 AddManifestText( <uses-sdk android:minSdkVersion="4" android:targetSdkVersion="8"/> <supports-screens android:largeScreens="true" android:normalScreens="true" android:smallScreens="true" android:anyDensity="true"/>) SetApplicationAttribute(android:icon, "@drawable/icon") SetApplicationAttribute(android:label, "$LABEL$") SetApplicationAttribute(android:theme,"@android:style/Theme.Holo") 'End of default text.

If you have exactly the above code in your manifest then change android:targetSdkVersion="8" to android:targetSdkVersion="9".

..The Manifest Code Helped me too.
Just be careful with this one in Manifest 'cause messages does not display completely in API Level below 11

B4X:

SetApplicationAttribute(android:theme,"@android:style/Theme.Holo")

See This Setting Holo theme for Android application - Stack Overflow

Originally Posted by marcoffio View Post
I have done tests but the program still does not work. the database is connected and open but the recordcount is always "-1" and therefore I can not read the record.
I tried to make a program in visual basic to read the mysql and the data is read.
Who can help me?

THANKS!
Marco

I had to GRANT privileges into the user
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'some_password if you want one, if not leave it blank' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Note: If you set a password you will need to modify the auth_type to cookie for a username and password prompt at phpmyadmin..This is necessary 'cause if you do not modify the following it will not let you access you're phpmyadmin engine because of the password set

config.inc.php located at you're phpmyadmin folder

$cfg['Servers'][$i]['auth_type'] = 'config'; modify it to cookie if you set a mysql password
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = ''; Use you're mysql password if you set one in you're mysql user

see this Auth types - PmaWiki

Extra: ...Verify too you're not null fields
My device does not display the log errors in that case I use msgbox.
e.g.

B4X:

Sub AdoConnectionError (Message As String, State As String, ErrorCode As Int)
Msgbox("AdoConnectionError : " & CRLF & "Error message : " & Message & CRLF & "State : " & State & CRLF & "Error code : " & ErrorCode,"Connection Error")
End Sub

Sub AdoRecordsetError (Message As String, State As String, ErrorCode As Int)
Msgbox("AdoRecordsetError : " & CRLF & "Error message : " & Message & CRLF & "State : " & State & CRLF & "Error code : " & ErrorCode,"Recordset Error")
End Sub

Stored Procedures: Yes! you can call'em

Forgot Something I use WampServer, the web development platform on Windows - Apache, MySQL, PHP for Testing...

Nice Lib By the way
Thank's
:sign0098:

ADORecordset & ADOConnection Library for MySQL

Member

New Member

Active Member

Member

Member

Member

Expert

Active Member

Attachments

Member

Active Member

Member

Active Member

Active Member

Member

Active Member

New Member

Well-Known Member

Member

Member

Active Member