1. Welcome to our site! Electro Tech is an online community (with over 170,000 members) who enjoy talking about and building electronic circuits, projects and gadgets. To participate you need to register. Registration is free. Click here to register now.
    Dismiss Notice

Writing to database

Discussion in 'Arduino' started by BioniC187, Apr 5, 2013.

  1. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Hi guys,

    I want to store some data captured by the Arduino into an Sql DB for later viewing.
    I was wondering on the limitations of this.

    Looking at the baud rate, if I select 115200, using 8 bits per byte, not even including start and stop bits, you get a rate of 14400.
    Which is essentially 14.4KHz.

    On the Arduino side of things, I intend to be sampling at a rate of about 500KHz for about 5 seconds.

    So is there a way to work around this?

    How can i write to Sql at such high rates?

    Is there another method I can use for chucking in data to a pc from an Arduino without the high speed restriction?
     
  2. misterT

    misterT Well-Known Member Most Helpful Member

    Joined:
    Apr 19, 2010
    Messages:
    2,697
    Likes:
    368
    Location:
    Finland
    1) What are you sampling? single bits, 10 bit ADC values, 8 bit ADC values..??
    2) What kind of Arduino?

    BTW: If you plan to use the ATmega ADC you won't get even near 500kHz sampling rate.

    Anyway.. what you need is buffer memory. There is probably 8K bytes of internal SRAM in your uC. You write your samples to the buffer and at the same time start sending the data out as fast as you can.. and hope that you don't run out of buffer space.


    I did some calculations.

    Dout = data rate out (samples/second)
    Din = data rate in (samples/second)
    B = buffer size (samples)
    T = time (seconds)


    You must have:

    Dout > Din - (B/T)

    Example with your numbers:
    Data in = 500000
    Buffer = 1000 samples
    T = 5 seconds

    Dout > 500000 - (1000/5)
    Dout > 499800

    So.. even with 1000 sample buffer, you'll need data out rate of 499.8 kHz.


    Another example:
    Data in = 500000
    Data out = 14400
    T = 5 seconds

    B > T * (Din - Dout)
    B > 5 * (500000 - 14400)
    B > 2428000

    So.. with 500 kHz in and 14.4 kHz out for 5 seconds, you would need a 2.5 Megasample buffer.


    Yet another example:
    Data in = 500000
    Data out = 14400
    Buffer = 1000 samples

    T < B / (Din - Dout)
    T < 1000 / (500000 - 14400)
    T < 0.002059

    With 500 kHz in, 14.4 kHz out and 1000 sample buffer, you can sample for 2.059 milliseconds. That is 1029 samples.

    All the above is purely theoretical. Even if you can get 500kHz sample rate using arduino, you wont get 14.4kHz out and your SRAM probably cant hold 1000 sample buffer.
     
    Last edited: Apr 5, 2013
  3. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Thank you for your reply :)

    Firstly, it will be off an optical encoder. The encoder can have a top speed of up to 4xxKhz, so I plan to work of a max of 500KHz to be safe.

    I have a Mega 2560. BTW, it is only necessary to get from one phase of this encoder, as only one direction of rotation is important.

    Maybe i should move to an ARM system that can have a few megabyes of ram, that can hold all of the samples, then after it can send to the pc.

    Thank you for the theoretical calculations, it puts things in perspective. Tall task for such a small controller. Or maybe go for a lower resolution encoder. It all comes down to feasibility in the end. Don't want to break the bank for an ARM system, when all i gotta do is use a lower resolution encoder, or gear system
     
  4. dave

    Dave New Member

    Joined:
    Jan 12, 1997
    Messages:
    -
    Likes:
    0


     
  5. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10

    I just realised that i could use the R pi instead. And it's really cheap lol
     
  6. misterT

    misterT Well-Known Member Most Helpful Member

    Joined:
    Apr 19, 2010
    Messages:
    2,697
    Likes:
    368
    Location:
    Finland
    What kind of encoder? If it is a quadrature encoder then I don't think you'll need to send data at 400kHz even if the encoder itself is spinning at 400kHz. Usually you count the pulses for a constant time period and then send the pulsecount.
    Atmels XMega microcontrollers could be perfect for you. It has a quadrature decoder functionality.
     
    Last edited: Apr 7, 2013
  7. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Thanks, but I don't think that would be an option. I still need a high amount of RAM to store the pulses, I need to store the time between the pulses, that's the important information that I require.

    But yeah, it is a quadrature encoder, it is optical.

    I am really wanting to get the R pi, it has many peripherals that I want. The s-video, and the SD card slot is all a big plus to display and long term storage of the collected data. And yes, I am ignorant about ARM controllers, but the pi seems to have a lot on a nice little board. For what I currently want, I don't need a whole bunch of IO pins, I just need to read the encoder, and data from 3-4 other devices.

    I think the pi will suffice for my purpose :)
     
  8. misterT

    misterT Well-Known Member Most Helpful Member

    Joined:
    Apr 19, 2010
    Messages:
    2,697
    Likes:
    368
    Location:
    Finland
    Sounds like you have a good plan.. report here on your progress :)
     
  9. 3v0

    3v0 Coop Build Coordinator Forum Supporter

    Joined:
    Jul 14, 2006
    Messages:
    9,404
    Likes:
    227
    Location:
    OKLAHOMA USA
    SQL writes maybe too slow to use in real time. If you get into any trouble on the PC side you may want to initially write the data to a flat file and load it into the database after the run.
     
  10. misterT

    misterT Well-Known Member Most Helpful Member

    Joined:
    Apr 19, 2010
    Messages:
    2,697
    Likes:
    368
    Location:
    Finland
    You have a point there, but for 5 seconds of data coming from microcontroller through USART.. I think any PC/database can handle it. Real time? No.. Data logging? Yes.
    I don't know what you mean by "flat file", but my "old" PC has 4 GB of RAM.. the USART is the bottleneck.
     
    Last edited: Apr 7, 2013
  11. 3v0

    3v0 Coop Build Coordinator Forum Supporter

    Joined:
    Jul 14, 2006
    Messages:
    9,404
    Likes:
    227
    Location:
    OKLAHOMA USA
    I am not saying you are wrong but it depends on what he has for a PC and what OS he is running and how bogged down it is by addware, malware, etc. The guys running CNC from PCs are either running Linux or XP because anything past XP is not responsive enough.

    This is a reasonable definition of flat file (WebOPedia)
    My thinking was that it is faster to write to a file rather then executing SQL statements to store it.
     
  12. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Writing to anything is first limited by how fast data is received. So, usart will be slow for my data rate. He did the math ^^;)

    My plan is to write all data to the RAM, at the fastest rate possible - hopefully. Then after i "record" the 5 seconds, i will empty that out to the sql database.
    I think this will work :)
     
  13. 3v0

    3v0 Coop Build Coordinator Forum Supporter

    Joined:
    Jul 14, 2006
    Messages:
    9,404
    Likes:
    227
    Location:
    OKLAHOMA USA
    Sorry I bothered you. Next time I will not.
     
  14. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Have I offended you in some way? :confused:
     
  15. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Update :
    I have found out that it is possible to send data at higher that 115200 bps. I can get up to 1mbps on the arduino. So, I am currently looking into that, also, maybe even new usart hardware that transmits at even higher speeds. Also, you don't have to send 8 bits, you can send 5 bits. So that would mean that you could essentially send more "bytes" per second, increasing the amount of pulses sent to the database.

    I also got a database configured, and it accepts data up to microsecond accuracy
     
  16. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    I wrote a little program for the arduino which sends out the letter "a" to the pc at 1000 ms intervals.

    I've been fooling around with c#, and i made a little program that reads the serial port and displays the time interval between pulses, coz i wanted to check the accuracy.

    the results aren't good. the interval is constantly changing.

    Ignore all the nonsense and only bother to look at the line that says "INTERVAL:"

    arduino serial test.jpg

    This could be due to a the pc timer generating the microsecond timestamp not being accurate, and then it could the arduino coz i used delay(1000) in the program. Tomorrow i will write the program with OVF timer or COMP timer and see how much better my accuracy becomes and will help me to deduce what is causing the non-constant time interval
     
  17. misterT

    misterT Well-Known Member Most Helpful Member

    Joined:
    Apr 19, 2010
    Messages:
    2,697
    Likes:
    368
    Location:
    Finland
    The "PC timer" is really not that good for accurate timing. Are you using the "System.Diagnostics.StopWatch" or "System.Timers.Timer"? You should be using the diagnostics StopWatch.

    For very accurate timing you can use Win32 multimedia timer. But, for this purpose I don't think it is worth the effort. I think the rs232 communication also introduces jitter when the received byte passes through all the drivers, operating system stuff and .Net runtime environment.

    If you are interested to do very accurate timing, then take a look at links below:
    http://www.codeproject.com/Articles/17474/Timer-surprises-and-how-to-avoid-them
    http://www.codeproject.com/Articles/98346/Microsecond-and-Millisecond-NET-Timer
    http://www.codeproject.com/Articles/5501/The-Multimedia-Timer-for-the-NET-Framework
    http://www.codeproject.com/Articles/2635/High-Performance-Timer-in-C
     
    Last edited: Apr 23, 2013
  18. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Actually, I'm just using "string dt = DateTime.Now.ToString("HH:mm:ss.ffffff");" . But you're right, I was doing some reading, and the stopwatch has good accuracy. I'm gonna write the arduino code to help reduce my error in timing from that side, and give stopwatch a go. Hopefully it'll yield good results.

    Thanks mate :)
     
  19. misterT

    misterT Well-Known Member Most Helpful Member

    Joined:
    Apr 19, 2010
    Messages:
    2,697
    Likes:
    368
    Location:
    Finland
    Ok, yeah. You picked probably the worst method for timing in C#.

    I have not used much Arduino IDE. Bad thing about it is that it hides much of the details. For accurate timing (in microcontroller) you should use Timer interrupts.

    You know that you can write your code in pure C using the free Atmel Studio and upload the compiled hex file into the arduino board (with uploader program like this: http://kmtronic.com/upload-hex-files-to-arduino.html).
    If you want to seriously learn/program microcontrollers I would recommend you ditch the Arduino IDE and learn proper C.
     
    Last edited: Apr 24, 2013
  20. BioniC187

    BioniC187 Member

    Joined:
    Aug 9, 2011
    Messages:
    281
    Likes:
    10
    Ah yes, I've seen that Atmel studio around. Didn't think about trying it though.

    Luckily, I'm a quick learner with programming.

    But even though, what will be the advantages of ditching the Arduino software, and coding in pure C?

    If the advantages are significant I will definitely move on to program in pure C
     
    Last edited: Apr 25, 2013
  21. misterT

    misterT Well-Known Member Most Helpful Member

    Joined:
    Apr 19, 2010
    Messages:
    2,697
    Likes:
    368
    Location:
    Finland
    Using Arduino sketch functions like "analogRead()" hides all the details and does not let you fully control the ADC conversion. You might want to change the conversion time for more stable readings etc. Or you might want to run the ADC in "free running" mode.

    Coding with pure C gives you full control of the microcontroller.. and you will learn how the microcontroller actually works. And then you can do relly impressive stuff like write your own multitasking kernel or generate TV signal with an 8bit uC. Like this: http://www.youtube.com/watch?v=z41MToCukc0

    Arduino IDE hides everything from you and you won't learn anything. Have you even ever read the atmega datasheet? I bet you don't have to if you use Arduino IDE.

    EDIT: In your UART communication the Arduino sketching is a huge bottleneck. For max speed you need to use UART interrupts with software buffer to send data.
     
    Last edited: Apr 25, 2013

Share This Page