PostgreSQL (Postgres) Setup

This guide covers moving your Student Manager data to the PostgreSQL (Postgres) version, including setting up the ODBC driver and DSN, preparing the database with pgAdmin 4, and porting your Visual FoxPro (VFP) tables to Postgres.

Requirements for Moving to PostgreSQL Server

• Upgrade Student Manager and ACEweb to the latest versions before you can convert data to PostgreSQL.
  Latest version of PostgreSQL installed on the target server (enterprisedb.com ↗️).
• Latest Postgres ODBC ↗️ driver installed on any machine that connects to the database (including the server where Student Manager and ACEweb reside).
• Latest version of pgAdmin 4 installed on the target server.

PostgreSQL Installer

The Postgres installer offers to install it, so you may already have it. However, the bundled version may not be the latest, so you may prefer to install it separately from pgadmin.org ↗️.

• Obtain the conversion files from your ACEware technician and place them in your Student Manager folder.

Setting up DSN

The DSN must be set up on the server and all workstations that will access Student Manager.

1. On 32-bit machines, find Data Sources (ODBC) in Administrative Tools (usually in Control Panel). On 64-bit machines, open C:\Windows\SysWow64\odbcad32.exe.
2. Go to the System DSN tab.
3. Click Add.
4. Select PostgreSQL ODBC Driver (UNICODE).
5. Click Finish.
6. For Data Source, give it a meaningful name (e.g. acewaresm).
7. For Database, give it a meaningful name (e.g. acewaresm).
8. For Server, enter the name or IP address of the server. If installing locally, localhost should be fine.
9. For User Name and Password, use whatever was set during the installation of Postgres. The default is usually postgres / postgres.
10. Description is optional.
11. Set SSL Mode based on whether this is a secure environment. Hosted environments should be set to allow; a local environment is usually set to disable (since you don't have a certificate installed).

12. For the Port, the default is 5432, but use whatever was set during installation.
13. Click Datasource.
14. Uncheck Bools as Char.

15. Click OK to close the Datasource screen.
16. Click Test to verify that everything communicates properly. At this point you should get an error that your database name doesn't exist (since it hasn't been created yet) — but there shouldn't be any other issues.
17. Click Save to close the Setup screen.
18. Click OK to close the ODBC screen.

Database Preparation with pgAdmin 4

1. Launch pgAdmin 4.
2. It should launch the Register - Server dialog automatically. If not, right-click Servers and click Register, or hit Add New Server from the Quick Links.
3. Give it a Name that makes sense to you (e.g. acewaresm).
4. On the Connection tab, enter the same Host name/address you used for the Server value in the ODBC Setup.
5. Fill in the Port.
6. Leave Maintenance database alone.
7. Enter the Username and Password, and turn on the Save password slider.
8. On the Parameters tab, set your SSL Mode as you did in the ODBC Setup.
9. Click Save.
10. pgAdmin should now connect to the maintenance database. Click the arrow next to the Name you gave in step 3 to expand it.
11. Right-click Databases and go to Create > Database.

12. Give it the same name you used for the Database in the ODBC Setup.
13. With the new database name highlighted, go to Tools > Query Tool in the menu (or right-click and select Query Tool). At this point you can retest the ODBC driver, as the database it connects to now exists.
14. Click the Open File icon: .
15. Find the student_manager_db_init.sql file wherever you saved it and open it.
16. Click the Execute icon: .
17. You should see a Query returned successfully message when it is done.
18. Click the Open File icon again.
19. Find the westwind.sql file wherever you saved it and open it.
20. Click the Execute icon.
21. You should again see the Query returned successfully message.
22. To verify that the tables were created, expand Schemas > Tables. You may need to right-click and click Refresh.

Porting VFP Tables to Postgres

1. Get everyone out of Student Manager.
2. Run vfp2pg.exe.
3. Type in the name of the database you created for the Data Source in the ODBC Setup.

4. Click OK.
5. Let it run. It will cycle through table names and other relevant messages in the upper-right corner.
6. While it is running, create a file named pgconnect.ini containing the same Data Source name, and save it to the Manager folder.
7. In aceweb.ini, put the Data Source name in the Sqlconnectstring in the [Main] section. In the [AceCode] section, put the Data Source name in the Pgconnectstring entry (create it if it doesn't already exist).
8. When you get the Finished message in the vfp2pg.exe window, verify that the tables were created in pgAdmin.

Installing ACEweb PostgreSQL Version on a New Server

If you are installing on a new server, obtain the SQL install file from your ACEware technician and install ACEweb.

Upgrading Current ACEweb Install to PostgreSQL Version

If you are upgrading a current ACEweb install, upgrade, obtain the SQL version update file and follow the steps to upgrade to it before continuing these steps.

Registering the aw4.exe

After applying the SQL version upgrade, you must register the new aw4.exe. Open a command prompt window, navigate to the inetpub\aceweb folder, then register aw4.exe, e.g.:

DCOM Configuration

You may need to edit the properties of the new aw4.acewebserver. See the DCOM Settings section of the ACEweb Mode Setup topic for more information.

Web.config and ACEweb.ini Changes

1. Edit the web.config in the inetpub\wwwroot\wconnect folder and make the following changes:

   • Change the ComServerProgId setting to aw4.AceWebServer.
   • Change the ExeFile setting to point to aw4.exe.
   • Change the UpdateFile setting to point to aw4.exe.

   

2. Make the following changes in the ACEweb.ini (in inetpub\aceweb):

   • Enter the ODBC Data Source Name in the PGconnectstring in the [Main] section. This is the name entered in step 8 of the Setting up DSN section. For example: `PGconnectstring=acewaresm'.
   • Set the DebugFlag setting to TABLE: (if it's not already set).
   • If you have not already enabled admin error emails: turn on the AdminSendErrorEMail INI setting, enter the email server in AdminMailServer, and an email in AdminEmail.
   • Update Datapath, and PccPath if necessary, to the Student Manager directory. This is only necessary if you have moved your Student manager folder.

Set Application Pool Identity

You must set the Application Pool to a user that has rights to access the PostgreSQL database.

WARNING

You cannot run the PostgreSQL version under the SYSTEM account. It must run under a specific domain account. See the permissions topic for more information.

Load the COM Server

1. Go to the admin.aspx page and click the Web Connection MOdule Administration link.
2. Click the Unload Com Servers link, then the Load Servers link.

You should now see the aw4.acewebserver COM Server(s) running.

ACEweb Template Changes

Some template changes may need to be applied to run the SQL version. Run the FileManager.awp tool to see what templates may need to be updated.

Maxlength on Fields

PostgreSQL won't truncate values. If a user tries to enter a value longer than the field length (e.g. a city longer than 20 characters), they will get this error on submit:

[SQL Server]String or binary data would be truncated. [1526:8152]

To avoid the error, fields on all templates that submit data to the SQL tables must have a maxlength set to the maximum length of the field. This applies to the following main templates: Attachments.awp, Attendtrack.awp, ChangePwd.htm, CourseProposal.awp, Credentials.awp, CustomRegister.htm, Gradebook.awp, Person.awp, Proxyreg.awp, Register.htm, Xperson.awp.

You can get updated templates (with maxlength values set) via the File Manager tool.

Re-apply Customization

If you need updated templates, you will need to re-apply any customization to the new template.

This also applies to any custom express registration and data capture templates in use, and to any nameudfs fields used on person.awp and proxreg.awp. If you are adding fields to those templates, ensure they have the correct maxlength value set.

Custom Routines

• Any custom routines or templates you have ordered will need to be reviewed and updated for PostgreSQL.
• Any Express Registration templates you are using must be reviewed for PostgreSQL compatibility.

Please contact your ACEware technician for help with the review.

Compile Framework Pages

Once you have all template updates in place, you must recompile your .awp type templates. Run the AWP Compiler and click the All Templates button to recompile all .awp templates.

Alternate Interfaces Using Different PostgreSQL Database

If your alternate interface uses a different database than the main interface, you must set the AlternateSQL to ON in the alternate interface's ini setting file (e.g. awp1.ini).