Simple Insert SQL query

[Former Member]

Hello,

I'm new to B1 so please bear with me.

I downloaded the SDK hoping I could develop some simple CGI programs in Visual Basic (VB6). However I must have lost track somewhere but it seems I need to have Visual Studio .NET 2003 (but I don't). In other words, I don't know how to compile the VB "BasicOperations" examples (missing SAP B1 Objects Bridge API v. 6.5).

In short, can someone please give a quick tip on how I can make a simple VB6 program to perform SQL SAP B1 queries? (required software)

However, I would prefer not to use the SDK if possible. Right now I am using an Apache/PHP Linux web server as a front-end to our customers. From our web site, our clients can view and hopefully modify SAP B1 data. However, the PHP scripts only work on the MS-SQL database (SELECT, UPDATE, INSERT...). It worked fine until now: I now need the PHP script to INSERT new Service Call records. I've managed to do this by INSERTing into OSCL and OINS tables but both callID and insID won't "auto-increment" in the SAP client unless I change these indexes' properties to Identity = TRUE (1,1). Nevertheless, changing SAP B1 index properties may have unexpected consequences although I haven't noticed anything wrong with my backed up database. I haven't found any triggers related to these tables and I don't know how SAP manages these two indexes (callID and insID). Thus my will to try out the SDK and maybe insert records with B1 objects by creating simple VB6 cgi web programs (in turn called by PHP on the Linux server).

So basically my question is: how can I avoid changing the indexes' properties but still correctly insert new records into OSCL and OINS via direct MS-SQL queries?

Are there any alternatives such as setting up our SAP B1 as a SOAP server and using our Linux PHP as a SOAP client?

Sorry for the newbie questions and thank you for your time.

Vieri

[eligargs]

Hello, I have the same problem. I using the lavarel PHP server and when I insert a record into OSCL table, in the SAP cliente, callID doesn´t autoincrement, just un MSSQL. I use the front-end to create 100 service calls automatically.

Anybody can help me?

[Former Member]

Hello Vieri,

First you don't need to have Visual Studio .Net. I think what you're missing is not only the SAP B1 Objects Bridge API v. 6.5 but the whole DI API. Installing the SDK is not enough. There you should use the API for your B1 version (e.g. B1 2005 with DIAPI 2005). There you should compile the BasicOperations example and you can use all the objects the DIAPI has (trust me is very easy to learn).

Now, about the SOAP server, B1 has the DIServer. With it you can make a Web Server. The SDK comes with an example of how. But you must have Visual Studio .NET to use it.

Hope this helps,

Ian

[Former Member]

Thanks Ian,

I have B1 2005 so I downloaded B1DEver112005.zip and executed the setup wizard.

However, it states that "to be able to run B1DE wizards you need Microsoft Visual Studio .NET 2003 installed [...] Specify path to Microsoft Visual Studio .NET 2003".

Have I downloaded the wrong file? Should I ignore this wizard screen?

[Former Member]

Vieri,

B1DE has many examples for VS.NET. With the B1 installer comes a package names DIAPI. If you have it install it. I think B1DE comes also with those libraries. You should add the appropriate reference to your vb6 project (e.g. DIAPI 2005 is named SAPbobsCOM2005.dll).

With the DIAPI installer comes a folder named JCO. There you'll have the JAVA libraries.

HTH,

Ian

[Former Member]

Hello Vieri,

Welcome to this form.

First of all I have to warm you that what you intend to do will not be supported by SAP. You're not allow to modify Business One table using SQL queries. You should always use SDK object to update business one table.

You should have a look to the DI server that allows you to update business one in a "SAP suported" way.

Having say that, here are the answer of your question.

1.

You can download VB6 sample program <a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/businessone/sdk/codesample/sap%20business%20one%20sdk%20di%20api%20samples.htm#_header1_1">here</a>

Just click on <a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sapportals.km.docs/business_packages/a1-8-4/b1vb6_01.basicoperations.zip">Basic Operations Sample VB6 source files</a>

2.

Business One record the next id of object in the table ONNM in the field AutoKey

The service call (OSCL) object code is 191 (you can find this information in the SDK help)

The equipmnent card (OINS) object code is 176

But again, I don't think you should take your second point has an option.

Sebastien

[Former Member]

Thank you Sebastien.

Point 2 clarifies everything.

However, will try to use "SAP supported objects" at least for INSERTs.

Point 1: I suppose I need to download the Basic Operations Sample VB6 source files to the SAP server which should also have Visual Studio 6. If nothing else is needed I will try that out.

Thanks again

[Former Member]

You need a IDE (Visual Studio 6 or .NET) to develop your program, even to compile SAP sample. The link I gave you will just allow you to have the source of the program, but you need to have an IDE.

there is an other thing.

if you don't think you need to make change in the application, like adding a form in Business one (commonly called in this for using the UI API) you may consider to use JAVA.

There are JAVA class you can use to access to the database. using these class, you can update altmost all the table of Business One in a way SAP will support.

HTH

Sebastien

[Former Member]

Sébastien,

all I really need is something really as simple as:

CGI program called by remote PHP script that just executes a query of type:

INSERT INTO OSCL x,y,z VALUES(a,b,c)

(basically I just want to "create" new Service Call records in OSCL).

Will search for the java class...

Thanks again

[Former Member]

By the way, do you know if the java classes are within the SDN or are they an external project? Are they included in the SDK ?