NotePage, Inc. Forum Index

Adding recipients programmatically from outside Pagegate

 
Post new topic   Reply to topic    NotePage, Inc. Forum Index -> PageGate Support
View previous topic :: View next topic  
Author Message
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Tue Mar 03, 2009 5:00 pm    Post subject: Adding recipients programmatically from outside Pagegate Reply with quote

We're using v4 of Pagegate server.

We have a web app that allows customers to update their contact info in our (non-pagegate) database. Home phone, cell, etc, but also including their pager number.

Occasionally a customer will update their pager number, but naturally this doesnt change pagegate for them, so unless they also call us and get one of our staff to update that, their change doesnt go into effect.

We are considering several solutions:one is simply alerting our staff with an email when a customer makes an update to a pager contact, but another is to allow our web app users to update pagegate's database directly.

I've written a few apps that query Pagegate's MDB directly, returning all users with emailto addresses that partially or completely match a user input string, for instance. Selecting data has not been an issue at all, those apps work fine.

I'm just worried that inserting into/updating (rather than just selecting from) the live database might cause some concurrency issues. I'm talking about inserting a single recipient mostly, and possibly editing an existing one also.

Has anyone else has made their own app that updates pagegate directly?


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4244

PostPosted: Wed Mar 04, 2009 8:46 am    Post subject: Reply with quote

Most times, I've seen clients do this with the SQL Database integration. It shouldn't cause any trouble, just make sure that your app doesn't try to open the database exclusively when it writes.



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Wed Mar 04, 2009 10:15 am    Post subject: Reply with quote

SQL integration, I like the sound of that.

Is that new in v5, and can you point me to more info on it?


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4244

PostPosted: Wed Mar 04, 2009 10:23 am    Post subject: Reply with quote

It is something we've recently implemented. Here are the steps on how to do it, though bear in mind you may not need to all of the steps if you already have your database created, already have your recipients configured, etc:

1. Stop Existing PageGate
a. If you have an existing copy of PageGate, be sure to stop ALL of the PageGate programs from running (i.e. PG Server, PG Admin, PG Monitor, PG GUI Clients, etc.)

2. Backup
a. If you have existing data in your PageGate program, be sure to make a backp of your PageGate database. The database file is named 'pagegate.mdb' and is found in the PageGate program folder by default.

3. Install PageGate v5, if you don't already have it installed.

4. Upgrade / Create Database
a. Run PG Admin. If you have an existing database you will be prompted to upgrade it. Otherwise a new blank database will be created.
b. If PG Admin is still running, close out of it.
c. Do not run the PageGate server at this time.

5. Import Structure and Data to SQL server
a. Run the SQL Server's 'Import and Export Data' wizard.
b. <Next>
c. For 'Data Source' choose 'Microsoft Access'
d. Browse to the PageGate database file for the 'File Name' field
1. If you upgraded from PG v4, the default database location is c:\program files\pagegate\pagegate.mdb
2. If you installed PG v5 fresh, the default database location is c:\program files\pagegate\database\pagegate.mdb
e. <Next>
f. For 'Destination' choose 'Microsoft OLE DB Provider for SQL Server'
g. Choose the correct settings for Server and Authentication
h. Choose '<new>' for 'Database'
i. Name the new database 'pagegate'
j. <Next>
k. Select the 'Copy table(s) and view(s) from the source database' option
l. <Next>
m. Click the 'Select All' button to select all of the PageGate tables
n. <Next>
o. Check only the 'Run immediately' option
p. <Next>
q. <Finish>
r. It should say that 13 tables were successfully imported
s. <OK>
t. <Done>

6. Modify Database Structure
a. The Import Wizard doesn't make all the right choices for the database structure so some changes need to be made.
b. Run the SQL Server's 'Enterprise Manager' program
c. Navigate down to the newly created 'pagegate' database tables
d. There will be some 'extra' tables there that were created by the SQL Server. We will only be concerned with the 13 tables we imported: ahm, asciitable, carriers, control, dialers, groups, lookup, mailboxes, messages, oncalltable, security, settings, users
e. Fix the date fields in these tables: carriers(1), control(1), messages(4), oncalltable(2), users(2)
1. Right-click on the table and choose 'Design table'
2. Select each field that has 'smalldatetime' for a data type
3. Change the data type field to 'datetime'
4. Repeat for the remaining PageGate tables
f. Add fields in all the PageGate tables, except lookup and messages:
1. Right-click on the table and choose 'Design Table'
2. Create a record id field
a. Select the next blank row and enter a new field name called 'record_id'
b. Choose 'int' for it's type
c. In the bottom part of the window change 'Identity' to 'Yes'
3. Create a timestamp field
a. Select the next blank row and enter a new field name called 'tstamp'
b. Choose 'timestamp' for it's type
5. Close the Design Table window and accept the changes
6. Repeat for all the PageGate tables except lookup and messages
g. For the lookup table
1. Right-click on the table and choose 'Design table'
2. Select the 'id' field
3. In the bottom part of the window change 'Identity' to 'Yes'
5. Close the Design Table window and accept the changes
h. For the message table
1. Right-click on the table and choose 'Design table'
2. Select the 'message_id' field
3. In the bottom part of the window change 'Identity' to 'Yes'
5. Close the Design Table window and accept the changes
i. Add primary indexes to all the identy fields
1. Riht-click on the first table and choose 'All Tasks' and then 'Manage Indexes'
2. For each PageGate table:
a. <New>
b. For the 'Index name' enter: pindex
c. Select the 'record_id' field (id in the lookup table, and message_id in the messsages table)
d. Check the 'Unique values' box
e. <OK>
f. Choose the next PageGate table from the 'Table/view' list and repeat starting with step 'a' above
3. <Close>
j. Close the SQL Server Enterprise Manager

7. Create linked tables in MS Access
a. Run MS Access 2000 and open the PageGate database
1. If you upgraded from PG v4, default location is c:\program files\pagegate\pagegate.mdb
2. For a fresh install of PG v5, default location is c:\program files\pagegate\database\pagegate.mdb
b. From the menu choose 'File', 'Get External Data', 'Link Tables'
c. For 'Files of type' choose 'ODBC Databases'
d. Select the 'Machine Datasource' tab
e. <New>
f. Select 'System Data Source'
g. <Next>
h. Select 'SQL Server'
i. <Next>
j. <Finish>
k. For 'Name' enter pgdatabase
l. For 'Description' enter PageGate Linked Database
m. Select your SQL server from the 'Server' list.
n. <Next>
o. Enter the appropriate authentication settings for your SQL server
p. <Next>
q. Select 'Change the default database to' checkbox
r. Select 'pagegate' from the list
s. <Next>
t. <Finish>
u. <Ok>
v. <Ok>
w. Select all 13 of the pagegate tables from the list: ahm, asciitable, carriers, control, dialers, groups, lookup, mailboxes, messages, oncalltable, security, settings, users
x. <Ok>
y. You should now see the new linked tables in the MS Access database window. They are all prefixes with 'dbo_'

8. Make the new linked tables live
a. Delete or rename all the existing PageGate tables (the ones that don't start with 'dbo_'
b. Rename the linked tables to the 'real' PageGate names by removing the 'dbo_' prefix from each of them
c. Relink the tables to make them live
1. From the menu choose 'Tools', 'Database Utilities', 'Linked Table Manager'
2. <Select All>
3. <Ok>
4. It should say that all the linked tables were refreshed
5. <Ok>

9. Try it out
a. Close MS Access
b. Start PG Monitor
c. Start PG Admin
d. Start the PageGate server



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Wed Mar 04, 2009 10:38 am    Post subject: Reply with quote

If I'm understanding that process, Pagegate is still using MSAccess as far as it knows, but the MBD doesnt have any data in it, rather it's all linked tables, and your data is actually in SQL.

Is there any reason that same process wouldnt work in v4? Not that we're against upgrading to 5 (it's on our todo list) but this is just a more immediate issue Ive been tasked with resolving asap.


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4244

PostPosted: Wed Mar 04, 2009 10:48 am    Post subject: Reply with quote

Right, that's what the process is. Your data is actually in SQL and PageGate still uses its own database, as far as it knows, it's the linked tables that actually supply the data.

The SQL Linked Tables is something I've only ever tested and worked with in version 5. It MAY be possible to do it in version 4 but you may run into unforseen issues with it because I just haven't tested it with the older version.



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Wed Mar 04, 2009 10:53 am    Post subject: Reply with quote

I'll do some messing around on a test machine, and I'll let you all know if i get it to work in 4.


Back to top
View user's profile
NotePage Sales
Site Admin


Joined: 23 Apr 2004
Posts: 265

PostPosted: Wed Mar 04, 2009 11:04 am    Post subject: Reply with quote

Information about upgrading PageGate v4 to v5 for $100.00 is at http://www.notepage.net/pagegate-v5.htm

Optional are a CD and Priority Support: http://www.notepage.net/priority-support.htm



_________________
NotePage Sales
Back to top
View user's profile Visit poster's website
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Fri Mar 06, 2009 1:31 pm    Post subject: Reply with quote

Well i got it partially working on V4; i could open PGadmin and view data, and even delete recipients etc.

I did get some errors when trying to add/update. They were somewhat vague (3146 ODBC call failed in add_user command1).

My data connection was fine and permissions wern't an issue (i am dbo), or i wouldn't be able to delete. My guess was that maybe a datatype was wrong somewhere.

I downloaded the trial for v5 and started fresh. I followed the same process, though i had to go back and add the audit table, but then everything looked good again. Got the server running, and was able to view data via PGAdmin.

Again i got that error 3146 when i tried to add a new user, but it was more specific this time and told me "Cannot insert value NULL into column 'tty_service'". So I made tty_service nullable, and the insert succeeded.

My guess is it was probably the same issue in v4, and changing the same field to nullable would make it work, but i blew away v4 on my test machine completely and I'm too lazy to go back to test that. =)


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4244

PostPosted: Fri Mar 06, 2009 1:45 pm    Post subject: Reply with quote

Actually, null values are bad stuff for the PageGate database. Instead of NULL values, set it to a blank string values.



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Fri Mar 06, 2009 1:53 pm    Post subject: Reply with quote

tty_service is a bit (true/false) field, so an empty string wouldn't be good either, but that's moot because i don't see how i can change what PGAdmin is sending.

I added a user via PGAdmin, and it's passing a null for tty_service in the insert. Not sure how i can change that behavior (not even really sure what tty_service is for; i dont have any field for editing it on the user i'm adding).



EDIT: ok came up with a better solution: I set that field's Default Value to 0, and made it non-nullable. Just tested and it works.


Back to top
View user's profile
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Wed Mar 11, 2009 3:34 pm    Post subject: SQL integration Reply with quote

I've been playing around with the SQL integration with v5 on and off for a few days, and it was working fine, but i broke something somehow.

I cant get it to start at all now while using the SQL linked mdb, but as soon as i swap in the plain old mdb with data in it it starts working fine again.

Symptom would seem to indicate a prob with SQL tables config i guess, but i didnt change anything there since i had it working, that i know of.

Well, I blew away the tables in SQL, and started the process again from the beginning. I imported from access and made the column modifications by hand as before. In access I linked to the sql tables via odbc and tested ability to read/write from there successfully.

But i'm still getting a hang at start. In the PGMonitor for PGServer i get just:
<datetime> Module Started
but it never actually starts.

I think the last thing i changed before it broke was to make PG run as a service instead of an app, but changing it back doesnt seem to help now.

Any advice?


Back to top
View user's profile
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Wed Mar 11, 2009 3:59 pm    Post subject: Reply with quote

Update: it DOES work using the SQL data when i switch it back to application made instead of service mode, so it seems to only have a problem starting when its a service.

When i configured it to run as a service, i just clicked "Apply" without specifying an account (to use local system account, as it always did before). But now it's doing more than talking to a local MDB...

I guessed permissions issue; "local system" can't access the sql data because i configured the DSN to use windows authentication. I created a sql authentication user on the sql server and gave it dbowner over the pagegate db, then changed the DSN to use that sql login instead of windows auth.

This still didnt fix my problem (even refreshed the linked tables in access).

Next i tried to tell it to use a different account (my own) via PGadmin (switched to app, then back to service to get account prompt).

Had a little trouble at first, (i had to grant my domain login the local rights to run as a service) but then i was able to get the main pagegate service to use my account instead of local system. Now it's working again in service mode.

EDIT: i am still not sure this is the best fix... i now have to go through and changing the account for each dialer/interface service also as they are hung...

EDIT2: better fix; went back to the DSN idea and re-linked my tables in access using the modified DSN, and now it can run regardless of service account, using a sql login instead of nt auth (IOW linked-table refresh doesnt cut it; if you changed the DSN, re-create the linked tables).


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4244

PostPosted: Thu Mar 12, 2009 1:01 pm    Post subject: Reply with quote

Brian,

Thanks for keeping us posted on this. This is really good information.

Tech Support



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
Brian.Turner



Joined: 29 Jun 2004
Posts: 26
Location: Atlanta

PostPosted: Thu Mar 12, 2009 3:30 pm    Post subject: Short version Reply with quote

For anyone else doing this, here's the short version of what i learned:

After creating and modifying your tables in SQL, the 'tty_service' field in users table needs Default Value of 0.

If you're going to run pagegate as a service, use Sql Server Authentication (instead of Windows NT Authentication) when setting up your odbc data source. Either can work for application mode.

When selecting which tables to link in access, if you're using sql authentication in your data source, don't forget to check the 'remember password' box in the lower right corner.


Back to top
View user's profile
Display posts from previous:   
Post new topic   Reply to topic    NotePage, Inc. Forum Index -> PageGate Support All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group
Theme created by Vjacheslav Trushkin