Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to pass parameters to SQL Queries used as Filters...

4 posts in Trial Discussion Last posting was on 2007-11-05 17:30:28.0Z
Dan Posted on 2007-10-23 17:56:02.0Z
Sender: 5bae.47153d42.1804289383@sybase.com
From: Dan
Newsgroups: ianywhere.public.secureemail.trial
Subject: How to pass parameters to SQL Queries used as Filters...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <471e35b2.343c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Oct 2007 10:56:02 -0700
X-Trace: forums-1-dub 1193162162 10.22.241.41 (23 Oct 2007 10:56:02 -0700)
X-Original-Trace: 23 Oct 2007 10:56:02 -0700, 10.22.241.41
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.secureemail.trial:207
Article PK: 11022

I have several Database Sync Actions that I would like to
give additional SQL Filter information. Specifically I want
to give something more than then Device Id, User, etc. How
can I do this? For example, I may want to filter the
server's table down by a date specified by me on the mobile
client.

I see where I can specify the SQL to execute during the sync
process (Sync Admin - Database Sync Action - Edit Table Map)
but is there a way I can specify this at "sync time" or
right before the call to
mobileObjectConnection.DoAction(...)?


Mark Wright Posted on 2007-10-24 18:16:59.0Z
From: "Mark Wright" <mark.wright@ianywhere.com>
Newsgroups: ianywhere.public.secureemail.trial
References: <471e35b2.343c.1681692777@sybase.com>
Subject: Re: How to pass parameters to SQL Queries used as Filters...
Lines: 52
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: surfwc054.sybase.com
X-Original-NNTP-Posting-Host: surfwc054.sybase.com
Message-ID: <471f8c1b@forums-1-dub>
Date: 24 Oct 2007 11:16:59 -0700
X-Trace: forums-1-dub 1193249819 192.138.150.54 (24 Oct 2007 11:16:59 -0700)
X-Original-Trace: 24 Oct 2007 11:16:59 -0700, surfwc054.sybase.com
X-Authenticated-User: dfuser
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.secureemail.trial:212
Article PK: 11025

There are different approaches to this question. Currently we have no way
of passing a variable to the server during a sync session so you need to
take an approach of getting the variable to the server before the next sync
action occurs.

The best way to do this is to you use a Systems Variables Table. This is a
table that you will create in your server database. The table will contain
the field USERID and then any other field that you will use for a variable,
in your case lets call it Mobile_Date.

In the server Database Adapter there is a selection for you to point to this
Systems Variables Table.

You will then configure a Database Sync Action that will run before all the
other Database Sync Actions and it will synchronize the Date from the mobile
to this Systems Variables Table and it will put the date in the row that
contains the correct USERID that is connecting. Each User that will be
using the system will need to have their username put into the USERID field
of this table. The sync action SQL query will be "Select * from
SystemsVariablesTableName Where USERID = %OBUSERID%"

The subsequent sync actions will now be able to use the date as a variable
and you will be able to write a query like:

"Select * from tableA Where DateField = %Mobile_Date%" Mobile_Date will
then be replaced with whatever value is in the table for the currently
connected user.

This may not be completely clear, but hopefully it is what you are looking
for. See the documentation for Systems Variable to get a better
understanding, or call your local support team.

Thanks

Mark

<Dan> wrote in message news:471e35b2.343c.1681692777@sybase.com...
>I have several Database Sync Actions that I would like to
> give additional SQL Filter information. Specifically I want
> to give something more than then Device Id, User, etc. How
> can I do this? For example, I may want to filter the
> server's table down by a date specified by me on the mobile
> client.
>
> I see where I can specify the SQL to execute during the sync
> process (Sync Admin - Database Sync Action - Edit Table Map)
> but is there a way I can specify this at "sync time" or
> right before the call to
> mobileObjectConnection.DoAction(...)?


Dan Posted on 2007-10-26 19:36:23.0Z
Sender: 5bae.47153d42.1804289383@sybase.com
From: Dan
Newsgroups: ianywhere.public.secureemail.trial
Subject: Re: How to pass parameters to SQL Queries used as Filters...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <472241b7.1c0f.1681692777@sybase.com>
References: <471f8c1b@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Oct 2007 12:36:23 -0700
X-Trace: forums-1-dub 1193427383 10.22.241.41 (26 Oct 2007 12:36:23 -0700)
X-Original-Trace: 26 Oct 2007 12:36:23 -0700, 10.22.241.41
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.secureemail.trial:216
Article PK: 11028

Makes perfect sense. Thanks.

Might I suggest a feature enhancement as this approach is
very expensive in bandwidth and setup? Good, here it is:

Overload the DoAction() method to take a Map. The Map would
be your typical Key/Value pair list. The DoAction call
would then transfer these to the server for parameter
substitution. This should increase performance by
light-years as there would be one less sync, one less
database table read, etc.

This also makes your product incredibly powerful since you
are allowing more dynamic SQL.

Another suggestion would be to allow me to specify the SQL
to use as a filter during DoAction.

Thanks again.

> There are different approaches to this question.
> Currently we have no way of passing a variable to the
> server during a sync session so you need to take an
> approach of getting the variable to the server before the
> next sync action occurs.
>
> The best way to do this is to you use a Systems Variables
> Table. This is a table that you will create in your
> server database. The table will contain the field USERID
> and then any other field that you will use for a variable,
> in your case lets call it Mobile_Date.
>
> In the server Database Adapter there is a selection for
> you to point to this Systems Variables Table.
>
> You will then configure a Database Sync Action that will
> run before all the other Database Sync Actions and it
> will synchronize the Date from the mobile to this Systems
> Variables Table and it will put the date in the row that
> contains the correct USERID that is connecting. Each User
> that will be using the system will need to have their
> username put into the USERID field of this table. The
> sync action SQL query will be "Select * from
> SystemsVariablesTableName Where USERID = %OBUSERID%"
>
> The subsequent sync actions will now be able to use the
> date as a variable and you will be able to write a query
> like:
>
> "Select * from tableA Where DateField = %Mobile_Date%"
> Mobile_Date will then be replaced with whatever value is
> in the table for the currently connected user.
>
> This may not be completely clear, but hopefully it is what
> you are looking for. See the documentation for Systems
> Variable to get a better understanding, or call your
> local support team.
>
> Thanks
>
> Mark
>
>
> <Dan> wrote in message
> news:471e35b2.343c.1681692777@sybase.com... >I have
> > several Database Sync Actions that I would like to give
> > additional SQL Filter information. Specifically I want
> to give something more than then Device Id, User, etc.
> > How can I do this? For example, I may want to filter
> > the server's table down by a date specified by me on the
> > mobile client.
> >
> > I see where I can specify the SQL to execute during the
> > sync process (Sync Admin - Database Sync Action - Edit
> > Table Map) but is there a way I can specify this at
> > "sync time" or right before the call to
> > mobileObjectConnection.DoAction(...)?
>
>


Mark Wright Posted on 2007-11-05 17:30:28.0Z
From: "Mark Wright" <mark.wright@ianywhere.com>
Newsgroups: ianywhere.public.secureemail.trial
References: <471f8c1b@forums-1-dub> <472241b7.1c0f.1681692777@sybase.com>
Subject: Re: How to pass parameters to SQL Queries used as Filters...
Lines: 92
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: surfwc054.sybase.com
X-Original-NNTP-Posting-Host: surfwc054.sybase.com
Message-ID: <472f5334@forums-1-dub>
Date: 5 Nov 2007 09:30:28 -0800
X-Trace: forums-1-dub 1194283828 192.138.150.54 (5 Nov 2007 09:30:28 -0800)
X-Original-Trace: 5 Nov 2007 09:30:28 -0800, surfwc054.sybase.com
X-Authenticated-User: dfuser
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.secureemail.trial:217
Article PK: 11026

Dan,

Thank you for the suggestions, I will pass these along and will give you an
update as to the status when there is an update.

Thanks

Mark

<Dan> wrote in message news:472241b7.1c0f.1681692777@sybase.com...
> Makes perfect sense. Thanks.
>
> Might I suggest a feature enhancement as this approach is
> very expensive in bandwidth and setup? Good, here it is:
>
> Overload the DoAction() method to take a Map. The Map would
> be your typical Key/Value pair list. The DoAction call
> would then transfer these to the server for parameter
> substitution. This should increase performance by
> light-years as there would be one less sync, one less
> database table read, etc.
>
> This also makes your product incredibly powerful since you
> are allowing more dynamic SQL.
>
> Another suggestion would be to allow me to specify the SQL
> to use as a filter during DoAction.
>
> Thanks again.
>
>
>> There are different approaches to this question.
>> Currently we have no way of passing a variable to the
>> server during a sync session so you need to take an
>> approach of getting the variable to the server before the
>> next sync action occurs.
>>
>> The best way to do this is to you use a Systems Variables
>> Table. This is a table that you will create in your
>> server database. The table will contain the field USERID
>> and then any other field that you will use for a variable,
>> in your case lets call it Mobile_Date.
>>
>> In the server Database Adapter there is a selection for
>> you to point to this Systems Variables Table.
>>
>> You will then configure a Database Sync Action that will
>> run before all the other Database Sync Actions and it
>> will synchronize the Date from the mobile to this Systems
>> Variables Table and it will put the date in the row that
>> contains the correct USERID that is connecting. Each User
>> that will be using the system will need to have their
>> username put into the USERID field of this table. The
>> sync action SQL query will be "Select * from
>> SystemsVariablesTableName Where USERID = %OBUSERID%"
>>
>> The subsequent sync actions will now be able to use the
>> date as a variable and you will be able to write a query
>> like:
>>
>> "Select * from tableA Where DateField = %Mobile_Date%"
>> Mobile_Date will then be replaced with whatever value is
>> in the table for the currently connected user.
>>
>> This may not be completely clear, but hopefully it is what
>> you are looking for. See the documentation for Systems
>> Variable to get a better understanding, or call your
>> local support team.
>>
>> Thanks
>>
>> Mark
>>
>>
>> <Dan> wrote in message
>> news:471e35b2.343c.1681692777@sybase.com... >I have
>> > several Database Sync Actions that I would like to give
>> > additional SQL Filter information. Specifically I want
>> to give something more than then Device Id, User, etc.
>> > How can I do this? For example, I may want to filter
>> > the server's table down by a date specified by me on the
>> > mobile client.
>> >
>> > I see where I can specify the SQL to execute during the
>> > sync process (Sync Admin - Database Sync Action - Edit
>> > Table Map) but is there a way I can specify this at
>> > "sync time" or right before the call to
>> > mobileObjectConnection.DoAction(...)?
>>
>>