Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot create or alter stored procedure with NUMERIC(15,5) in Dialect 1 database via ExecuteImmediate, but can via DSQL [CORE3985] #1462

Closed
firebird-automations opened this issue Nov 16, 2012 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @jasonwharton

Attachments:
IBOBadNumeric.zip
screenshot-1.jpg
IBOBadNumeric_SQLTraceDump.txt

I have a case where I can execute a statement via a normal DSQL just fine, but it fails when I take the same statement and execute it via the API's execute immediate facility.

Here is the statement:

CREATE OR ALTER PROCEDURE BAD_NUMERIC
(
BAD_IN NUMERIC(15,5)
)
RETURNS
(
BAD_OUT NUMERIC(15,5)
)

AS
BEGIN
END;

I will also attach a demo app that will enable a quick look. Just select the method to execute the statement through and click execute and it will either succeed or an exception will be raised.

@firebird-automations
Copy link
Collaborator Author

Commented by: @jasonwharton

Unzip this to any directory and a folder called IBOBadNumeric will be created there.
You need to have a server running for localhost TCP connection.
Password are the default upon install.
Just run the executable in this zip file.
It will automatically attach to the database in there via localhost.

@firebird-automations
Copy link
Collaborator Author

Modified by: @jasonwharton

Attachment: IBOBadNumeric.zip [ 12260 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @jasonwharton

This is the error that appears for me.

@firebird-automations
Copy link
Collaborator Author

Modified by: @jasonwharton

Attachment: screenshot-1.jpg [ 12261 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @jasonwharton

This is a text dump of the SQL trace of the exact exchanges that took place leading up to the problem.
There may be additional helpful details in here.

@firebird-automations
Copy link
Collaborator Author

Modified by: @jasonwharton

Attachment: IBOBadNumeric_SQLTraceDump.txt [ 12262 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I cannot test with Windows executable connecting to local server.

What is the client dialect (looks like 3)? What is the dialect used in isc_dsql_execute_immediate?

What is a "normal DSQL statement" (I suppose isc_dsql_prepare). What is the dialect used in this call?

@firebird-automations
Copy link
Collaborator Author

Commented by: @jasonwharton

Yes, correct. I should have said with a database created as Dialect 1 but when connecting at Dialect 3.
If I set the application to connect via Dialect 1 then it works just as it should.

My main interest is in pointing out how there is a difference between execute immediate and DSQL.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Jason, prepare, execute, execute immediate functions has all a "dialect" parameter. Behavior may depend on the dialect used in the calls (not only the client one).

@firebird-automations
Copy link
Collaborator Author

Commented by: @jasonwharton

Yes, I confirmed that this is the situation:

Database itself is created with Dialect 1.
Connection is established with Dialect 3.
Execute Immediate call performed with Dialect 3.
DSQL Prepare and Execute call performed with Dialect 3.

The DSQL works just fine.
The Execute Immediate fails with the error noted.

I will create a revised Executable that will allow you to use a remote connection.

@firebird-automations
Copy link
Collaborator Author

Commented by: @jasonwharton

I have a new report.
I stripped away everything and wrote this using direct API calls so that I for sure had control of every aspect of things.
The behavior between DSQL and ExecuteImmediate is the same when the SQLDialect is passed in the same.
They BOTH fail under the conditions where the database is created as Dialect 1 and the statements are executed/prepared with Dialect 3.

It's possible that this statement failing is intended behavior. In which case, feel free to close this issue as resolved.

Thanks for your time and attention.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Yes, the error is intended.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I'd say this is absolutely expected.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant