This article describes the steps necessary for connecting to a remote MSSQL database server, from a Wt application that has been compiled and is running on a linux box.

Note: This procedure is brand-new (to me) and so these steps are not fully developed and documented yet. Use with caution.

The steps encompass the following;
1. Install ODBC driver on Linux
2. Create a Session Connection string
3. Create a database Table dbo class
4. Create a Table View of the data

Install the ODBC driver

There are a few steps involved in installing the ODBC driver. There is an ODBC driver from microsoft, and then there is an additional tool called 'FreeTDS' which is an additional wrapper to the microsoft driver. I am unclear (at this time) as to the relationship between the FreeTDS and the MS-ODBC.

Follow the procedure here to install the ODBC driver
1. https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

Create a Session Connection string

The session connection string can be employed a couple of different ways; by DSN values or by DIRECT values. Either can work but I think it might be a little more convenient for start-up systems to simply employ the DIRECT connection string. I'll show both here;

Connection String Methods: 250 #ifdef WTX_USING_MSSQL
251 #ifdef USING_DSN_STRING
252 auto dbName =
253 Wt::WString( "DSN={1};UID={2};PWD={3}" )
254 .arg( "dbname" )
255 .arg( "username" )
256 .arg( "password" )
257 .toUTF8()
258 ;
259 #else
260 auto dbName =
261 Wt::WString( "Driver={1};Server={2};UID={3};PWD={4};Database={5};" )
262 .arg( "{ODBC Driver 17 for SQL Server}" ) // set to your proper ODBC installation version
263 .arg( "servername_or_ip" )
264 .arg( "username" )
265 .arg( "password" )
266 .arg( "dbname" )
267 .toUTF8()
268 ;
269 #endif
270
271 auto connection =
272 std::make_unique<Wt::Dbo::backend::MSSQLServer>( dbName );
273 #endif
274

This will open a session to the database, regardless of where the database is located, either on the localhost or on a remote-ip address, it doesn't matter. As long as you have the credentials correct, and you can get to the server you're good to go!

Create a database Table dbo class

In order to "talk" to the database and bring data in to the application so as to be able to represent it in a browser, a class-object must be create to contain the database record information. This is quite easy to do, simply by defining a class with the necessary storage fields, and then providing in that class a hook for the dbo connections.

DBO Class Definition: 149 //
150 // predefine the 'Entity' class
151 //
152 namespace Coy {
153 class Entity;
154 }
155
156 //
157 // Wt provides for optimistic record-locking through the use
158 // of a 'version' field on each record. This code causes that
159 // feature to be disabled, since we cannot modify the schema
160 // of this foreign database.
161 //
162 //
163 namespace Wt {
164 namespace Dbo {
165 template<>
166 struct dbo_traits<Coy::Entity> : public dbo_default_traits
167 {
168 static const char * versionField()
169 {
170 return nullptr;
171 }
172 };
173 } // endnamespace Dbo
174 } // endnamespace Wt
175
176 //
177 // Create an 'Entity' class for holding the database record
178 // information
179 //
180 namespace Coy {
181 class Entity
182 : public Wt::Dbo::Dbo<Entity>
183 {
184 public:
185
186 Entity();
187
188 template<class Action> void persist( Action &a )
189 {
190
191 Wt::Dbo::field( a, code, "code" );
192 Wt::Dbo::field( a, description, "description" );
193 Wt::Dbo::field( a, typeID, "typeID" );
194
195 }
196
197 std::string code;
198 std::string description;
199 int typeID;
200
201 }; // endclass Entity
202 } // endnamespace Coy {
203

With this class, it is now possible for us to 'inform' our database session about the class, so that the session knows where to put the data that it's going to grab from the database.

Map the DBO class to the DBO Session: ...continued from the Session Connection string above
283 auto connection =
284 std::make_unique<Wt::Dbo::backend::MSSQLServer>( dbName );
285 #endif
286
287 #ifdef SHOW_QUERIES
288 connection-> setProperty( "show-queries", "true" );
289 #endif
290
291 setConnection( std::move( connection ) );
292
293 //
294 // map the 'Entity' class, and let the session know what the
295 // actual table-name is
296 //
297 mapClass<Coy::Entity>("Entity");

Create a Table View of the data

We now have a session connection and we have a Class object mapped to that session, now we can create a view to show the data.

Create a Browser table-view of the data: 348 {
349 // When talking to the database we need a transaction open
350 Wt::Dbo::Transaction t(*session());
351
352 // Ask the session to return a collection of 'entities'. It's
353 // just a vector, really, of a bunch of pointers to the entities.
354 // It's pretty efficient and pretty fast.
355 auto entities = session()->
356 find<Coy::Entity>()
357 .where( "typeid = 5" )
358 .orderBy( "code desc" )
359 ;
360
361 // using the query from above, create a 'Model' of the data
362 auto queryModel = std::make_shared< Wt::Dbo::QueryModel< Wt::Dbo::ptr<Coy::Entity> > >();
363
364 queryModel-> setQuery( entities );
365 queryModel-> addColumn("id");
366 queryModel-> addColumn("code");
367 queryModel-> addColumn("description");
368 queryModel-> setColumnFlags( 2, Wt::ItemFlag::Editable );
369
370 // Create a TableView and assign the model to it
371 auto u_tableView = std::make_unique<Wt::WTableView>();
372 auto tableView = u_tableView.get();
373 lw-> addWidget( std::move(u_tableView) );
374 tableView-> setColumnWidth( 2, 600 );
375 tableView-> setAlternatingRowColors(true);
376 tableView-> setSelectionBehavior( Wt::SelectionBehavior::Items );
377 tableView-> setSelectionMode( Wt::SelectionMode::Single );
378 tableView-> setEditTriggers( Wt::EditTrigger::DoubleClicked );
379 tableView-> setEditOptions( Wt::EditOption::SingleEditor );
380
381 tableView-> setModel( queryModel );
382 }

This should produce somthing like this;

Conclusion

As you can see, it's really quite easy to establish a connect to a remote database even running on a foreign operating system, and build interfaces and classes to access and manipulate that data.

Wt is pretty powerful stuff. You will be well advised to wear protection at all times when working with it!

References