TIL: mapping multiple Statement() against one DataSource() is a bad idea

[tz8]

just FYI, there obviously is a difference between:

Code: Select all

var dbConn=new Datasource();
dbConn.connect("dsnname");

var orderSQL=new Statement(dbConn);
orderSQL.execute("SELECT id FROM orders");

while( orderSQL.isRowAvailable() )
{
	orderSQL.fetchRow();
	var orderID=orderSQL.getString(0);
	
	var shipmentSQL=new Statement(dbConn);
	shipmentSQL.execute("SELECT id FROM shipments WHERE orderid='"+orderID+"'");
	
	while( shipmentSQL.isRowAvailable() )
	{
		shipmentSQL.fetchRow();
		var shipmentID=shipmentSQL.getString(0);
		
		var lineItemSQL=new Statement(dbConn);
		lineItemSQL.execute("SELECT foo,bar,fup FROM lineitems WHERE shipmentID='"+shipmentID+"'");
		
		// do stuff with these values
	}
}
dbConn.disconnect();

and

Code: Select all

var orderConn=new Datasource();
orderConn.connect("dsnname");

var shipConn=new Datasource();
shipConn.connect("dsnname");

var itemConn=new Datasource();
itemConn.connect("dsnname");

var orderSQL=new Statement(orderConn);
orderSQL.execute("SELECT id FROM orders");

while( orderSQL.isRowAvailable() )
{
	orderSQL.fetchRow();
	var orderID=orderSQL.getString(0);
	
	var shipmentSQL=new Statement(shipConn);
	shipmentSQL.execute("SELECT id FROM shipments WHERE orderid='"+orderID+"'");
	
	while( shipmentSQL.isRowAvailable() )
	{
		shipmentSQL.fetchRow();
		var shipmentID=shipmentSQL.getString(0);
		
		var lineItemSQL=new Statement(itemConn);
		lineItemSQL.execute("SELECT foo,bar,fup FROM lineitems WHERE shipmentID='"+shipmentID+"'");
		
		// do stuff with these values
	}
}
orderConn.disconnect();
shipConn.disconnect();
itemConn.disconnect();

Notice that every Statement has its own DataSource. The behaviour is totally dependent of the ODBC driver in use. The first example worked fine on a MAC with the ActualODBC package against a MS SQL server, it failed to work on a Win10 machine with MS ODBC drivers: the second Statement.execute() always returned 0 rows.

Using multiple DataSource() fixed this, but also slowed the script down a bit... So if you hit that problem... there's a fix for that