Recently we had some serious problems in our system. One of the chief operations was aborting with errors. Me and another developer decided to dig into the code. We found where the error was being generated. Right before the error, there was some SQL in a Pro*C file that we just did not understand. The code was doing an “EXEC SQL AT” and then declaring a cursor. Neither of us knew definitively what the AT meant.The developer I was working with had a lot of guesses as to what the AT meant. He called up some people with Oracle experience. However this did not help us. We needed somebody with Pro*C experience. We both did some Google searches. I finally found some articles online that helped me get the point.
You use an EXEC SQL AT statement when you are using multiple connections to the database. Each connection would have been started with an EXE SQL CONNECT. The connections themselves were names when you connected to the database. Here is an example:
EXEC SQL CONNECT user
IDENTIFIED BY password
AT connection1
USING dbname;
Later, if you want to use this particular connection to execute a SQL statement, you reference the connection name. Here is an example:
EXEC SQL AT connection1
SELECT COUNT(*) FROM bigtable;
Our software was creating a separate transaction to perform the main operation. This was separate from the normal database connection used for the rest of the database access. Therefore the Pro*C SQL code for this specific connection required the AT clause. You learn something new every day. I think the moral of this story was that I need some more Pro*C training to be prepared for my current project.
