basic stored procedure hangs in SQLDM 1

[xift]

I am trying to run a simple stored procedure in the SQL Data Manager, to test using the PRINT command, and eventually to test passing parameters.

This is the code I'm using:

  create procedure PrintTest();
  begin
    print 'this is PrintTest';
  end;
  #
  call PrintTest

I first run the "create procedure" statement, and it comes back successfully. I then run the "call Printest" statement and it hangs. When I click the "execute" button it stays pressed in and the program eventually says "Not Responding" in the title bar (under Win2003Server).

This is using SQLDM 8.00.114.000. I've tried this on both Win2003Server and Win2000.

My expectation is that I'm calling something incorrectly, but I don't know what. I think I'm matching the syntax shown in the examples in the help. Or maybe PRINT doesn't work in SQLDM? I've also tried it using "with default handler" but it hangs just the same.

Thanks for any info on this.

- Todd

 

[mirtheil]

Todd,
One very big item to note. The "Print" statement will display a message box (or display on the console for Linux and NetWare) on the machine where the engine is running. If you are using client/server, this means the server not the client. I just tried your code on SQLDM 8.60 (8.0 SP2-- free update from Pervasive's web site) and it worked for me. The only time I've heard of this type of behavior is when the Print was occurring at the server and no one was seeing it so it appeared to hang at the client.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[xift]

Thanks Mirtheil. I haven't tried it on the server to confirm, but I'm sure that was it.

I was hoping "print" would send it's output to the text window of the SQL data manager, but apparently not. I wanted to use it sort of like the Immediate window in Visual Basic, to see intermediate results of a stored procedure in progress. Do you know of any way to do something like that?

- Todd

 

[mirtheil]

There is nothing like that from the Pervasive Stored Procedure syntax. I would suggest not using Stored Procedures. There's usually no performance gain by using them.

I would also suggest updating to Pervasive.SQL V8.6 (SP2) or upgrading to V9 (just shipped) so that you're fully patched.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[xift]

Not using stored procedures? I thought people generally recommended them, not so much for performance but to separate database logic from other processing and UI stuff. I'm actually just starting to use them.

Most of my applications are in VBA using ADO to access our main database system (Anasazi behavioral health software, which is DataFlex converted to Pervasive.SQL). Typically I do most of my queries by building strings and submitting them with a command or recordset object.

Previously I would (laboriously) gather fields together into Access tables or in-memory recordsets with multiple queries. Recently as I've learned more about SQL, I've started creating "temp" tables (self-managed) and have found that to speed things up enormously. Then storing all of the code to create the temp tables in a procedure just helps to keep it isolated from the rest of the project. I can use the same procedure either from VBA or in the SQLDM to look at results.

So what are your thoughts on why not to use them, apart from performance?

Also, I will mention your suggestion about upgrading to my boss. The Anasazi software is apparently somewhat sensitive to the version of PSQL being used, so we can't just upgrade without confirming it with the company.

- Todd