B4R Question mysql connector for B4R

[Michael Sergio]

Hello.
I have used the SQL connector for MySQL database on Windows in some of my projects in B4J.

#Region  Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
    #AdditionalJar: mysql-connector-java-5.1.39-bin.jar
#End Region

...
Private pool As ConnectionPool
pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://" & SqlServerIp & ":port/smt?autoReconnect=true&useSSL=true","user","password")
    Dim sql1 As SQL=pool.GetConnection   
' My code
    sql1.Close

My question is if i can do the same with an Arduino Mega + ethernet shield, I know that the jSQL library is based on java , but i saw here https://github.com/ChuckBell/MySQL_Connector_Arduino that there is a connector for mysql.
I should mention that I don`t really know how to work with http requests, and i dont have some jServer that i can connect to also I dont have xammp or other http server . I just want to store/read some data from mySQL server directly like i did with jSQl.
I don`t know if that`s possible, any help would be appreciated.

On a second note, i noticed that on my MEGA 2560 with a shield attached i have :

If eth.InitializeDHCP(MacAddress) = False Then
        Log("Error connecting to network.")
        Return
    Else
        Log("Connected to network. My ip address: ", eth.LocalIp)
    End If

always returns after a while "Error connecting to network", but if i have:

eth.Initialize(MacAddress,Array As Byte(192, 168,1, 126))
    Log("IP= " ,eth.LocalIp)

it returns the ip i`ve set, i don`t know why my DHCP server wont give me an ip.

Thank you.

 

[Erel]

On a second note, i noticed that on my MEGA 2560 with a shield attached i have :

Please start a new thread for the second question.

You cannot connect the Arduino directly to the database. However you can use the modified jRDC2 that supports MQTT: https://www.b4x.com/android/forum/threads/rdc-based-on-mqtt.72416/#content

 

[Michael Sergio]

Thank you for your help. I know almost nothing about mqtt, but i managed to populate the database with your default example in table animals.
I downloaded the broker, connected both jRDC and RDC(mega) to the broker, and every second the database was populated with "abc".
Question about selected data:
I have : "SELECT DBCode, FullCode,Number FROM xxx.variable WHERE DBCode =( SELECT max(DBCode) from xxx.variable)"
When i receive the data i have in Log(data(i)) the result:

select - tag: tag, number of rows: 1
10561
21202152125314
2

How can I assign individually each of the lines to different variable when i receive the data for ex:
DBcode = 10561
FullCode = 21202152125314
Number = 2
Sorry if its simple and i dont know it.

I also resolved my DHCP not assigning IP on my shield, it seems that ,since i have a Chinese clone, its not working via switch because the clone "incorrectly include a 510 Ohm resistor network (labelled 511) on the network cable connection instead of the specified 49.9 Ohm resistor network" ( source: https://forum.arduino.cc/index.php?topic=351477.15) .
So i switched the resistor network with 50 Ohm network /bar and now its working connected to a switch + router.
Thank you again.

 

[Peter Simpson]

Hmm, I find that library is interesting on Github. I tried the library and yes it works perfectly fine with an Mega based microcontroller board and a cheap Chinese shield Network shield, to my complete surprise it worked firs out connecting to a test database on my online server . Please note that I used Inline C and it ran relatively smoothly. Before you ask I have already scrapped the test code as it's a terrible idea.

Why do you need to use and Arduino with MySQL, what are you doing?
I would personally opt for an ESP8266 based microprocessor board like the NodeMCU which has a quicker processor, more memory and has built-in WiFi, all of which is a huge advantage over the Mega any day of the week. I would also use MQTT or maybe even Google Firebase Database, but that completely depends on what you are doing in the first place.

 

[Michael Sergio]

Basically i already have a project in b4j, that im trying to convert it to b4r more or less.
My project is currently 80% functional on a rasp pi3.
Why i tought Mega:
First problem...i need 3 hardware serials.
I have a printer connected, a note acceptor and a game board each on a serial in pi (i had connected 3 prolific usb to serial converters).
The process goes like this... i pool the db, if i have some data for this specific pi ( i binded the ip adress to pi mac and i've gave it a number) then the pi takes the data which contains a barcode number, the time it was created, a value (sum) ..., then it prints that data to the printer. The pi also has the role to communicate with the note acceptor . If money comes in, the data from serial 2 goes directly to the game board ( serial 3). If instead a ticket printed with the barcode is in the acceptor's escrow, the pi checks in the database if that specific barcode exists, or if its already been used, and if its valid, it converts it into data ( value) that the game board knows and sends it via the 3rd serial.
I tought maybe i'll try to make this work with an arduino board, since the pi does have just 1 serial on the pins,and it takes too long for my liking to boot (20-25 seconds with linux) but first i needed to see if i can connect my ardu to mysql somehow. I'm pretty new with ardu boards, idk maybe i cant do this project because of the memory size being too small.
I have also some espwifi boards, but i really need those serials, and sw serial won't cut it, since i need to send- receive data acurattely plus i have different parities and stopbits required for the serials.
I forgot...i have a system (server and 6-7 clients in the same lan). On the server i have some kind of automated program that has a note acceptor and a note dispenser.
The process:
Insert a barcode tichetgenerated from one of my clients , if valid then a cashout occurs from the dispenser.
Edit:

Before you ask I have already scrapped the test code as it's aterrible idea.

May I ask why its a terrible idea? Maybe memory issues?
I dont know how to use inline code so i never tried it in b4r.


Sry English is not my primary.

 

[Peter Simpson]

You said it @Michael Sergio, memry and it not exactly the quickest solution. I always delete anything that I will never use, even if I get it working as expected that's just me. I used Inline C to get the code in your link working correctly, but it's not really a good idea and it was not the fastest either.

@Erel's solution really is the best solution for you...

 

[Erel]

How can I assign individually each of the lines to different variable when i receive the data for ex:
DBcode = 10561
FullCode = 21202152125314
Number = 2

Dim DBCode As String = data(0) 'type depends on the the database type
Dim FullCode As String = data(1)
Dim Number As Int = data(2)