CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Passing Query Parameter to WebService

 



ashesh28
Novice

Jun 17, 2015, 2:36 AM

Post #1 of 15 (7050 views)
Passing Query Parameter to WebService Can't Post

Hi ,


Code
my $som = $soap->GetListItems(  
name(listName => $list_name),
name(query => \value(
name(Query => \value(
name(OrderBy => \value(
name('FieldRef' => {Name => 'Created', Ascending => 'False'}),
)),
)),
)),
name(rowLimit =>10)
);


I have this lines of code which is use to extract Items from SharePoint list.Can someone help me on How to make use of the Query Parameter to extract only specific item from the List , Lets say for example Item where ID=500 only.


ashesh28
Novice

Jun 22, 2015, 6:43 PM

Post #2 of 15 (7017 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi Guys ,

Any solution for my problem ?


Zhris
Enthusiast

Jun 22, 2015, 7:09 PM

Post #3 of 15 (7011 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi,

You can provide a where clause as part of the query element. Refer to the GetListItems documentation for Windows SharePoint Services.

Chris


ashesh28
Novice

Jun 22, 2015, 7:41 PM

Post #4 of 15 (7006 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi ,

This seems to be the Query structure to be passed to the Web service ,
But i am not sure on how to incorporate them to the Perl query


Code
<Query> 
<Where>
<Lt>
<FieldRef Name="ID" />
<Value Type="Counter">3</Value>
</Lt>
</Where>
</Query>


I tried something like this , But i am unable to figure out on where to specify the ID value



Code
my $som = $soap->GetListItems(   
name(listName => $list_name),
name(query => \value(
name(Query => \value(
name(Where => \value(
name(Eq => \value(
name('FieldRef' => {Name => 'ID'}),
name('Value' => {Type => 'Counter'})
name(OrderBy => \value(
name('FieldRef' => {Name => 'Created', Ascending => 'False'}),
)),

)),

)),

)),
)),
name(rowLimit =>10)
);



Zhris
Enthusiast

Jun 23, 2015, 12:39 AM

Post #5 of 15 (6997 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi,

I'm unfamiliar with SOAP::Lite, therefore am probably of little help.

Firstly, the syntax you are using doesn't quite correlate with SOAP::Data, you are using SOAP::Lite in a functional manner via its exported methods which as far as I can tell is undocumented.

I think it would be easist to work with raw xml data, which goes hand in hand with the sharepoint documentation.

Nonetheless, I have looked through the SOAP::Lite documentation / source code. An attr method is exported and it looks as though additional arguments are passed to the value method, whos return value is in turn returned.

Finally, it looks like you've put the order by as part of the where, which I don't think is right.

My best guess until I have time to look deeper into the source:


Code
my $som = $soap->GetListItems(   
name(listName => $list_name),
name(query => \value(
name(Query => \value(
name(Where => \value(
name(Eq => \value(
name(FieldRef => {Name => 'ID'}),
name(Value => \attr(
{Type => 'Counter'}, \value( 3 )
)),
)),
)),
name(OrderBy => \value(
name('FieldRef' => {Name => 'Created', Ascending => 'False'}),
)),
)),
)),
name(rowLimit => 10)
);


Chris


(This post was edited by Zhris on Jun 23, 2015, 12:55 AM)


Zhris
Enthusiast

Jun 23, 2015, 4:45 AM

Post #6 of 15 (6976 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi,

I have been playing around with SOAP::Lite for the last hour and have familiarised myself with its functionality.

You need to be aware that the value method tests if the first argument is a reference of any type, if so then that is designated as the object. Your original code commonly calls the value method, supplying a child SOAP::Lite object, the result of the name method, which I doubt is what you meant. You should not use the exported value function, instead call it as a class method via \SOAP::Data->value. The name method uses the same test, but isn't an issue since the first argument supplied is always a string. Also, the second argument to the name method cannot be a hash reference of attributes. You should instead make use of the attr method.

You stated "I have this lines of code which is use to extract Items from SharePoint list", assumably your original code is broken?

Your original code:

Code
use strict; 
use warnings;
use SOAP::Lite;
SOAP::Data->import( qw/name value/ );

my $list_name = 'mylist';

my $soap = SOAP::Lite->new;

my $element_listname = name(listName => $list_name);

my $element_query =
name(query => \value(
name(Query => \value(
name(OrderBy => \value(
name('FieldRef' => {Name => 'Created', Ascending => 'False'}),
)),
)),
));

my $element_rowlimit = name(rowLimit =>10);

print $soap->serializer->envelope( 'method' => 'GetListItems', $element_listname, $element_query, $element_rowlimit );

# my $som = $soap->GetListItems( $element_listname, $element_query, $element_rowlimit );

Output:

Code
<soap:Envelope soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
<soap:Body>
<GetListItems>
<listName xsi:type="xsd:string">mylist</listName>
<query>
<c-gensym4>
<c-gensym5>
<c-gensym6>
<Ascending xsi:type="xsd:boolean">true</Ascending>
<Name xsi:type="xsd:string">Created</Name>
</c-gensym6>
</c-gensym5>
</c-gensym4>
</query>
<rowLimit xsi:type="xsd:int">10</rowLimit>
</GetListItems>
</soap:Body>
</soap:Envelope>


My modified version with where clause:


Code
use strict; 
use warnings;
use SOAP::Lite;
SOAP::Data->import( qw/name/ );

my $list_name = 'mylist';

my $soap = SOAP::Lite->new;

my $element_listname = name( 'listName' => $list_name );

my $element_query =
name( 'Query' => \SOAP::Data->value(
name( 'Where' => \SOAP::Data->value(
name( 'Eq' => \SOAP::Data->value(
name( 'FieldRef' => SOAP::Data->attr(
{ 'Name' => 'ID' }
) ),
name( 'Value' => SOAP::Data->attr(
{ 'Type' => 'Counter' }, 777
) ),
) ),
) ),
name( 'OrderBy' => \SOAP::Data->value(
name( 'FieldRef' => SOAP::Data->attr(
{ 'Name' => 'Created', 'Ascending' => 'False' }
) ),
) ),
) );

my $element_rowlimit = name( 'rowLimit' => 10 );

print $soap->serializer->envelope( 'method' => 'GetListItems', $element_listname, $element_query, $element_rowlimit );

# my $som = $soap->GetListItems( $element_listname, $element_query, $element_rowlimit );

Output:

Code
<soap:Envelope soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> 
<soap:Body>
<GetListItems>
<listName xsi:type="xsd:string">mylist</listName>
<Query>
<Where>
<Eq>
<FieldRef Name="ID"/>
<Value Type="Counter" xsi:type="xsd:int">777</Value>
</Eq>
</Where>
<OrderBy>
<FieldRef Ascending="False" Name="Created"/>
</OrderBy>
</Query>
<rowLimit xsi:type="xsd:int">10</rowLimit>
</GetListItems>
</soap:Body>
</soap:Envelope>


Note, I wasn't sure if the additional parent 'query' node was necessary, therefore have left out.

Chris


(This post was edited by Zhris on Jun 23, 2015, 4:51 AM)


ashesh28
Novice

Jun 23, 2015, 5:26 AM

Post #7 of 15 (6971 views)
Re: [Zhris] Passing Query Parameter to WebService [In reply to] Can't Post

Hi Zhris ,

Thanks a lot for your valuable insight. I am no familiar with SOAP:: lite module also , But i noticed this being the only option available to be used to extract data with efficiency.

I tried creating Nintex workflow which indeed works well , but it takes well over 15 mins to extract a list of 55 odd items.

As you mentioned the piece of code which i posted is indeed incomplete , I am posting my complete code for you to verify. Please let me know me know if you find a better way to get this done.


Code
 
#!/usr/bin/perl
use v5.10;
use Authen::NTLM;
use SOAP::Lite;
use Text::CSV;
use Data::Dumper;
use EOL;

my %cfg = (
host => 'sharePoint server Address', # must include port
endpoint => 'xxxx/lists.asmx',
user => '\\username', # must have leading backslashes
pass => 'password',
);

# enable NTLMv2 in Authen::NTLM
ntlmv2(1);

# syntax shortening of SOAP::Data methods
sub name {
my ($name, $value) = @_;
my $d = SOAP::Data->name($name);

return $d->attr($value) if (ref $value eq 'HASH');
return $d->value($value) if (defined $value);
return $d; #otherwise
}
sub value { SOAP::Data->value(@_) }

my $soap = SOAP::Lite
->proxy($cfg{endpoint}, keep_alive => 1,
credentials => [$cfg{host}, '', $cfg{user}, $cfg{pass}])
->default_ns('http://schemas.microsoft.com/sharepoint/soap/')
->on_action(sub { $_[0] . $_[1] }) # change default SOAPAction header
->readable(1);

# fetch the list
my $list_name = '{2CDBAED7-0DCE-4BA5-9B1A-29DBE39E03A1}'; # Team task
my $som = $soap->GetListItems(
name(listName => $list_name),
name(query => \value(
name(Query => \value(
name(OrderBy => \value(
name('FieldRef' => {Name => 'Created', Ascending => 'False'}),
)),
)),
)),
name(rowLimit =>10)
);
die $som->faultstring() if defined $som->fault();

my @results = $som->dataof('//GetListItemsResult/listitems/data/row');

my $oc = Text::CSV->new({sep_char => ',', eol => $/ })
or die Text::CSV->error_diag();
open my $of, '>', 'outfile.csv' or die $!;
chomp @results;
foreach my $data (@results) {
my $item = $data->attr;
chomp $item;
$oc->print($of,[@$item{qw( ows_ID ows_Title ows_Last_x0020_Name ows_Emp_x0020_ID ows_Email_x0020_ID ows_Department ows_Phone ows_Job_x0020_Type ows_Technology ows_Customer_x0020_Name ows_Wafer_x0020_ID ows_Lot_x0020_ID ows_Program_x0020_Type ows_Failure_x0020_Mode ows_Failure_x0020_Mode_x0020_Test ows_Sample_x0020_Type ows_Last_x0020_Layer ows_Wafer_x0020_Type ows_Fab_x0020_Out_x0020_Date ows_Yield ows_Yield_x0020_Lost_x0020_1 ows_Yield_x0020_Lost_x0020_2 ows_Yield_x0020_Lost_x0020_3 ows_Add_x0020_Info )}]);

}

close $of;



Zhris
Enthusiast

Jun 23, 2015, 5:29 AM

Post #8 of 15 (6968 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi,

I supplied a modified version in my last post for you to fit in and test.

Chris


ashesh28
Novice

Jun 23, 2015, 5:33 AM

Post #9 of 15 (6965 views)
Re: [Zhris] Passing Query Parameter to WebService [In reply to] Can't Post

Hi Zhris ,

Yes , i just noticed that portion.
Will try to fit modified version with my existing code and see how it goes.

Will post the outcome.
Thanks Again


Zhris
Enthusiast

Jun 24, 2015, 12:29 AM

Post #10 of 15 (6939 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi,

Just looked over your complete script. I see that you overrode the name and value functions in order to provide an alternative interface, invalidating some of my earlier assumptions. You should remove these functions before fitting in my version, it relies on the native interface. It goes to show the importance of providing your full script or at minimum all relevant sections when asking for help.

Chris


ashesh28
Novice

Jun 24, 2015, 1:18 AM

Post #11 of 15 (6934 views)
Re: [Zhris] Passing Query Parameter to WebService [In reply to] Can't Post

Hi Zhris ,

I tried to incorporate the code as below ,


Code
#!/usr/bin/perl 
use v5.10;
use Authen::NTLM;
use SOAP::Lite;
use Text::CSV;
use Data::Dumper;
use EOL;

my %cfg = (
host => 'sdrsspsapq01:21898', # must include port
endpoint => 'xxx/lists.asmx',
user => '\\username', # must have leading backslashes
pass => 'Password',
);

# enable NTLMv2 in Authen::NTLM
ntlmv2(1);

my $soap = SOAP::Lite
->proxy($cfg{endpoint}, keep_alive => 1,
credentials => [$cfg{host}, '', $cfg{user}, $cfg{pass}])
->default_ns('http://schemas.microsoft.com/sharepoint/soap/')
->on_action(sub { $_[0] . $_[1] }) # change default SOAPAction header
->readable(1);

SOAP::Data->import( qw/name/ );
my $listid = shift;
my $list_name = '{2CDBAED7-0DCE-4BA5-9B1A-29DBE39E03A1}';
#my $soap = SOAP::Lite->new;
my $element_listname = name( 'listName' => $list_name );

my $element_query =
name( 'Query' => \SOAP::Data->value(
name( 'Where' => \SOAP::Data->value(
name( 'Eq' => \SOAP::Data->value(
name( 'FieldRef' => SOAP::Data->attr(
{ 'Name' => 'ID' }
) ),
name( 'Value' => SOAP::Data->attr(
{ 'Type' => 'Counter' }, 518
) ),
) ),
) ),
name( 'OrderBy' => \SOAP::Data->value(
name( 'FieldRef' => SOAP::Data->attr(
{ 'Name' => 'Created', 'Ascending' => 'False' }
) ),
) ),
) );

my $element_rowlimit = name( 'rowLimit' => 10 );
my $som = $soap->GetListItems( $element_listname, $element_query, $element_rowlimit );
# my $som = $soap->GetListItems( $element_listname, $element_query );
quit(1, $som->faultstring()) if defined $som->fault();
my @results = $som->dataof('//GetListItemsResult/listitems/data/row');

my $oc = Text::CSV->new({sep_char => ',', eol => $/ })
or die Text::CSV->error_diag();
open my $of, '>', 'outfile.csv' or die $!;
chomp @results;

foreach my $data (@results) {
my $item = $data->attr;
chomp $item;
$oc->print($of,[@$item{qw( ows_ID ows_Title ows_Last_x0020_Name ows_Emp_x0020_ID ows_Email_x0020_ID ows_Department ows_Phone ows_Job_x0020_Type ows_Technology ows_Customer_x0020_Name ows_Wafer_x0020_ID ows_Lot_x0020_ID ows_Program_x0020_Type ows_Failure_x0020_Mode ows_Failure_x0020_Mode_x0020_Test ows_Sample_x0020_Type ows_Last_x0020_Layer ows_Wafer_x0020_Type ows_Fab_x0020_Out_x0020_Date ows_Yield ows_Yield_x0020_Lost_x0020_1 ows_Yield_x0020_Lost_x0020_2 ows_Yield_x0020_Lost_x0020_3 ows_Add_x0020_Info )}]);

}
close $of;


The above shown code return all 10 records when run.


ashesh28
Novice

Jun 24, 2015, 1:37 AM

Post #12 of 15 (6932 views)
Re: [Zhris] Passing Query Parameter to WebService [In reply to] Can't Post

This appears to be the Structure of the Soap query ,


Code
<?xml version="1.0" encoding="UTF-8"?> 
<soap:Envelope
soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Body>
<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<listName xsi:type="xsd:string">{2CDBAED7-0DCE-4BA5-9B1A-29DBE39E03A1}</listName>

<Query>
<Where>
<Eq>
<FieldRef Name="ID" />

<value Type="Counter" xsi:type="xsd:int">518</value>
</Eq>
</Where>

<OrderBy>
<FieldRef Ascending="False" Name="Created" />
</OrderBy>
</Query>

<rowLimit xsi:type="xsd:int">10</rowLimit>
</GetListItems>
</soap:Body>
</soap:Envelope>



Zhris
Enthusiast

Jun 24, 2015, 2:22 AM

Post #13 of 15 (6929 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi,

Take a look at the community additions of the online documentation for getlistitems ( https://msdn.microsoft.com/en-us/library/lists.lists.getlistitems%28v=office.12%29.aspx ). krisneo mentions that the <Query> section must be wrapped inside a parent <query> in order to execute the query properly. Make this modification then report back.

Chris


ashesh28
Novice

Jun 25, 2015, 1:59 AM

Post #14 of 15 (6905 views)
Re: [Zhris] Passing Query Parameter to WebService [In reply to] Can't Post

Hi Zhris ,

Million thanks for all your help boss.
Finally i learnt something new and managed to get expected result.

Adding <query></query> does resolved the issue of returning Query result. Later i had issue with querying field with "Space" in the field , For example " First Name" .

i figured out the solution by adding "First_x0020_Name" to the Field Ref Name parameter (Replacing space with x0020).Also figured out on how to query Date based Fields.

Complete Working Code



Code
#!/usr/bin/perl 
use v5.10;
use Authen::NTLM;
use SOAP::Lite;
use Text::CSV;
use Data::Dumper;
use EOL;

my %cfg = (
host => 'ServerName', # must include port
endpoint => 'xxxx/lists.asmx',
user => '\\username', # must have leading backslashes
pass => 'Password',
);

# enable NTLMv2 in Authen::NTLM
ntlmv2(1);

my $soap = SOAP::Lite
->proxy($cfg{endpoint}, keep_alive => 1,
credentials => [$cfg{host}, '', $cfg{user}, $cfg{pass}])
->default_ns('http://schemas.microsoft.com/sharepoint/soap/')
->on_action(sub { $_[0] . $_[1] }) # change default SOAPAction header
->readable(1);

SOAP::Data->import( qw/name/ );
my $listid = shift;
my $list_name = '{2CDBAED7-0DCE-4BA5-9B1A-29DBE39E03A1}';
my $element_listname = name( 'listName' => $list_name );
my $data = '2015-01-15T00:00:00Z';
my $FieldName = 'Completion_x0020_Date';
my $element_query =
name( 'query' => \SOAP::Data->value(
name( 'Query' => \SOAP::Data->value(
name( 'Where' => \SOAP::Data->value(
name( 'Gt' => \SOAP::Data->value(
name( 'FieldRef' => SOAP::Data->attr(
{ 'Name' => $FieldName }
) ),
name( 'Value' => SOAP::Data->attr(
{ 'Type' => 'DateTime' }, $data
) ),
) ),
) ),
name( 'OrderBy' => \SOAP::Data->value(
name( 'FieldRef' => SOAP::Data->attr(
{ 'Name' => 'Created', 'Ascending' => 'False' }
) ),
) ),
) ),
) );

my $element_rowlimit = name( 'rowLimit' => 1000 );
print $soap->serializer->envelope( 'method' => 'GetListItems', $element_listname, $element_query, $element_rowlimit );
my $som = $soap->GetListItems( $element_listname, $element_query, $element_rowlimit );
my @results = $som->dataof('//GetListItemsResult/listitems/data/row');


my $oc = Text::CSV->new({sep_char => ',', eol => $/ })
or die Text::CSV->error_diag();
open my $of, '>', 'outfile.csv' or die $!;
binmode $of, ':utf8';
chomp @results;
foreach my $data (@results) {
my $item = $data->attr;
chomp $item;
$oc->print($of,[@$item{qw( ows_ID ows_Title ows_Last_x0020_Name ows_Emp_x0020_ID ows_Email_x0020_ID ows_Department ows_Phone ows_Job_x0020_Type ows_Technology ows_Customer_x0020_Name ows_Wafer_x0020_ID ows_Lot_x0020_ID ows_Program_x0020_Type ows_Failure_x0020_Mode ows_Failure_x0020_Mode_x0020_Test ows_Sample_x0020_Type ows_Last_x0020_Layer ows_Wafer_x0020_Type ows_Fab_x0020_Out_x0020_Date ows_Yield ows_Yield_x0020_Lost_x0020_1 ows_Yield_x0020_Lost_x0020_2 ows_Yield_x0020_Lost_x0020_3 ows_Add_x0020_Info ows_Completion_x0020_Date )}]);
}
close $of;


Thanks Again Zhris for your time and suggestions.


Zhris
Enthusiast

Jun 25, 2015, 9:41 AM

Post #15 of 15 (6897 views)
Re: [ashesh28] Passing Query Parameter to WebService [In reply to] Can't Post

Hi,

Really glad to hear that you solved this problem and were able to fix further issues on your own initiative.

With regards to the date based field, have a quick read of SOAP::Data type detection, this is likely a cleaner solution.

For consistency, consider changing instances of "name" to "SOAP::Data->name". In future you may prefer to use the heaviliy documentated "class" orientated interface, resulting in much more readable notations:


Code
SOAP::Data->name ( 'Value' ) 
#->attr ( { 'Foo' => 'Bar' } )
->value( $data )
->type ( 'DateTime' );


Best regards,

Chris


(This post was edited by Zhris on Jun 25, 2015, 9:47 AM)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives