simple multiple records from database

smugford

I have got the following working using JDBC and MySQL

http://www.lightstreamer.com/vb/showthread.php?t=231&page=2

Somebody commented on the example:

Quote:
Originally Posted by gerard
I don't see in the code why it just reads the "first record" - isn't it looping over the entire record set and sending them all.

Because in this example we suppose that the first record maps a subscribed item. So, each change in this record is pushed to the clients. In real-world scenarios, you could have different mappings.

I've looked through all the examples an example that i can only update a couple of records

is there anywhere in the documentation that explains how to update a couple of records or explains mapping records and how to use them.

thanks.
Scott

Dario Crivelli

We have never tried to suggest a mapping between relational database concepts and lightstreamer concepts.

As a simple rule:
an item in MERGE mode corresponds to a record in a relational table (possibly obtained through a join) identified by a predetermined unique key,
whereas an item in COMMAND mode corresponds to a whole table (possibly obtained through a join) which supports a unique key.

However, this is only a logical mapping and all the conversion stuff has to be performed by the Data Adapter.

Did I address the question?

smugford

Not really, i may not be asking the right question.

Basically what i am trying to do is change the stock list demo in java from from the external feed simulator to a real database feed.

it doesn't have to be as complicated as the demo just updating a couple of records from the db would be fine for now.

table row 1 field one: update me with record one
table row 2 field one: update me with record two

I the database demo it shows how to update only one record using the subscribe function.

what i need to know is how to update multiple records using the subscribe function.

i may just be missing the concept of how subscribe works?

smugford

i went over the general concepts pdf again.

I definitely want MERGE mode.

Dario Crivelli

I'm not sure if you still need an answer.

So, for now, I just clarify that subscribe tells you when some client needs the updates related with a row; moreover, the supplied item name tells you which row.
For this reason, you have to predetermine the item names in such a way that they refer to rows; the obvious way is using a unique key field associated to the rows.

Once you have received the request through subscribe(and until you receive unsubscribe ), it's up to you to observe the row and send updates upon changes (and the initial row contents as the snapshot, if needed).
I would prefer not to suggest a way of doing that; just note that, as shown in the tutorial, that should be performed by your own threads.

smugford

thank you, i do understand the concept of subscribe

Since the example shows that are are only subscribed to own item my question is where do you actually loop through the result set to subscribe to each item (with the unique key)

if i change the if (rs.next()) to while (rs.next()) it loops through all the records in the table but it updates only the first.

Can you show me where i would modify this code to subscribe to the unique items.

Thank you very much
Scott

import java.util.*;
import java.io.File;
import java.sql.*;
import com.lightstreamer.interfaces.data.*;

public class HelloWorldDataAdapter implements SmartDataProvider {

    private ItemEventListener listener;
    private volatile GreetingsThread gt;
    private Statement command;
	private final HashMap subscribedItems = new HashMap();
	/*
	 * used for the IndexedItemEvent version only (currently commented out)
     */
    private static final String[] names = new String[]{"message", "date"};

    
	
	
	public void init(Map params, File configDir) throws DataProviderException {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String dbFile = "ls";
            String database = "jdbc:mysql://localhost:3306/ls";
            String url = "jdbc:mysql://localhost:3306/";
		String dbName = "ls";
			
			Connection conn = DriverManager.getConnection(url+dbFile, "user", "pass");
            command = conn.createStatement();
        } catch (Exception ex) {
            System.out.println(ex);
            throw new DataProviderException(ex.getMessage());
        }
    }

    public boolean isSnapshotAvailable(String itemName) throws SubscriptionException {
        return false;
    }

    public void setListener(ItemEventListener listener) {
        this.listener = listener;
    }

    public void subscribe(String itemName, Object itemHandle, boolean needsIterator)
            throws SubscriptionException, FailureException {
        if (itemName.equals("greetings")) {
            gt = new GreetingsThread(itemHandle);
            gt.start();
        }
    }
    
    public void subscribe(String itemName, boolean needsIterator)
                throws SubscriptionException, FailureException {
    }           

    public void unsubscribe(String itemName) throws SubscriptionException,
            FailureException {
        if (itemName.equals("greetings") && gt != null) {
            gt.go = false;
        }
    }

    class GreetingsThread extends Thread {

        private final Object itemHandle;
        public volatile boolean go = true;

        public GreetingsThread(Object itemHandle) {
            this.itemHandle = itemHandle;
        }

    
	


		public void run() {
            int c = 0;
            Random rand = new Random();
            
			while(go) {
                try {
					ResultSet rs = command.executeQuery("select * from data");       
                    
					
					
					
					if (rs.next()) {
                        Map<String, String> data = new HashMap<String, String>();
                        data.put("message", rs.getString("message"));
                        data.put("timestamp", rs.getString("timestamp"));
                        listener.smartUpdate(itemHandle, data, false);

                    }
                } catch (Exception ex) {
                    System.out.println(ex);
                }                    
                
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                }
            }
        }
    }

}

Dario Crivelli

As far as I am concerned, I can only provide some guidelines, not the actual code.

First of all, the "data" database table should be extended with a third column, which should be declared as a unique key. As you recalled the StockList Demo, let's call it "stock_name".

Then your client page, in its extended form with two rows, should associate two item names to the two rows. In place of the "greetings" item name, you could use two stock names, for instance GOOG and MSFT.

You should then populate the table with data, using "GOOG" and "MSFT" as "stock_name" and setting suitable text in the "message" column.
You can even setup a process which changes the values in the "message" column over time.

Then the implementation of the Data Adapter should be extended in such a way that two possible threads can be spawned, depending on the item that is being subscribed to:

[SYNTAX="JAVA"]
public void subscribe(String itemName, Object itemHandle, boolean needsIterator)
throws SubscriptionException, FailureException {
if (itemName.equals("GOOG")) {
gtGOOG = new GreetingsThreadGOOG(itemHandle);
gtGOOG.start();
}
if (itemName.equals("MSFT")) {
gtMSFT = new GreetingsThreadMSFT(itemHandle);
gtMSFT.start();
}
}
[/SYNTAX]

The two different threads will differ in the way they perform the query:

[SYNTAX="JAVA"]ResultSet rs = command.executeQuery("select * from data where stock_name='GOOG' "); [/SYNTAX]
vs
[SYNTAX="JAVA"]ResultSet rs = command.executeQuery("select * from data where stock_name='MSFT' "); [/SYNTAX]

Obviously, the above is the worst possible way in which you can generalize the code to two or more items,
but doing that in a better way is just a refactoring matter.

smugford

your suggestion is the first thing that i thought of but as you said it's not the best way.

If it was just going to be the two it would be fine. But i'm sure it's going to extend through a loop.

In the stocklist example i see a syncronize(SubscribedItems) function.

I do understand what your talking about with the index key. I develop php web apps using mysql so the unique key concept is not a new one to me.

I think all i need is to know how to make the subscribed items based on those keys loop through and subscribe.

Can i pay someone to actually do this code?

If i can't find a solution I will have to look at a different product which is disappointing because i'm sure lightstreamer is more than capable of doing what we want

It seems like it would be really easy for someone with just a little bit of experience.

Thank you very much for your help i'll continue trying to make it work.

Dario Crivelli

Let me add a few Lightstreamer-related notes.

If your aim is to perform one single loop, fetching all the rows of the "data" table at regular times,
so that, upon each row found, you can update the corresponding subscription (i.e. the item which corresponds with the row key),
this is also feasible.
Just consider that, for each line found, you will have to check whether the corresponding item is currently subscribed to,
because only in this case can updateor smartUpdate be invoked
(hence the need to synchronize on subscribedItems, i.e. the set of the active subscriptions).

Such a loop should be active anytime there is at least one active subscription; you may choose to start it at Data Adapter initialization and keep it always running.

Obviously, depending on the size of the table and the client activity, most of the loop operations may be redundant.
Also note that, as long as the contents of the table don't change for a key, the updates you send for that key are also redundant: the Server will recognize that nothing has changed and will just discard the update.