The SQL Library Manager is a standalone desktop tool that enables users to maintain the queries that make up the SQL Library. Queries can be added, edited, removed, and exported/imported to an external file to use with other installations of fSeries.

Connecting to the fSeries Server

The SQL Library Manager is a client/server application. Therefore when you first open it, you will need to connect to a server running fSeries. Once connected, you can maintain the SQL Library held on that server.

Enter the full URL (including http:// or https:// prefix) of the server into the box provided, then hit the Connect button.

If you need to log into the server, you will be presented with an entry box to enter your fSeries user id and password.

Once you have successfully logged onto the server, the box will disappear and you will be connected to the server. Once you have connected to a server for the first time, it is remembered and its name will be stored on your machine. This list is cross-referenced by fData and the fDocs Designer add-in for Microsoft Word, so that if you use both programs, the list will be updated by each program in turn.

The next time you start fData, or hit the Connections button, you can use the dropdown box to select from a list of previously visited servers. After selecting one, its URL can be checked by hovering your mouse over the Connect button.

Clicking the Cancel button on this screen will close the DSD Library Manager.

Technical Note: The list of previously visited servers is stored at %userprofile%\fSeries\config.xml, and may be copied or deployed between users if required.

Managing the DSD Library

Once logged in the main DSD Library window will appear:

To begin, select a connection from the drop down list. This contains a list of all SQL database connections held on your fSeries server.

Once selected, the queries that make up the library will appear on the left of the window, and additional buttons are enabled at the top of the screen.

Printing the Library

The print button at the top of the screen will become enabled if you have assigned an fDocs template to use when printing the SQL Library.

To do this, log onto fAdmin (see separate documentation) and enter the location of the template into the “SQLLibraryDefintion” setting. Your template will need to be based on the SQL Library DSD, which will have been added to the server during the SQL Library installation.

Importing Queries

Clicking the import button allows you to select a SQL Library file (.XML) which will have been exported from the SQL Library Manager.

Once you have selected your file to import a dialogue will appear showing the queries available and allow you to view and select which queries you wish to import.

If a query you are importing contains the same identity (Id) as one that already exists you are given the option to change it, or to overwrite your existing query.

Any queries that you import are not saved until you hit the Save button at the bottom of the SQL Library window.

Exporting the SQL Library

Exporting queries allows you to create an XML file which can be used by other users to import the queries into their SQL Library. You can also use this to copy library queries from one fSeries server to another, by exporting the queries, logging into a different server, and then importing them.

To export queries from the current connection, click the Export button, select the queries you want to export and hit the OK button. A file save dialog will appear allowing you to save the exported file (in .XML format) to a location of your choice.

Manage an SQL Library Query

Selecting a query from the left hand list opens its properties on the right hand side of the window. You can change its description, notes, the SQL query itself, and placeholders to be used by the query when testing and creating a schema for use in fPanels (for panel definition) or fDocs (when building the tag list).

To test a query, use the Test Query button. This will open fTest and show the results of running the SQL query. If fTest has not been installed you will be given the option to open notepad to view an XML file containing the results of the test.

Managing Placeholders

Placeholders allow data to be passed around while a DSD is executing, for example when passing data from one data group to be used in a second data group (such as the Id from the results of a MSCRM view being used in a subsequent data group that returns data from a table in SQL).

Each placeholder must have a unique id within the query. This is how the placeholder is referred to, embedded in #…# (e.g. #ClientId#). It is case sensitive.

When building the tag list, or when testing a query, the format of the data that would populate a placeholder needs to be known so that a correct substitute value can be inserted. This is important so that if a field in a database is expected to be a number, a text value is not inserted in its place, which would cause an error in the SQL.

The list of placeholders will include an option to create a new placeholder or to add any placeholders that have been found in the query entered but have not yet been specified.

Enter the placeholder Id (which must exactly match the placeholder used in the query above) and optionally enter some notes to assist DSD managers when using the query in their DSD. Then, specify its format and if the type of placeholder is “Sample”, enter the sample text you wish to substitute in the query.

Converting SQL Data Groups

The convert button allows you to search through your current DSDs, finding any SQL data groups that match the current SQL query. If any are found, you may select the ones you wish to convert into an SQL Library type data group, with a reference to the current query.

This means that if you have many DSDs that all contain data groups with the same query, you can create a SQL library entry for the query and then convert all the data groups to use this new library query. Then in the future, whenever you change the query, all the data groups will immediately change to reflect your change, rather than having to open each DSD and data group in turn and make the change.

The conversion will be taken on the current unpublished version of the DSD. If there have been no other changes made to the DSD since it was last published, then you are able to immediately publish the DSD with the new data group in place. Otherwise, you will need to open the DSD in fData and publish it to apply this change, and any other changes you have made to it, for live use.

Deleting a Query

To delete a query from the SQL library, use the Delete button. Please note that any DSDs that contain SQL library type data groups that refer to this query will then return no data. This change is immediate.

 Saving a Query

To save any changes to a SQL library query, use the Save button. This will update the SQL library entry and any changes will take immediate effect across all DSDs that contain SQL library data groups that reference this query.

The query can then be used within fData by creating a SQL Library type data group and selecting the query and connection from the drop down list (see section 3).