09_WincanVX_SERVER_Database_EN

Owned by Beat Schneuwly (Unlicensed)
Last updated: Jun 04, 2024 by Daniel Steiner
10 min read
image-20240604-093719.png


Access to Server Databases from WinCan VX


Version 1.1
Date: 27.03.2018


1 Access to Server Databases from WinCan VX
1.1 Server with MSSQL Database
1.2 Server with ORACLE Database
2 SQL Server Setup
2.1 Location of Data
2.2 WinCan SQL Users
3 Installation
3.1 Enabling System Administrator
3.2 Download und Installation von WinCan VX
4 License Configuration
5 Establishing WCSYS on SQL-Server
6 Connecting to WCSYS on SQL-Server
7 Setting SQL-Server as default database
8 Creating new SQL-Server projects
9 Opening existing SQL-Server projects

Access to Server Databases from WinCan VX
The application WinCan VX originally has been developed for operation in a TV truck. Taking into account certain rules you also may run the software within a network domain accessing a central database. The management of large data amounts is thus simplified and may be of interest for water authorities, which are responsible of the maintenance of complex urban pipe line systems.
The present documentation addresses to network administrators and resellers of the WinCan software. It describes two sustainable concepts of embedding PC workstations WinCan VX has been installed on into a network domain. To make full use of the basic software functionalities a network license (red dongle) is therefore needed.

Server with MSSQL Database

This concept is based on a central MSSQL server database AND a file server for storage of the media (photos, video clips etc.). Both items can be established either on the same or on two different machines.

Server with ORACLE Database

This concept is based on a central ORACLE server database AND a file server for media storage (photos, video clips etc.). Both items can be established either on the same or on two different machines.

SQL Server Setup

The following steps describe a general procedure how to establish a central SQL database that can be accessed by the application WinCanVX:

  • The MS-SQL Server software (i.e. SQL-Server 2005 or higher or SQL Express) must be installed on a server by a professional server administrator. In case of any troubles please refer to the Microsoft SQL installation help document.

  • Define a file server which must be centrally accessible to the users when opening a WinCanVX project from SQL server. The file server can be a separate machine or may be located on the same machine as SQL server.

  • Create appropriate server instances:

[servername]
[instance] or .
[instance] ("." describes the current server machine)
An instance can imply several databases but always in relation to specific working domains. Creation of instances is preferable as it may help to maintain desirable organization by better segregating authentication, permissions and databases away from other applications:

  • SQL\Instance1 -> storage and management of pipeline infrastructure data

  • SQL\Instance2 -> storage and management of tax data

  • SQL\Instance3 -> storage and management of inhabitant statistics

etc.
Each project that gets added to WinCanVX will exist as its own database on the SQL-Server instance.

  • Create different users and define privilege levels for each user to create new or to access existing SQL-databases.

Location of Data

WinCan VX saves information to the project database, but saves media files to files. The database itself will contain the following important tables:
PROJECT: Project table (i.e. contractor, client…)
SECTION: Section table (i.e. location, street, pipe shape, diameter, material…)
SECINSP: Inspection table (i.e. operator, data, time, weather…)
SECOBS: Observation table (i.e. OP code, observation text…)
NODE: Node table (i.e. manhole data)
NODINSP: Node inspection table (i.e. manhole inspection data)
NODOBS: Node observation table (i.e. manhole observation data)
The file server is used to save the files linked to the WinCanVX database. The most important file types and folders in the WinCan project directory are listed below:

  • Subfolder Picture: picture files (*.jpg, *.gif, *.bmp) saved in Sec (sections) or Nod (manholes) subfolders as appropriate.

  • Subfolder Video: video files (*.mpeg, *.mp2, *.mp4, *.avi) saved in Sec (sections) or Nod (manholes) subfolders as appropriate.

  • Subfolder Module\Inclination: contains inclination files (*.inc).

  • Subfolder Module\ ScanExplorer: contains the folders and files created by the different pipe scanning systems.

WinCan SQL Users

WinCan VX requires SQL permissions for the following actions:

  • Connect to server

  • Create database: this is used to create each database that correlates with each WinCan projects that the user may wish to create.

  • Add new records: this is used to add records to many of the tables, such as to the SECTION table when a new section is added by the user

  • Delete records: used for some housekeeping within the WinCan project database. Typically items that are deleted from the user front-end interface are actually kept but simply updated within the database to be marked as deleted.

  • Update records: this used to update information on any number of items within the WinCan project database, such as simply editing an observation or a section.


These permissions may be granted as applicable only on databases created by the user if desired. There may be created a SQL-User or a SQL-Group of WinCan-Users to which all applicable users' Active Directory account are added.
For the purpose of this documentation, examples will assume a SQL-User named WinCan is created, and this information is saved to all installations of WinCanVX within the network environment. If other authentication methods are used this just needs to be specified where applicable rather than selecting SQL-Authentication.

Installation

Important:
WinCanVX should be installed from the built-in Administrator account on the computer. This is not the same as installation from a user account with administrator permissions or a domain admin account often used by IT-staff.
If installation from the local Administrator account is not possible WinCanVX must be installed as user with administrator permissions.
Basic program installation is performed on any Windows machine that will use WinCan, even if a license is not available for that machine. By default WinCanVX will open in Viewer Mode until a license is configured.
Viewer-Mode allows the user to open and view WinCan VX projects saved locally or on a shared network file-location.
Once a license is available, when WinCanVX is opened the user will be granted the ability to create and edit projects, plus any additional capabilities that have been licensed.

Enabling System Administrator

During the installation of Windows 7, Windows 8/8.1 or Windows 10 you are asked to create a first user account with administrator privileges. This account however does NOT correspond to the system administrator, which has to be enabled manually in order to have full control over various system components needed for a proper installation of WinCan VX. Proceed as follows to do so:

  1. Right-click on the desktop icon My Computer or This PC and select the context menu option Manage:

  1. In the following window, navigate to the Users category, right-click on the profile Administrator and select the Properties option to ensure that the built-in Administrator account is enabled:

Uncheck this option to ensure the Administrator account is enabled and click Apply.




  1. Be sure to also set a password for the built-in Administrator (i.e. Local or System Administrator) and confirm.

  2. Close all dialogs and reboot the PC.

From now on, it is possible to log on as a System Administrator at the PC startup. This is essential for installing and maintaining WinCan VX, its related modules as well as drivers for any device that must work together with the application.

Download und Installation von WinCan VX

Once the local Administrator account is enabled, log off the computer and log into the Administrator account.
Visit the website www.wincan.com and navigate to the Support tab, then select the link WinCanVX Downloads to download the installation file WinCan VX Setup:

When the download is finished run the file and follow the on-screen prompts, clicking Next several times and finally Finish – no options need to be changed during installation. WinCanVX is now installed and ready to be used in view-only mode. Configure a local or network license to extend functionality.
The program installation files of WinCanVX are located in the following two subdirectories:

  • C: \ Program Files (x86) \ CDLab

  • C: \ Users \ Public \ Public Documents \ CDLab



License Configuration

Network license dongles are used when multiple client computers will be using WinCanVX. With a network license there is no need to move dongles between computers – any computer than can contact the license management server can use WinCanVX in edit-mode.

  • Copy the latest version of the hardlock driver HASPUser_Setup.exe to the server hard drive and run the installation

  • Plug in the network license dongle:

54 6321Client within same subnet as Server

  1. Open the internet browser on the server and navigate to http://localhost:1947.

  2. Click on the category Update/Attach.

  3. Click on the button Browse.

  4. Browse to the location where the .V2C license file is saved and select it.

  5. Hit the button open within the Windows dialogue.

  6. Click on the button Apply File.

On CLIENT computers located within the same network subnet as the server no additional configuration is necessary. When WinCanVX is opened on the CLIENT it will automatically find the network license management server and use an available license.
If a CLIENT installation is located on a different network subnet than the Server, then some additional configuration is required on the CLIENT computer:
561432Type the host name or IP address of the license serverClient within different subnetas Server

  1. Go to the CLIENT computer, open the internet browser and navigate to http://localhost:1947.

  2. Click on the category Configuration.

  3. Hit the tab Access to Remote License Managers.

  4. Tick the ckeck box for Allow Access to Remote Licenses.

  5. Type the hostname or the IP address of the license server into the text box for Remote License Search Parameters.

  6. Click on the button Submit to connect to the license server.


Finally, check whether the Sentinel license service (Control Panel > Administrative Tools > Services) is activated on the current CLIENT computer:

Establishing WCSYS on SQL-Server

WCSYS is a database that is used to unify project functionality and listings. It is important for a SQL-Server implementation to have one WCSYS database that all users can access on the database server – this way everyone shares the same list of available projects.
In order to be able to work with SQL-Projects in WinCan first install WinCanVX on one client computer, run the application and proceed as follows:

  • Hit the icon Project > Manage projects to run the project manager. Switch into the Expert Mode (1) and push the button Oracle/SQL Server Setup (2):

21

  • Create a new WCSYS database on the SQL-Server.



  • Click on the folder icon to browse for the type of database that will be pushed to SQL-Server:

4321

  • Click on the button Next (4) to enter the SQL authentication credentials applicable to your environment (5), test the connection (6) and finish the database distribution:

655

  • Install WinCanVX on the other client computers (one network license package is valid for 10 clients)



Connecting to WCSYS on SQL-Server

Once WCSYS has been created on / copied to the SQL-Server WinCanVX must always be directed to login to THAT copy of WCSYS; click on the tab Home, select the command Login and activate the Expert mode:
Make sure this option is activated to use this connection per default for future LOGINS1
Select the list item (new connection…) (1) and fill out the SQL authentication information applicable to your environment (2). Be sure you select the appropriate authentication type (3) and enter WSYS for the Database name (4). Finally test the connection before saving (5):
542223
Once the new WCSYS has been activated from the client computer, the list of available WinCanVX projects will refresh to reflect those available in the SQL-Server location of WCSYS.

Setting SQL-Server as default database

WinCanVX is able to use default settings for project creation. Many of these settings are useful for a SQL implemention. Proceed as follows to access these settings.

  • Click on the tab Home, hit the command Settings and switch to the category Presets (1). Select the list item MS SQL Server in the database presets (2) in order SQL will become the default project type:

4321

  • Enter the UNC path for the WinCan project directory on the file server (3); so the media files always get saved to and loaded from the correct shared location.

  • Mind the default settings for the damage catalogue (Standard) and template, which can be entered within this panel too (4). So they don't need to be re-selected each time a new project is going to be created.

Creating new SQL-Server projects

SQL-Server projects are created in the same way as normal projects except one step.

  • Hit the tab Projects and click on the icon New Project.

  • Enter a project name as normal but be sure to change the database type to Microsoft SQL Server 2005 or higher:


Click on the button Next to continue

  • Check the project path that must correspond to your environment.

  • Change the authentication to SQL server (1) and enter the server name as well as the login credentials applicable to your environment:

3221

  • If the connection test (3) is successful click Next to finish the project creation procedure. A new WinCanVX SQL-project is loaded now which looks like a normal project:

This directory contains the video clips that have got a link in the databaseThis directory is empty as the project is accessing the SQL-database directly.This directory contains the pictures that have got a link in the database*Test_US_2015_11_09*

Opening existing SQL-Server projects

Existing SQL-Server projects always must be added from the project manager –they cannot be accessed from the command Projects>Open project.

  • Hit the tab Projects and click on the icon Manage projects. Push the button Add existing project from the Project manager and change the Database type to Microsoft SQL Server 2005 or higher:

  • Enter the SQL authentication information applicable to your network environment.

  • Select an existing WinCanVX SQL project, click on Finish and add it.