Web Service Help

[Former Member]

I am an old dog trying to learn new tricks. I need to create a web service on an SQL Anywhere 16 database and call it from a Powerbuilder 10.5.1. Build 6021 application. Since I have never done this, I am having a hard time trying to create this process. Where I'm stuck is I created a web service and now I'm trying to create a proxy on the application but it asks for a WSDL file and I have no idea how that is created. Any help will be appreciated.

Rick

[Former Member]

Ok. Almost at the finish line. Here's what I've done.

1. Created a Stored Procedure (Function) called GetValue. It has 2 input variables. It returns a varchar value.

2. Created a SOAP web service called MyTest.

3. Created a DISH web service called MyWS.

4. Created the proxy for MyWS and deployed it.

5. Put this code in a button on a window to call the web service.

SoapConnection lnv_soap

myws myproxy

string ls_arg1, ls_arg2, ls_response

long ll_rtn, ll_sqlcode

lnv_soap = CREATE SoapConnection

ll_rtn = lnv_soap.CreateInstance(myproxy, "MyWS",  "http://localhost:8080/MyWS" )

ls_arg1 = 'XXX'

ls_arg2 = 'YYY'

ls_response = myproxy.mytest(ls_arg1, ls_arg2, ll_sqlcode)

Destroy lnv_soap

This is what I get back.

<rowset>

  <xs:schema id="rowset" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

    <xs:element name="rowset" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

      <xs:complexType>

        <xs:choice minOccurs="0" maxOccurs="unbounded">

          <xs:element name="row" minOccurs="0" maxOccurs="79228162514264337593543950335">

            <xs:complexType>

            </xs:complexType>

          </xs:element>

        </xs:choice>

      </xs:complexType>

    </xs:element>

  </xs:schema>

</rowset>

If I call the Function GetValue through Sybase Central and pass those parameters, I get the return value of 'None'. But I don't see that value anywhere in the return schema set. What am I missing?

[Former Member]

Any ideas on what I am missing?

[Former Member]

Hi Rick;

  Did you run the SP with the Debugger from Sybase Central to see what happens in the code-line when its called from your PB application?

Tip: You also might want to try & use the free Fiddler utility to see what happens at the WS/XML interaction level:  Fiddler free web debugging proxy

Regards ... Chris

[Former Member]

What version/build of SQLAnywhere are you on?  I remember this being a bug that was fixed in one of the EBFs to 16.0...

[Former Member]

Rick,

I couldn't tell what is missing sample.   Here is the sample that I am using:

CREATE SERVICE "Samples/TestSoapOP1" TYPE 'SOAP' AUTHORIZATION OFF USER "DBA" DATATYPE ON AS call sp_techo(:i,:f,:s);

CREATE PROCEDURE sp_techo(i INTEGER, f REAL, s LONG VARCHAR)
RESULT( ret_i INTEGER, ret_f REAL, ret_s LONG VARCHAR )
BEGIN
SELECT i, f, s;
END;

CREATE SERVICE "mytest2" TYPE 'DISH' AUTHORIZATION OFF USER "DBA" FORMAT 'DNET';

For the webproxy, I use the .NET webservice engine and this url:  http://localhost:8080/mytest2

In PowerBuilder, I am using this code:

mytest2    myproxy
soapconnection lnv_soap
long a1,ll_mylong

real a2

string a3,ls_response

a1=11

a2=1123

a3="AA"

lnv_soap = create soapconnection
lnv_soap.createinstance(myproxy,"mytest2","")
try

ls_response = myproxy.samples_testsoapop1(a1,a2,a3,ll_mylong)

catch (soapexception s1)

messagebox("Soap Exception",s1.getmessage())

end try

messagebox("Here the response output", ls_response)

Output response:

Does that help?

Thanks,

Beverly

[Former Member]

Ahhh.......Did not deploy it. So now I have the object called mytest1. Next issue (hopefully the last), It's giving me a Bad number of arguments for function: mysample. Here's the web service called mysample.

[Former Member]

You have to do these things in this order (you may have done these already, but not in the correct order):

1) Create the stored procedure GetValue.  It should take the two string arguments and return whatever result set it returns (can't see that from your screen shot).

2) Create the SOAP web service mySample that calls GetValue().

3)  Create the DISH web service myTest1. 

4) Now you should be able to open a browser, go to http://localhost:8080/myTest1 and see the WSDL for the service

5) Create the web service proxy project in PB.  Use the .Net engine and point the WSDL URL to http://localhost:8080/myTest1.  Click the "Services" button and select everything - then deploy the project. 

6) You should now see the proxy class myTest1.  If you expand it in the system tree, you should see the method mysample that takes two arguments.

7) Write your code to instantiate the myTest1 proxy class and invoke the mysample method.

[Former Member]

Good deal. So last thing. To call the web service, I have this code.

SoapConnection lnv_soap

mytest1 myproxy

string ls_arg1, ls_arg2, ls_response

long ll_rtn

lnv_soap = CREATE SoapConnection

ll_rtn = lnv_soap.CreateInstance(myproxy, "mytest1",  "http://localhost:8080/mytest1" )

ls_response = myproxy.mysample(ls_arg1, ls_arg2)

Destroy lnv_soap

The issue I am getting is that mytest1 is an illegal data type. When I created the web service proxy, this is what I see for it.

Web Service Generator Project Status

Deployment PBL:  C:\...\system.pbl

Clear PBL on build:  No

Confirm overwrites:  No

WSDL Location:  http://localhost:8080/mytest1

Use .NET Engine:  Yes

Assembly Name:  mytest1.dll

Services selected :

  Service WebService.mytest1:

  Entire Service Selected

The proxy is in the same library as the object that has the code. What I am missing?

[former_member190719]

What is the name of the object that got created when you "deployed" the web services client project?

[Former Member]

To answer Bruce's question....No, I am not needing to parse out the value. I was just using Beverly's code to test the web service. This is really what I need to do with the web service. I need to be able to pass 2 arguments to the web service. If I read the documentation correct, the SQL statement in a web service can be a stored procedure, correct? If that is so, the web service would call a stored procedure then return a value which the web service would pass to the user who called it. How would the SQL statement look like with the variables?

[Former Member]

Call myStoredProcedure (:arg_1, :arg_2 );

[Former Member]

I get these right off the bat.

Illegal data type: mytest1

Illegal data type: pbdom_builder

Illegal data type: pbdom_document

Illegal data type: pbdom_element

[Former Member]

illegal data type = bad target library list.

What does yours look like?  Post the full library list here.

1) Did you generate the web service proxy project? 

2) Which PBL did you generate the proxy into?

3) Is that PBL in your application's library list?

-Paul-

[former_member190719]

It looks like you're trying to use the PBDOM extension as well.  You need to make sure you have that PBD (PBDOM105.PBD) in your library search path or import the PBX (PBDOM105.PBX) into one of your PBLs.

Is the service really returning a string that you need to parse with PBDOM?  The web service client is intended to retrieve the response into a structure that matches that of the web service response if there is a complex response.

[Former Member]

Hi Rick;

  Before you get too carried away in PB'Land ... may I first suggest using something easy to test your SA Web Service from the client perspective.   

FYI: WSDL Test Utility .... wizdl - Web Service GUI Test Tool - Home

FWIW: If your WS is returning a result set ... you should really look into using the WS DataWindow feature.  

HTH

Regards ... Chris

[Former Member]

Ok. Downloaded the SDK and I was able to create a web service proxy using the examples that Paul provided. I also read that I needed to import the PBWSClient105.pbx. So I did that. I tried using the code that Beverly provided earlier but I got alot of exception errors. Here's what she put down.

SoapConnection lnv_soap
mytest1 myproxy
string ls_response
long ll_rc,ll_mylong

lnv_soap = CREATE SoapConnection
ll_rc = lnv_soap.CreateInstance ( myproxy, "mytest1",  "http://localhost:8080/mytest1" )
ls_response = myproxy.mysample(ll_mylong)
Destroy lnv_soap

PBDOM_Builder pbdom_bldr
pbdom_document pbdom_doc
pbdom_element element[]
boolean bb_bool
pbdom_bldr = Create PBDOM_Builder
pbdom_doc = pbdom_bldr.BuildFromString (ls_response)

// retrieves the customer id from customer table
bb_bool = pbdom_doc.getelementsbytagname("id",element[])
integer ii_bound, i
ii_bound = upperbound(element)
for i = 1 to ii_bound
   mle_1.text += element[i].gettext() + "~r~n"
next

Any thoughts or changes needed for on this?

[Former Member]

You didn't say what the errors were...

Make sure that PBWSClient105.pbd (from \Program Files (x86)\Shared\PowerBuilder, or whatever that same folder was for the 10.5 release) is in your project's library list. 

And NOT PBSoapClient105.pbd...

The SoapConnection class (among others) is defined in both PBDs - one is for .Net, the other is for EasySoap.  You only need one and adding both can result in errors (depending upon which one is first in the library list).

-Paul-

[Former Member]

I am currently using PB version 10.5.1 Build 6021. Where can I look at to see which .NET framework version I have?

[Former Member]

Hi Rick;

  If I recall correctly - PB 10 was such a long time ago for me - PB 10.5 (like PB 11.x) is .Net 2.0 based. In fact, "I think" that it was the 1st .Net 2.0 compliant PB version (fingers crossed here for you).

1) If you have the .Net 2.0 SDK installed - it should be here:

   32bit:  C:\Program Files (x86)\Microsoft.NET\SDK\v2.0 32bit

   64bit:  C:\Program Files\Microsoft.NET\SDK\v2.0 64bit

2)  .Net 2.0 SDK for W7:  http://www.microsoft.com/en-ca/download/details.aspx?id=15354

Tip: For the System path .. make sure you expose the SDK's BIN folder (ie):  C:\Program Files\Microsoft.NET\SDK\v2.0 64bit\Bin

HTH

Regards ... Chris

[Former Member]

Next issue. When trying to create a web service proxy, it's telling me this,

"The .NET framework SDK is not installed"

I know I have the .NET Framework installed. Is there something else i may be missing?

[Former Member]

Hi Rick;

  Wow .. your getting close now!  

   Yes, the .Net framework is not the same as the SDK. You will need the SDK as it contains the Microsoft WSDL compiler (WSDL.exe).

   Now - you will need to be careful here as the SDK is tied to: a) CPU type; b) MS-Windows version; and c) .Net release. Also, you will need to let us know what version of PB you are using as PB 11.x is .Net 2.0 based, PB 12.x is .Net 3.5 based, PB 12.5.x is .Net 4.0 based and PB 12.6.x is .Net 4.5 based.

SDK Download (example):   Download Microsoft Windows SDK for Windows 7 and .NET Framework 4 from Official Microsoft Download C...

Tip: Once the SDK is installed - open the DOS command window and type "WSDL.exe /?". If it responds, you should be good to go to process the SA web-service proxy.

Note: Make sure the SDK is in your System Path. For example ... C:\Windows\Microsoft.NET\Framework\v4.0.30319

Regards ... Chris

[Former Member]

Upon hitting the next button, I get this on the next page.

There is no service in the WSDL file

Please go back and select the file again.

[Former Member]

Quick change to Beverly's SQL script:

CREATE SERVICE "mysample"
    TYPE 'SOAP'
    DATATYPE ON
    USER DBA
    AUTHORIZATION OFF
    AS select customers.ID from Customers ;

CREATE SERVICE "mytest1"
    TYPE 'DISH'
    authorization off
    user dba
    FORMAT null;

This just creates the SOAP service before creating the DISH that exposes it. 

Now you should be able to access http://localhost:8080/mytest1 from a web browser, and from the PB web service proxy project.  Make sure to use the .Net engine, not Easysoap.

[Former Member]

Hi Rick;

  The link I posted earlier walks your through the process step by step with screen snapshots to guide you. From the PB side - use a Web Service DataWindow to easily guide you through building a .Net compliant client.

HTH

Regards ... Chris

[Former Member]

The example Chris posted does not cover SOAP and DISH web services.  It mentions that these exist, but the examples provided cover the creation of an HTML web service. 

In other words - If Rick follows this example, he won't get WSDL, and won't be able to use a web services datawindow.

[Former Member]

Hi Paul;

  Yes it does .. the author describes both the need & use of SOAP + DISH in the article and addresses the WSDL issue as well (if you read carefully). 

Regards ... Chris

[Former Member]

Perhaps you can point me to the screen shot where he shows an example of creating a SOAP or DISH web service?

[Former Member]

I started the database with the -xs option and if I open the browser and put this in, http://localhost:8080/mytest1, I do see the schema. But when I am trying to create a web service proxy in PB, and it asks "Which WSDL file do you want to access?", I am unsure where to find the file.

[Former Member]

You have the option of a file or a URL.  Use the URL.

http://localhost:8080/mytest1?WSDL

[Former Member]

Thanks to all who responded to my inquiry last week. Sorry I did not respond back but I had to stop last week to jump on another project. Now I'm back to try this. I created the 2 web services that Beverly put down in her example. I was unable to create the Web service proxy in PB. I am assuming because as Chris pointed out, it has to be exposed. How is this done?

[Former Member]

Hi Rick;

I hope that this article may help you in this endeavour:  http://pbdj.sys-con.com/node/943812

Regards ...Chris

[Former Member]

You restart the SQLAnywhere database service with the extra "-xs http(port=8080)" switch.  That allows SQLAnywhere to start an HTTP listener on port 8080 and exposes your new web services.

-Paul-

[Former Member]

Beverly's example is a good starting point.  The link Chris provided doesn't cover SOAP web services and WSDL.  Those only get created when using the SOAP/DISH types.

[former_member190719]

How did you create the web service?

If you did it from PowerBuilder, you should be able to see the endpoint and the WSDL location in the wizard you used to create it.

For .Net based web services (which is what PB generates) you just add "?WSDL" to the end of the web service endpoint to get the WSDL.

[Former Member]

I created the web service through Sybase Central directly on the database.

[Former Member]

Rick,

I am going to take a try at this:

In Sybase Central, I created these webservice entries:
mytest1
enabled:  yes
type:  DISH
User:  DBA

mysample
enabled:  yes
type:  SOAP
User:  DBA
SQL Statement:  select dba.customer.id from DBA.customer

start asa as the webserver:
dbsrv16.exe "<mydb_path\easdemo.db" -xs http(port=8080)

In PowerBuilder:
create a .net webservice proxy using:  http://localhost:8080/mytest1

SoapConnection lnv_soap
mytest1 myproxy
string ls_response
long ll_rc,ll_mylong

lnv_soap = CREATE SoapConnection
ll_rc = lnv_soap.CreateInstance ( myproxy, "mytest1",  "http://localhost:8080/mytest1" )
ls_response = myproxy.mysample(ll_mylong)
Destroy lnv_soap

PBDOM_Builder pbdom_bldr
pbdom_document pbdom_doc
pbdom_element element[]
boolean bb_bool
pbdom_bldr = Create PBDOM_Builder
pbdom_doc = pbdom_bldr.BuildFromString (ls_response)

// retrieves the customer id from customer table
bb_bool = pbdom_doc.getelementsbytagname("id",element[])
integer ii_bound, i
ii_bound = upperbound(element)
for i = 1 to ii_bound
   mle_1.text += element[i].gettext() + "~r~n"
next

Thanks,

Beverly

[Former Member]

Hi Rick;

  You need to expose the Web Service in SA by locating its Web Service Description Language (or WSDL for short). Most WS's that are deployed to either a J2EE or .Net web server can let you see the WSDL in your web browser via an http link (ie: http://<web server>/<ws root>/<web service>?wsdl.

  For PB, you will need to make sure that your WS is built using SOAP (Simple Object Access Protocol). PB 10.x does not support other protocols.

FYI: Web enabling data on a SQL Anywhere Server using a native web service - CodeProject

HTH

Regards ... Chris