Below is an example PHP script which accesses the sample WCF Service defined by the WCF template in Visual Studio 2010.
-
GetDataUsingDataContract(CompositeType composite)
This function is declared by the template WCF Service in Visual Studio 2010. It accepts a ‘CompositeType’ object as the parameter, which is itself defined within the webservice. This object has two properties, a bool called ‘boolValue’, and a string called ‘stringValue’.
To pass a parameter from PHP, a similar class has to be defined within the PHP as well.
//Declare the class that will be passed as the parameter
-
class myCompositeType{
-
public $boolValue;
-
public $stringValue;
-
}
-
-
//instantiate an object of the class we just created
-
$obj = new myCompositeType();
-
//Assign values to the properties of the class
-
$obj->boolValue=true;
-
$obj->stringValue="World!";
-
-
//Wrap up the object before it can be passed
-
//Ensure that the array key is exactly the same as the expected parameter name declared in the webservice.
-
$myParameter = array('composite'=>$obj);
-
-
//Create a SOAP client
-
$client = new SoapClient("path to your .wsdl");
-
-
//Call the method, passing our parameter
-
$retVal = $client->GetDataUsingDataContract($myParameter);
-
-
//Handle the result, which is also an object of the myCompositeType class
-
echo $retVal->GetDataUsingDataContractResult->stringVal;
(Ensure that the array key is exactly the same as the expected parameter name declared in the webservice.)
If you are familiar with your database structure, and have permissions, it is possible to quickly setup a user interface to your SQL database using just Microsoft Excel.
There are 2 approaches that can be taken to achieve this in a matter of minutes:
- Office Data Connection
- On the Data tab, select ‘From Other Sources’>’From SQL Server’. You will see a prompt to connect to the database, where you must enter the SQL Server hostname and login credentials. The next window will ask which database to connect to. At this point, you can click ‘Finish’ to end the process of creating your database connection.
- The next step is to select the data that you want to pull through into Excel. On the Data tab, select ‘Existing Connections’, and open the connection that you have just created. Click on the ‘Properties…’ button, and select the ‘Definition’ tab. Choose ‘SQL’ from the ‘Command Type:’ dropdown, and write your SQL query in the ‘Command Text:’ field. Click OK to end.
- Your data will be taken from the database and displayed as a table in your Excel spreadsheet.
- Microsoft Query
- This solution requires a System DSN, which is created using the interface in Control Panel>Administrative Tools>Data Sources (ODBC). We recommend that your DSN is created using a read-only account.
- On the Data tab, select ‘From Other Sources’>’From Microsoft Query’. Select the DSN that you have setup to connect to the correct database.
- You will now step through a series of stages to create your query. If you prefer you can skip to the last screen, and select ‘View data or edit query in Microsoft Query’, where you will be able to use the visual interface to generate your SQL query.
- To send the results back to Excel, click ‘File’>’Return Data to Microsoft Office Excel’.
To make the query even more useful, you can also pass parameters from Excel to your query. This builds a level of user interaction into the dataset, and enables report customisation within a matter of seconds.
When connecting Excel 2007 to a SQL Server database, the ‘Parameters…’ button remains disabled and greyed out, until at least one parameter has been included in your query. To include the first parameter within the query, and therefore activate the ‘Parameters…’ button, within Query Manager type your user prompt surrounded by square brackets, for example ’[Please enter the start value of the desired range]‘.
Now, from the Data tab, select ‘Connections’ and choose ‘Properties…’ for the connection that you want to make customisable. Go to the ‘Definition’ tab and now you will see that the ‘Parameters…’ button has been activated. Click this button to modify how the parameter is populated. There are 3 options here:
- Prompt the user with a popup window
- Use a static value
- Use the contents of another Excel cell
Option 3 enables an Excel user to customise the report without any knowledge of databases or SQL, just the ability to update the contents of an Excel cell.
This is a really nifty method to allow users on your network to safely access your data. If you have used a read-only DSN there is no risk of a user successfully performing something malicious – DROP DATABASE database_name!
To return an ADODB.Recordset in Visual Basic Application (VBA), from a function, the Recordset must be disconnected.
-
Function queryDatabase(strSQL, username, password) As ADODB.Recordset
-
-
'Declare our variables
-
Dim strConnection, conn, rs
-
-
'Initialisation
-
Set rs = New ADODB.Recordset
-
strConnection = "Provider=sqloledb;Data Source=serverName;Initial Catalog=DbName;User Id=username;Password=password"
-
Set conn = New ADODB.Connection
-
-
conn.Open strConnection
-
rs.CursorLocation = adUseClient
-
rs.Open strSQL, conn, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic
-
-
'Disconnect the connection
-
Set rs.ActiveConnection = Nothing
-
-
'Return the recordset
-
Set queryDatabase = rs
-
-
conn.Close
-
Set conn = Nothing
-
End Function
-
-
Sub main()
-
Dim rs As ADODB.Recordset
-
Set rs = queryDatabase("SELECT cols FROM DB", username, password)
-
-
'Now we can handle our recordset, returned from the function
-
rs.MoveFirst
-
While Not rs.EOF
-
MsgBox (rs("colName"))
-
rs.MoveNext
-
Wend
-
-
rs.Close
-
Set rs = Nothing
-
End Sub
Only a disconnected recordset is able to be passed around in your code and handled later.
