Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Execute Immediate

  1. How-to
0
Votes
Undo
  1. Stephan Schröpel
    Stephan Schröpel
  2. PowerBuilder
  3. Thursday, 3 December 2020 17:24 PM UTC

I like to generate a stored function in iAnywhere 17.

The code, tested in Central works fine, but I want to create the function in the database if not exits during software application using EXECUTE IMMEDIATE (:ls_sql_string).

But there are double quoted declarations of Variables an single quoted variables for strings in the SQL commandstring, so  EXECUTE IMMEDIATE generates an error. 

I tried to replace all quotes with ~", so the SQL string seems to be in right syntax, but EXECUTE IMMEDIATE generates also an error.

How can I get the SQL into a string to work? 

There is a Database Command EXECUTE IMMEDIATE USING QUOTES ON but this is not accepted in Powerbuilder.

How can I get the Execution work?

 

Thanks!

Stephan

 

  1. Embedded SQL
Which method is best for triggering events ?
NativePDF: Is it possible to save two datawindows ...
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 3 December 2020 20:46 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Stephan;

  Have you tried ....

SQLCA.DBParm = "DelimitIdentifier='Yes'"

Regards ... Chris

Comment
There are no comments made yet.
Responses (5)
Chris Keating
Chris Keating Accepted Answer Pending Moderation
  1. Friday, 4 December 2020 16:41 PM UTC
  2. PowerBuilder
  3. # 1

Another option is to use square brackets to delimit the identifiers.  For example:

"CREATE FUNCTION [DBA].[fu_bil_unt_tenants_period]( IN [pl_bil_per_id] integer, [pl_est_unt_id] integer) " +&
" RETURNS VARCHAR(250) " +&
"DETERMINISTIC " +&
"BEGIN " +&
"DECLARE [ls_tenant_list] VARCHAR(500); " ...

Comment
There are no comments made yet.
Stephan Schröpel
Stephan Schröpel Accepted Answer Pending Moderation
  1. Friday, 4 December 2020 08:28 AM UTC
  2. PowerBuilder
  3. # 2

Thank you Chris (that was the reason!) and all others....

The problem was: I had copy&Paste the code from the iAnywhere-Central to PowerBuilder. Including all quotes:

 

ls_sql = "CREATE FUNCTION DBA.fu_bil_unt_tenants_period( IN "pl_bil_per_id" integer, "pl_est_unt_id" integer) " +&
 " RETURNS VARCHAR(250) " +&
"DETERMINISTIC " +&
"BEGIN " +&
 "DECLARE "ls_tenant_list" VARCHAR(500); " 

.......

EXECUTE IMMEDIATE :ls_sql;

 

DBParm DelimitIdentifier='No'

 

After Chris' Tip I changed it to DelimitIdentifier='YES' and it worked!

But to avoid problems to other parts of the software, I switched back to DelimitIdentifier='No' and remoced all quotes in the string ls_sql

ls_sql = "CREATE FUNCTION DBA.fu_bil_unt_tenants_period( IN pl_bil_per_id integer, pl_est_unt_id integer) " +&
" RETURNS VARCHAR(250) " +&
"DETERMINISTIC " +&
"BEGIN " +&
"DECLARE ls_tenant_list VARCHAR(500); " +&

......

And it worked too now!

Thank you all

Have a nice day and stay healthy!

Stephan

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 4 December 2020 15:03 PM UTC
Hi Stephan ... that is "Awesome" news & you are most welcome. :-)

PS: Don't forget to hug a DataWindow today! ;o)

  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 3 December 2020 20:02 PM UTC
  2. PowerBuilder
  3. # 3

If you can't get it to work, create a stored procedure to which you pass the syntax of the execute immediate that you want to do. 

For example: SP_RUN_EXEC_IMMEDIATE. This procedure you call it as you would call any normal stored procedure, without execute immediate.

Then within the SP, the actual EXECUTE IMMEDIATE will be done, using your parameter string.

I should have an example somewhere of when I did this for a Tibero Database, let me know if you need more help.

Also, like Govinda already said, an example of what you want to do would be really helpful.

Comment
There are no comments made yet.
Govinda Lopez @Appeon
Govinda Lopez @Appeon Accepted Answer Pending Moderation
  1. Thursday, 3 December 2020 18:18 PM UTC
  2. PowerBuilder
  3. # 4

Hi Stephen,

 

Have you tried the other way around?

 

For example: 

 

sql = "SELECT EMP_ID, 'TEST', NAME FROM ...."

 

Where in this example the double quotes are external. If not, can you provide an example of the code you have tested?

 

 

Regards,

Comment
Chris Keating
  1. Chris Keating
  2. Friday, 4 December 2020 16:41 PM UTC
This would then treat 'TEST" as a char datatype with the value 'TEST'. It would not be a database column named TEST..
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.