ADORecordset & ADOConnection Library for MySQL

S

sigster

Active Member
Licensed User
Longtime User
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
 
L

laguilar

Member
Licensed User
Longtime User
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
 
Last edited:
L

laguilar

Member
Licensed User
Longtime User

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.
 
L

laguilar

Member
Licensed User
Longtime User
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.
 
S

sigster

Active Member
Licensed User
Longtime User
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
 

Attachments

  • Mysql_demo.zip
    14.8 KB · Views: 363
L

laguilar

Member
Licensed User
Longtime User
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
 
Last edited:

marco.canta

Active Member
Licensed User
Longtime User
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
 
L

laguilar

Member
Licensed User
Longtime User
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

 

marco.canta

Active Member
Licensed User
Longtime User
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
 
Last edited:

marco.canta

Active Member
Licensed User
Longtime User
: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
 
M

monchanch

Member
Licensed User
Longtime User
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.
 
Last edited:
S

Sotiris

New Member
Licensed User
Longtime User

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".
 
Last edited:
S

skywalker34

Member
Licensed User
Longtime User
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
 
Last edited:
A

andre.astafieff

Member
Licensed User
Longtime User
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?
 
S

Sytek

Active Member
Licensed User
Longtime User

..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

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
Click to expand...
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
Click to expand...

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

Nice Lib By the way
Thank's
:sign0098:
 
Last edited:
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…