SQLContainer and MSSQL

Hello!

I love the SQLContainer and have used it successfully in past projects with MySQL. I’d like to use it for my current project however I need to use sql server this time. I’m having a hard time getting the container and MSSQL to work together. I’m doing every thing the same as before but with a different connection string and a few more tables. Would it be possible for someone to post an example of how to use sql server and the container together?

Thanks in advance!

DeAnna

Incase anyone else needs an example I finally figured out my code issues. Here’s my container testing code with two tables with FreeformQueryDelegates. I hope this can help someone!


Main Application

private void initConnection(){		
		try{
			connectionPool = new SimpleJDBCConnectionPool(DRIVER, URL, USERNAME, PASSWORD,2,5);
		} catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	private void initDatabase(){
		try{
			con = connectionPool.reserveConnection();
			Statement statement = con.createStatement();			
			try{
				statement.executeQuery("SELECT * FROM Event");
				statement.executeQuery("SELECT * FROM Event_Dates");
			} catch (SQLException e){
				e.printStackTrace();
			}			
			statement.close();
			con.commit();
			connectionPool.releaseConnection(con);
		} catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	private void initContainer(){
		try{
			FreeformQuery query = new FreeformQuery("SELECT * FROM Event", Arrays.asList("Event_ID"), connectionPool);
			query.setDelegate(new PersonFreeformQueryDelegate());
			eventContainer = new SQLContainer(query);			
			FreeformQuery datesQuery = new FreeformQuery("SELECT * FROM Event_Dates", Arrays.asList("Event_Dates_ID"), connectionPool);
			datesQuery.setDelegate(new EventDatesFreeformQueryDelegate());
			evtDatesContainer = new SQLContainer(datesQuery);
		} catch(Exception e){
			e.printStackTrace();
		}
	}


Event FreeformQueryDelegate

The important Parts:

PreparedStatement statement = null;
        if (row.getId() instanceof TemporaryRowId) {
            statement = conn
                    .prepareStatement("INSERT INTO Event (Name, Location, Disabled, Admin_Email, Confirmation_Message) VALUES(?, ?, ?, ?, ?)");            
            statement.setString(1, (String) row.getItemProperty("Name")
                    .getValue());            
            statement.setString(2, (String) row.getItemProperty("Location")
                    .getValue());
            statement.setInt(3, (Integer) row.getItemProperty("Disabled")
                    .getValue());
            statement.setString(4, (String) row.getItemProperty("Admin_Email")
            		.getValue());
            statement.setString(5, (String) row.getItemProperty("Confirmation_Message")
            		.getValue());
        } else {
            statement = conn
                    .prepareStatement("UPDATE Event SET Name = ?, Location = ?, Disabled = ?, Admin_Email = ?, Confirmation_Message = ? WHERE Event_ID = ?");            
            statement.setString(1, (String) row.getItemProperty("Name")
                    .getValue());            
            statement.setString(2, (String) row.getItemProperty("Location")
                    .getValue());            
            statement.setInt(3, (Integer) row.getItemProperty("Disabled")
                    .getValue());            
            statement.setString(4, (String) row.getItemProperty("Admin_Email")
            		.getValue());            
            statement.setString(5, (String) row.getItemProperty("Confirmation_Message")
            		.getValue());
            statement.setInt(6, (Integer) row.getItemProperty("Event_ID").getValue());
        }

        int retval = statement.executeUpdate();
        statement.close();
        return retval;
	}


Event Dates FreeformQueryDelegate

The important parts again

PreparedStatement statement = null;
        if (row.getId() instanceof TemporaryRowId) {
            statement = conn
                    .prepareStatement("INSERT INTO Event_Dates (Date, Lunches, Event_ID) VALUES(?, ?, ?)");            
            statement.setTimestamp(1, (Timestamp) row.getItemProperty("Date")
                    .getValue());            
            statement.setString(2, (String) row.getItemProperty("Lunches")
                    .getValue());
            statement.setInt(3, (Integer) row.getItemProperty("Event_ID")
                    .getValue());
        } else {
            statement = conn
                    .prepareStatement("UPDATE Event_Dates SET Date = ?, Lunches = ?, Event_ID = ? WHERE Event_Dates_ID = ?");            
            statement.setTimestamp(1, (Timestamp) row.getItemProperty("Date")
                    .getValue());            
            statement.setString(2, (String) row.getItemProperty("Lunches")
                    .getValue());        
            statement.setInt(3, (Integer) row.getItemProperty("Event_ID")
                    .getValue());            
            statement.setInt(4, (Integer) row.getItemProperty("Event_Dates_ID")
            		.getValue());
        }

        int retval = statement.executeUpdate();
        statement.close();
        return retval;

Thanks for sharing your code.
But did you try with TableQuery instead of FreeFromrQuery.

Because I have issues with TableQuery just with SQL server and not With Oracle and MySQL.