Avonelle's Tech Blog

Go Back

Formatting for Pivotal Timestamp fields

This is a quick reminder for how to handle Pivotal timestamp fields when updating data via an active form. (These are not timestamp fields in SQL, but are identified as timestamp fields within Pivotal itself.)

In order to set a value properly, the date/time information must be formatted using this syntax:

Format(Data, “yyyy-MM-ddTHH:mm:ss.000”)

Posted by Avonelle on Wednesday, May 12, 2010. There are 0 Comments.

Saving a “Choice” value from a .NET app via an Active Form in Pivotal

Today’s Pivotal lesson relates to saving data via an Active Form from a .NET application. I’m updating an existing app, so I didn’t have to write any of the XML plumbing to communicate with Pivotal.

However, the table I’m trying to update has a field type not used in the rest of the app. Within Pivotal, this table field is defined as type “Choice Display 40”. As near as I can tell, this is just a hard coded list of choices. The list is basically an integer value with a text description. The integer value is what is stored in the field, so I assumed this is what I needed to pass to the Active Form.

However, this is not true. Instead, I must pass the text description, and it saves the correct integer value in the database. Weird.

From my vantage point, this is unfortunate, because I can’t figure out where these choices are stored in the database, and so I have to hard code them in my app. Now, if the text is renamed it could completely mess up the app. And since it doesn’t generate an error, that also means users won’t know. Yuck.

But at least I know why it wasn’t saving before.

Posted by Avonelle on Thursday, January 21, 2010. There are 0 Comments.

The answer to Pivotal’s tricky questions: Security, security, security

Once again I was stuck on something with Pivotal, and it turned out security was the answer.

I have an app server rule that attempts to run a Pivotal query and checks for the returned results. Everything was working perfectly, except that the query never returned any results from my code. As it turns out, my user didn’t have the correct permissions for the table, so while the query would run without errors, it never returned any results.

A CDC employee suggested that I try this line of code immediately prior to running the GetRecordset function:

dataAccess.PermissionIgnored = true;

(dataAccess is the instance of the Appserver rule.)

If results are returned after trying this, then the problem is with the permissions. Obviously the right approach is to fix the permissions and not use this everywhere, but it did help me verify that the issues was related to security.

Posted by Avonelle on Thursday, November 05, 2009. There are 0 Comments.

Pivotal web service reports no errors but is missing data: solved

For the last several hours I have been fighting with a problem with a Pivotal web service. The web service runs an active search, and it should have returned several records in the result. Instead it was return zero records with no errors (even in the XML log file). Earlier this afternoon, I verified that the web service LoadById  was working fine to retrieve a single record, so I suspected a weird permissions problem.

I just solved it. The active search calls a target query, which also has a subquery. I took a look at the SQL for both the query and subquery, and realized there was a table being accessed for which I hadn’t granted permissions. Once i added those permissions, it works fine.

Very frustrating. An error in this case would have been much more helpful.

Posted by Avonelle on Thursday, April 09, 2009. There are 0 Comments.

Document your Pivotal web services

Today I encountered something blechy about the Pivotal web service setup. In Pivotal, you must define an integrated web service by selecting the appropriate Active Search and SRL, and/or Active Form. The selections are stored in the business module, and that information is used by the web services generator.

What is yucky is that since only the web services generator needs that information, web services are not included in an RTR. This means when I received a new business module from my client today, I lost all my web service info. In order generate web services again, I had to completely recreate them. I also lost all my security settings, so I had to recreate all of that, too. As far as I know, there is no automated way of doing this. It was a very manual and slow process. And if they send me another one, I’ll have to do the same again. Yuck.

Be advised: document your web services thoroughly so that you don’t to worry about this scenario. In my case I did the next best thing. Since my old web services still existed, I pointed a browser at each .asmx file and took a snap shot of the various methods. I already had a separate document that matched method names up with the appropriate searches, SRLs and active forms. So it was a pain, but not horrible.

I still find the security model almost unintelligible. I suspect the one outstanding problem I have is a security issue (my web service returns no errors but also returns no records; the query in Pivotal returns several records to the search is fine.)

Posted by Avonelle on Thursday, April 09, 2009. There are 1 Comments.

How C# let me down today

I'll admit - C# isn't my regular language of choice. I don't have anything against it personally; I just am not a big fan of case sensitivity and semi-colons. But I never found it truly annoying until today.

As mentioned previously, I'm using the Pivotal web services generator for a way my app to talk to Pivotal. The web services generator creates C# source code, which is fine. Unfortunately, we discovered this issue with the IDs for certain Pivotal objects being hard-coded into the generated code. This is no big deal if you are generating web services for fun or from production, but usually it is preferable to use the same compiled code across development, test and production, and this prevents this approach.

Since I don't expect these web services to change much, I thought an okay workaround would be to modify the generated code to read the IDs from a config file. That way, when we move from test to production, we can just change the IDs. Sounds reasonable, right?


I wanted to make the changes to the generated code in an automated fashion so that if we regenerated, I could just re-run my updates and be good to go. I really only had to change a few places, but this was the one that bit me:

switch (ASRLID)
	case  "8000000000000041":
		Contact_for_Order data1 = new Contact_for_Order();

I thought I could just change the "case" line to use a variable. No can do. This is because while switch....case is similar to select case in VB.NET, it has one important difference: it requires that the case items be constants or enums - variables are not permitted. I read a number of explanations about why it works this way, including those of several people who believe this is superior. Bully for them, but in my situation it really wasn't very helpful. (Apparently I'm not the only person who has been frustrated by this.)

At any rate, the solution was for me to also change the switch...case to an If...then statement. (My code only contained one case item anyway, so it isn't the end of the world.) A bit more work, but not a deal breaker.

Posted by Avonelle on Wednesday, January 07, 2009. There are 0 Comments.

Pivotal Web Services may need to be re-generated on the system they are used on

Ugh. This requirement is very unfortunate. Apparently, when a web service that contains an Active Search is generated, it doesn't reference that Active Search by name. Instead, the ID for the search is embedded in the generated code. Unfortunately, this means that it won't work on another system because the IDs won't match. This is because transferring objects via an RTR does not retain the original IDs.

In my situation it is a real pain because my customer is not set-up to generate web services and recompile. Also, perhaps I'm not understanding something about how moving from test to production will work, but it seems to me that they will have to regenerate and recompile again for that, and that's not a very good approach.

The thing that is most annoying is that there is no indication of this in any of the documentation or support articles I've read. The good news is that you'll figure it out pretty quickly if you turn on XML logging. The log will indicate that it can't find a record it needs for a specific Search ID.

I'm sure there is a reason why they took this approach instead of using the Search name. But I wish they had done it in a more clever way that doesn't involve regeneration. For example, they could have put those IDs in a resource or config file. Then regeneration when moving to a new system wouldn't have been necessary. Blech.

Posted by Avonelle on Wednesday, January 07, 2009. There are 1 Comments.

How to correctly represent dates in a Pivotal Web Service

I've been struggling tonight with the correct way to update a record using a Pivotal Web Service when the record includes a datetime field. I'm not sure that I completely understand what is required, but here's what works for me:

yyyy-MM-dd + T + HH:mm:ss.000

So, for example, the current date and time value is: 2008-12-08T22:57:13.000

I'm guessing the "T" stands for "time", but it is just speculation on my part. And the final 3 digits are probably fractional seconds, but I couldn't care less, as I don't need anything that precise. Formatting my data in this way is working for me.

Posted by Avonelle on Monday, December 08, 2008. There are 0 Comments.

Pivotal Web Service - Oddball Permissions

I had a difficult time getting some of my web services set up properly with the correct Pivotal permissions. Here are a few tips about what your web service user will need for permissions that aren't particularly obvious:

  • If you are configuring your web service to include a search service, the Search Results List you define must be set as Scriptable. Also, one of the Search Result Lists for the table associated with your SRL must be set to Default. My understanding is that the SRL you are using for the web service does NOT have to be the Default, but a Default has to be defined. Otherwise you will probably get an error. I was getting the errors "Access to the specified object is denied." and "Unspecified error", written to the XML logging file.
  • You need a search results list defined for the associated table for your web service even if your web service does not use include a search service and instead only includes a record level service. I just figured this one out a few minutes ago. One of my web services wasn't working and I couldn't figure out why. It occurred to me that like the problem above, Pivotal might be expecting something that wasn't there. Since the previous problem related to search results list and the table for this service did not have one defined, I simply created one and made sure it had the above permissions. Now the service is working. (The error I was getting in the XML logging file was: "Access to the specified object is denied. The requested header was not found.")

Hopefully documenting this will help me to remember these rules when it is time to deploy the new web services.

Posted by Avonelle on Friday, December 05, 2008. There are 0 Comments.

Creating a Pivotal Web Service: What you need before you can "Generate"

Pivotal's Generate Web Service tool is really something that is used at the end of a process. Before you can generate a Pivotal web service, you must first:

  • Create all the necessary objects (searches, lists, forms, etc.) for your web service in Pivotal
  • Define the web service in Pivotal
  • Give the user who is generating the web service the appropriate permissions

Only then will you be ready to generate your web service code.

Web services in Pivotal are tied to specific tables, and can include Search Services and/or Record Level Services. Search services allow you to retrieve records from Pivotal by querying the data in some way. If you are not familiar with Pivotal or it's syntax, you may find Search Services a bit daunting. Here are the steps for creating all the objects necessary for a Search Service:

  1. Find an existing business object for the service you intend to create (or create a new one).
  2. Create or find an appropriate List.
  3. Find a Target Query based on the previously mentioned business object and list (or create a new one if one doesn't exist).
  4. Create an Active Search for the Target Query.
  5. Create or find an appropriate Search Results List.
  6. Create a new Integrated Web Service and define the search service with the Active Search and Search Results List.

I struggled with this a lot at first because I didn't really understand all these objects in Pivotal. Since I started at the end (Create a new web service...) I would get dialog boxes with drop down lists but I didn't know where the data for the drop down lists was coming from. Hopefully this list will help others to complete the process in the correct sequence instead of working backwards.

Posted by Avonelle on Friday, December 05, 2008. There are 0 Comments.

Pivotal Debugging Tools

There are some things in Pivotal when generating web services that can help you troubleshoot issues you may encounter. The tools I am aware of are:

Web Service ReadMe.XML file. This file is created/updated when you generate a web service and the generation process fails. In my situation, the failure is almost always a permissions problem. The file looks something like this:

<?xml version='1.0' ?>
<error>The form '0x80000000000000C0' could not be loaded.This error message was generated for Active Form: 80000000000000C0</error>

Not a ton of information, but since each web service is tied to only one active form, it is easy to narrow down the specific object if not the cause. The file is placed in the virtual directory where your web service resides.

XML Logging file. This file contains detailed information about what the XML commands, results and errors that are happening behind the scenes when using web services. To start XML logging, you must use the LCS customization assistant. (If you can't find a menu option for it, it is LCSDev.exe and is located in the ePower folder of Pivotal.) After starting the LCS customization assistant, select the Debugging tab. I have Log XML Commands, Log XML Results, and Log XML Errors all checked.

SQL File Log. This will log all the SQL going through Pivotal to a file. This was particularly helpful for me in determining I had accidentally messed up one of my tables through my complete misunderstanding of how something in Pivotal works. To start/stop logging, you must modify the appropriate Pivotal 'System" using the Pivotal System Manager. The dialog has a tab called 'Windows Access". check the "Log SQL to File" box and indicated the name and location of the log file to be created.

Event Log. Sometimes I will find something useful in the event log I haven't found any place else. When you are really stuck, don't forget to look there.

Posted by Avonelle on Friday, December 05, 2008. There are 1 Comments.

Generating Web Services with Pivotal Includes some Additional Tasks

Here are a couple of additional tasks you might want to perform when generating a web service:

Shutdown the Lifecycle Engine AppServer COM+ component. This component caches the permissions of the user when it first runs, so if you have changed the permissions at all (like if you just added a new method to your web service in Pivotal), you'll want to shut it down. That way, when it is called again it will have the updated permissions. Keep this in mind if you try to generate a web service and the generation fails with a security error. You'll need to update the permissions, then shut down the component again before attempting web service generation.

Change the ASP.NET version for the IIS virtual folder. I wanted my web services to run under ASP.NET 2.0 and not ASP.NET 1.1. This works fine, but every time you generate a new web service, it resets the virtual folder. So, if you generate a web service, then make changes and regenerate, you will need to reset the ASP.NET version again in IIS. Otherwise you will get an error when you try to access the web service.

Posted by Avonelle on Friday, December 05, 2008. There are 0 Comments.

Capturing the stuff in my head on Pivotal

Recently I started working with a customer relationship management package called Pivotal from CDC Software that one of my manufacturing clients uses. Specifically, it has a feature for generating web services that can query and update the underlying database, which is what I'm working with.

For a programmer with no Pivotal experience, it has been a challenging experience. Unfortunately it isn't as simple as I expected, and I've run into a lot of snags along the way, as well as some things that probably make sense to someone with Pivotal experience but completely flummoxed me. There is quite a bit of documentation for Pivotal, but it is hard to find the right document sometimes, and it isn't always as complete as it could be.

Over the next couple of weeks I'll be posting on some of the things I have learned.  Hopefully this will help the next guy who is trying to work with Pivotal.

Posted by Avonelle on Friday, December 05, 2008. There are 0 Comments.