Forbidden, allowed and recommended database issues

[Former Member]

Hi,

This post´s goal is to clarify what SAP says about database manipulation. I think it is not clear yet, as you can see (there will be more for sure).

So i can guess there are 3 possible answer about database manipulation issues, Forbidden, allowed and recommended (For certification).

The topics that come to my mind are:

In SBO database:

- Creating tables using SQL statement (non SBO).

- Creating Stored Procedures.

- Creating Functions

- Creating Views

- Creating Triggers against SBO tables

- Creating Triggers against non SBO tables

- Making Select statemens (from any SP, function or view)

- Making inserts/updates/delete statements against SBO tables.

- Making inserts/updates/delete statements against SBO user tables.

- Making inserts/updates/delete statements against non SBO tables.

In another database:

- Creating Stored Procedures that get data from SBO.

- Creating Stored Procedures that change data in SBO.

- Creating Functions against SBO data.

- Creating Views against SBO data.

I hope Frank can clarify all this and the ones from other people.

Some of them are clear in the previous posts, but I´m looking for a full information post. If we make it complete, perhaps I could create a weblog with the information.

Regards,

Ibai Peñ

[former_member185703 · Accepted solution]

Hi Ibai,

Please note:

1. There's a Forum in the ICC area here on SDN where SAP ICC can be asked questions about

certification criteria (note: SAP ICC is in the end responsible for the execution).

Here is the link

2. I (or someone from ICC) am going to write a Weblog which clarifies the cert. requirements

and the motiviation for each of them (the motivation / overall goals / context is important)

in a comprehensive way.

(I just applied to become a Weblogger ;-); ...but I won't be able to do so in the next 2 weeks ...)

3. However, I will answer your questions around the database here:

Forbidden ("-"), allowed ("0") and recommended ("+") for <b>certification</b>

(may be stricter than SAP Support).

In B1 Company database:

-/0/+
---------------------------------------------------
0  Creating tables using SQL statement (non B1);
   not recommended though.
-  Creating Stored Procedures.
-  Creating Functions
0  Creating Views
-  Creating Triggers on B1 tables
-  Creating Triggers on non B1 tables
0  Making Select statements 
   (from any SP, function or view) 
-  Making inserts/updates/delete statements on B1 tables.
0  Making inserts/updates/delete statements on "No Object" UDTs.
0  Making inserts/updates/delete statements on non B1 tables.

In another database:
0  Creating Stored Procedures that get data from B1.
0  Creating Stored Procedures that change data in "No Object" UDTs in B1.
-  Creating Stored Procedures that change data in B1 tables.
0  Creating Functions on B1 data, 
   IF these functions don't change B1 data of course.
0  Creating Views on B1 data.

The <b>only</b> <u>recommended</u> way to work with the database is:

<b>NOT</b> to work directly with the database!

However, today this would just be the ideal case; unfortunately we are not there yet.

Regards,

Frank

Edited by: Frank Moebius on Feb 28, 2008 4:49 PM

[simone_pastorin · Accepted solution]

Hello, I see a contradiction, in B1 database:
-  Creating Stored Procedures.

-  Creating Functions

0  Making Select statements

   (from any SP, function or view)

I can SELECT with stored procedures and functions but... I cannot create them. How am I supposed to use them if I cannot create them?

Maybe you mean I can create them on another database and SELECT against B1 database?

Thanks in advance

[Former Member]

Hi Frank;

Just one more Q: Indexes on NON B1 tables in B1 Company DB - allowed?

Yaniv G.

[former_member201110]

Hi Ibai,

Thanks for raising this topic in a single thread. I'm also keen to get this information as the responses I've had from SAP have been inconsistent at times. It certainly seems that the answer you get depends on who at SAP you are talking to. There doesn't seem to be a proper document that sets out the rules.

When programming myself, the only unbreakable rule is that I do not update any SAP tables (neither system nor UDT tables) directly. I don't use triggers or views but I do make use of stored procedures (read-only for SBO tables), SQL user-defined functions and non-SBO sql tables. Previously I've read that SAP don't like these objects to be in the SBO database and they need to be set up in a separate database but I know of a number of certified third-party add-ons that use stored procedures and tables in a SBO company database so I'm assuming that this is OK.

Regards,

Owen

[former_member185703]

Hi Owen,

Sorry, but I have to disagree:

>There doesn't seem to be a proper document that sets out the rules.

<b>As mentioned time and again</b> (e.g. just yesterday in a reply to another thread:

The requirements as of today (this version released in Q4/2005) are available in the Test Plan available here on SDN:

[original link is broken]

You can let yourself be guided to that page e.g. through a link on the Developer Area for B1 - here on SDN.

It may of course happen that e.g. non-technical people are not 100% familiar with what is available on SDN (since it stands for SAP <u>Developer</u> Network)...

AND the information in the test plan might not provide enough overview - that was the reason why Ibai had opened this thread...

I will follow up with a reply to it soon.

Regards,

Frank

[former_member201110]

Hi Frank,

Thank you for your response. I had previously downloaded the test tools and had a play but somehow this test plan document passed me by

I had also asked on the SAP Marketplace and was told that they didn't think anything existed.

On page 11 of the test plan it states:

"Therefore the usage of stored procedures in the SAP Business One databases will not be

accepted regardless of their purpose as to write and/or extract data."

But I am aware of certified add-ons that use stored procedures in the company database. My question is, how rigourously are these rules enforced? Are some exceptions granted if the use can be justified (eg read-only)? This goes back to my previous comment, what I hear from one source at SAP is different than another and doesn't always match what I'm seeing in the marketplace.

Kind Regards,

Owen

[former_member185703]

Hi Owen,

I have to admit that there's still something that can be improved to facilitate finding your way to the right information...

>I had also asked on the SAP Marketplace and was told that they didn't think anything existed.

Do you mean you created a message to SAP Support?

Stored procedures:

ASFAIK, "Read-Only" won't be accepted as an excuse by SAP ICC.

However, there seems to be some issues with the solution directory on CPSN and the flavors of "certification status" (I think there are even several) listed there and/or the solutions listed as certified on the license key request page - if this is what you mean by "certified"?

Regards,

Frank

BTW: I am thinking about moving these discussions into the ICC Forum for B1 since this appears to be the right place to me...

[Former Member]

Hi Frank,

Thanks for the info. I think it´s clearer now. Hope you can write the weblog explaining the reasons.

Feel free to move the message if you want, but I think is important for developers too to know this issue. I didn´t know the forum you talk about, and I suppose most of the SBO developers only use this forum (as I do to).

Thanks again,

Ibai Peñ