SQL server native webservices and PHP

35 pts.
Tags:
Apache
PHP
SQL Server
SQL Server 2008
WebServices
Hi, I have a SQL server 2008 database and I need to query it from PHP running in a Linux box. I thought that using webservices was our best option. I am trying to use native HTTP SOAP in SQL server. Following some tutorials, I made first an SP
USE [Basededatos]
GO
/****** Object: StoredProcedure [dbo].[c_cons_int] Script Date: 04/06/2011 13:56:17 ******/
[...]
CREATE PROCEDURE [dbo].[c_cons_int] AS
BEGIN
SELECT nro_internet from int_internet where nro_internet=4377 OR nro_internet=4378
END
that is, a simple SELECT, no parameters. Then, an ENDPOINT
CREATE ENDPOINT [DatosInternet] AUTHORIZATION [sa] STATE=STARTED
AS HTTP (
PATH=N'/internet',
PORTS = (CLEAR),
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED), SITE=N'192.168.2.2',
CLEAR_PORT = 80,
COMPRESSION=DISABLED
)
FOR SOAP (
WEBMETHOD 'Datos'(
NAME=N'[Basededatos].[dbo].[c_cons_int]' ,
SCHEMA=STANDARD ,
FORMAT=ALL_RESULTS
),
BATCHES=DISABLED,
WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=DISABLED,
SESSION_TIMEOUT=60,
DATABASE=N'basededatos',
NAMESPACE=N'HTTP://192.168.2.2/',
SCHEMA=STANDARD,
CHARACTER_SET=XML
)
This is the PHP program (simplified, I found som code that allowed me to use NTLM authentication)
<?

$client=new SoapClient("http://192.168.2.2internet?wsdl",array('login'=>"user",'password'=>"pass")); 

<pre>
print_r($client->Datos());
</pre>

?>
I do get an answer. The problem is that the answer is this:
stdClass Object
(
[DatosResult] => stdClass Object
(
[SqlRowSet] => stdClass Object
(
[schema] => Array
(
[0] =>
[1] =>
)

<any] => 4377 4378
)

[SqlRowCount] => stdClass Object
(
[Count] => 2
)

[SqlResultCode] => 0
)

)
That means, data comes as a single string in <any] I need them to come as separate items. I tried "FOR XML AUTO, ELEMENTS" in the SP but it stops working. I am sure the problem is the way in which the WSDL file is generated. Any ideas? Thanks!

Software/Hardware used:
PHP over Apache on CentOS 5.4, SQLserver 2008 on SBS 2008

Answer Wiki

Thanks. We'll let you know when a new response is added.

It’ll be a lot easier to just log into the database and run the procedure manually. SOAP endpoints are being removed from SQL Server in the future.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Claubolson
    Sorry, I couldn't edit the question, the answer I got was stdClass Object [...] [0] => [1] => ) [any] => 4377 4378 ) [SqlRowCount] => stdClass Object ( [Count] => 2 ) [SqlResultCode] => 0 ) )
    35 pointsBadges:
    report
  • Claubolson
    Mrdenny: how do I do that, from other server running PHP on LInux?
    35 pointsBadges:
    report
  • Denny Cherry
    You'll have to get one of the PHP guys to provide you sample code on how to create a database connection and execute the stored procedure. There's sample code all over the net on doing this, as this is how most every PHP application logs into a database. Just about any sample database code should work, just change the connection string to allow it to connect to the Microsoft SQL Server.
    66,005 pointsBadges:
    report
  • Claubolson
    OK, I tried before what you said but: 1. that is exactly what I didn't want to do: expose the server to the internet 2. PHP on Linux doesn't have connection for mssql 3. Only way is using ODBC, ADODB, etc, and form some reason I can't recall now I wasn't able to do it. Thanks anyway I will install PHP in the windows server and set up the WS there.
    35 pointsBadges:
    report
  • Denny Cherry
    You shouldn't have to expose your SQL Server to the internet in order to have your web server access it, unless your SQL Server and web server are in different data centers and you don't have a VPN setup between the data centers. There are PHP on linux drivers available for SQL Server. I don't know where they are found. Someone else on here can assist you with that.
    66,005 pointsBadges:
    report
  • Claubolson
    [...] 7. Mrdenny provided some guidance and an approved answer to a question regarding SQL Server native web services and PHP. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following