• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Getting data in realtime from Ms SQL with java

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello
l need how to stream, in realtime, the changes made to a table in a Microsoft SQL server  database to a java application
 
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the Ranch

I presume you are already familiar with Java‑database connections? I am not convinced you can stream real‑time data from a database. It takes time to enter data into the database, you would probably want to do that via a transaction which must complete first, an network connections will slow things down, so who knows how long it will take for the data to go from source to your app. It could be a second or even more.
What would happen if you have a trigger on some of your data to run a query whenever you update something?

Moving to our databases forum.
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Welcome to the Ranch

I presume you are already familiar with Java‑database connections? I am not convinced you can stream real‑time data from a database. It takes time to enter data into the database, you would probably want to do that via a transaction which must complete first, an network connections will slow things down, so who knows how long it will take for the data to go from source to your app. It could be a second or even more.
What would happen if you have a trigger on some of your data to run a query whenever you update something?

Moving to our databases forum.



That was my thought at well. Databases don't push data, database clients have to pull it. And streaming in the Java sense isn't record-oriented like database requests are. I think we need to know more about what it is that needs to be done without assuming how (that is, "streaming") it should be done.
 
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

l need how to stream, in realtime, the changes made to a table in a Microsoft SQL server  database to a java application


I looked at this recently - I wanted to get a stream of database changes which I could feed it in to Kafka to do some real-time analysis.

The two approaches that I read about are to either query the database tables periodically and look for changes (may require an additional field such as a sequnece number or timestamp to identify when a record has been inserted/changed), or to watch the database's redo log (change log / commit log / journal) and use the extract the change events (similar to how one database would synchronize/replicate with another).

The first approach is probably fine for smaller data sets, but unlikely to scale well when dealing with a large number of records.  The second approach requires you to understand how the structure of redo log how to interpret the data - probably not an easy task.

A third approach, which would require changes to the code, would be to enhance the data layer so that in addition to making changes to the database, that it would also generate the event information you want.
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ron McLeod wrote:

l need how to stream, in realtime, the changes made to a table in a Microsoft SQL server  database to a java application


I looked at this recently - I wanted to get a stream of database changes which I could feed it in to Kafka to do some real-time analysis.

The two approaches that I read about are to either query the database tables periodically and look for changes (may require an additional field such as a sequnece number or timestamp to identify when a record has been inserted/changed), or to watch the database's redo log (change log / commit log / journal) and use the extract the change events (similar to how one database would synchronize/replicate with another).

The first approach is probably fine for smaller data sets, but unlikely to scale well when dealing with a large number of records.  The second approach requires you to understand how the structure of redo log how to interpret the data - probably not an easy task.

A third approach, which would require changes to the code, would be to enhance the data layer so that in addition to making changes to the database, that it would also generate the event information you want.



For something like that, I'd probably either poll the database or attach a trigger to the table(s) of interest. It would depend on how much latency I could tolerate and how much data I'd be watching over.

Triggers could simply touch a change table that would allow "one stop shopping" for changes so that a poll could just test to see if there was data to pull or, for really low-latency operations, the trigger might reach out of the database via JMS or MQ or something like that. And for really, really low latency, I'd want an application that did the database changes that could be hooked to a message service so that it would be direct app-to-app notifications.
 
Campbell Ritchie
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I merged your stuff with the following thread. I hope that is okay by you.
 
pen pene
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello
I want to make a client application java that connects to a ms SQL database server, and displays and auto synchronizes in real time with a database server table, this table changes frequently, and I want to display this table in permanent
how to do, I would like to have an idea
Thank you
 
Rancher
Posts: 1171
18
IntelliJ IDE Hibernate Firefox Browser MySQL Database Spring Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the ranch. Sorry for the late responce, but once in a while a post fall through the cracks, especially when posted during the holidays. I don't think there is a function that let your program know when the table changed, what you could do however a schedule a task that does that for you.
You could set the task up to querry the table for example every hour our whatever you need and display the results
 
Campbell Ritchie
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You appear to have an answer in your other thread; I think it better the two become one.
 
Daniel Demesmaecker
Rancher
Posts: 1171
18
IntelliJ IDE Hibernate Firefox Browser MySQL Database Spring Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Now that I ready your previous post, I guess my answer is mute, since I suspect that's what Ron ment by querying the table periodicly
 
Tim Holloway
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Daniel Demesmaecker wrote:I guess my answer is mute moot,



I'm sorry. You'll have to tow the line. We simply cannot allow free reign to improper English usage in our forums or we'll loose our professional image.  
 
Sheriff
Posts: 17644
300
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Seems like WebSocket might be something you could use for this kind of requirement as well.
 
reply
    Bookmark Topic Watch Topic
  • New Topic