Hi,
We are using the ASA 9.0.1.1899 version with .Net and
Synchronization with Oracle. Sync. process happening well.
Now i just want to validate my key value for which i am
going to fetch the data from the Oracle server is valid or
not. I added -ap "Keyvalue,Version" of the application. I
added authenticate_parameters script with the following
Procedure.
I have not written any authenticate_user or hashed script. i
just need to validate that table if that table has records,
user can continue with sync. otherwise no need of procedding
with sync. Now it is not returning auth_status as 3500,
instead it is returning 1000. Please tell me the problem in
my process and please tell me how will i handle the
auth_status in the frontend. since i want to show to my user
that this keyvalue is invalid.
Regards,
David.


create or replace procedure SpACNumVerify (auth_status in
out integer,MLUName varchar2,Acnum varchar2,EFBVersion
Varchar2)
as
RCount Integer;
begin
Insert Into authtest (Auth_Stat,UserId,AcRegno,Version)
values (auth_status,MLUName,Acnum,EFBVersion);
SELECT count(*) into RCount FROM
EFBC_CfbAcDtl_aircraft_dtl where
UPPER(CfbAcDtl_aircraft_reg_no) = UPPER (Acnum);
if RCount > 0 then
Select Count(*) into RCount From
EFBC_Aircraft_EFBVersion Where UPPER(CFBACV_Acnum) = UPPER
(Acnum);
if RCount = 0 then
Insert into EFBC_Aircraft_EFBVersion
(CFBACV_Acnum,CFBACV_AppType,CFBACV_Version) Values
(Acnum,'EFB',EFBVersion);
else
Update EFBC_Aircraft_EFBVersion Set
CFBACV_Version = EFBVersion Where UPPER(CFBACV_Acnum) =
UPPER (Acnum);
end if;
auth_status :=1000;
else
auth_status :=3500;
end if;
end SpACNumVerify;

I don't see anything obviously wrong with your stored procedure, and a very
simple stored proc I wrote works like a charm :

CREATE OR REPLACE PROCEDURE AuthParm (
action OUT integer,
user_name IN varchar2,
p1 IN varchar2,
p2 IN varchar2 )
AS
BEGIN
action := 3500;
IF p1 = p2 THEN
action := 1000;
END IF;
END;
/

call ml_add_connection_script( 'v1', 'authenticate_parameters', '{call
AuthParm(?,?,?,?)}' )
/

I'd suggest modifying your SP a little to see the value of RCount after your
first select statement.

Old :

New :

SELECT count(*) into RCount
FROM EFBC_CfbAcDtl_aircraft_dtl
where UPPER(CfbAcDtl_aircraft_reg_no) = UPPER (Acnum);
Insert Into authtest (Auth_Stat,UserId,AcRegno,Version, CurCount)
values (auth_status,MLUName,Acnum,EFBVersion, RCount);

Verify that your select statement is really returning the number of rows you
are expecting.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"


<David> wrote

David wrote in news:43f325bc.78bc.1681692777 (AT) sybase (DOT) com
of sybase.public.sqlanywhere.mobilink:

D> We are using the ASA 9.0.1.1899 version with .Net and
....
D> I have not written any authenticate_user or hashed script. i
D> just need to validate that table if that table has records,
D> user can continue with sync. otherwise no need of procedding
D> with sync. Now it is not returning auth_status as 3500,
D> instead it is returning 1000. Please tell me the problem in
D> my process and please tell me how will i handle the
D> auth_status in the frontend. since i want to show to my user
D> that this keyvalue is invalid.

Well the code you have supplied below can return 1000 or 3500. Have you
verified the logic in your stored procedure?

D> create or replace procedure SpACNumVerify (auth_status in
D> out integer,MLUName varchar2,Acnum varchar2,EFBVersion
D> Varchar2)
D> as
D> RCount Integer;
D> begin
D> Insert Into authtest (Auth_Stat,UserId,AcRegno,Version)
D> values (auth_status,MLUName,Acnum,EFBVersion);
D> SELECT count(*) into RCount FROM
D> EFBC_CfbAcDtl_aircraft_dtl where
D> UPPER(CfbAcDtl_aircraft_reg_no) = UPPER (Acnum);
D> if RCount > 0 then
D> Select Count(*) into RCount From
D> EFBC_Aircraft_EFBVersion Where UPPER(CFBACV_Acnum) = UPPER
D> (Acnum);
D> if RCount = 0 then
D> Insert into EFBC_Aircraft_EFBVersion
D> (CFBACV_Acnum,CFBACV_AppType,CFBACV_Version) Values
D> (Acnum,'EFB',EFBVersion);
D> else
D> Update EFBC_Aircraft_EFBVersion Set
D> CFBACV_Version = EFBVersion Where UPPER(CFBACV_Acnum) =
D> UPPER (Acnum);
D> end if;
D> auth_status :=1000;
D> else
D> auth_status :=3500;
D> end if;
D> end SpACNumVerify;
D>


Besides that, you should always provide the ML server output.
I would run ML using these additional flags: -vcrsn -ot ml.txt and then
post ml.txt if you are still have difficulties.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
http://ianywhere.codexchange.sybase....ctDocumentList

David wrote in news:43f325bc.78bc.1681692777 (AT) sybase (DOT) com
of sybase.public.sqlanywhere.mobilink:

D> We are using the ASA 9.0.1.1899 version with .Net and
D> Synchronization with Oracle. Sync. process happening well.
D> Now i just want to validate my key value for which i am
D> going to fetch the data from the Oracle server is valid or
D> not. I added -ap "Keyvalue,Version" of the application. I
D> added authenticate_parameters script with the following
D> Procedure.
D> I have not written any authenticate_user or hashed script. i
D> just need to validate that table if that table has records,
D> user can continue with sync. otherwise no need of procedding
D> with sync. Now it is not returning auth_status as 3500,
D> instead it is returning 1000. Please tell me the problem in
D> my process and please tell me how will i handle the
D> auth_status in the frontend. since i want to show to my user
D> that this keyvalue is invalid.
D> Regards,
D> David.

Well, based on the logic in your stored procedure, this query is
returning a count > 0:

SELECT count(*)
into RCount
FROM EFBC_CfbAcDtl_aircraft_dtl
where UPPER(CfbAcDtl_aircraft_reg_no) = UPPER (Acnum);

You must verify outside of MobiLink what Oracle is returning from this
query.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
http://ianywhere.codexchange.sybase....ctDocumentList

Hi David,
Thanks for the response. I tried to test the procedure in
the oracle it is working correctly. I mean if you pass the
invalid key value it is returning 3500 or for a valid key it
is returning 1000.
What shall I do now. is this necessary to authenticate_user
or Hashed event also to implement this logic. Kindly provide
me the solution for this issue.
Regards,
David

David wrote in news:440410b4.667e.1681692777 (AT) sybase (DOT) com
of sybase.public.sqlanywhere.mobilink:

D> Thanks for the response. I tried to test the procedure in
D> the oracle it is working correctly. I mean if you pass the
D> invalid key value it is returning 3500 or for a valid key it
D> is returning 1000.
D> What shall I do now. is this necessary to authenticate_user
D> or Hashed event also to implement this logic. Kindly provide
D> me the solution for this issue.

What parameters is ML passing to this stored procedure?
You need to turn on some debugging to ensure the procedure is doing what
you want.

If this was an ASA database, I would add:
MESSAGE 'Hello, here at step 1 using userid:'+string(UserId);

And pepper my code with those. Then in the minimized engine window I
would see what was being called.

Oracle has similar features, but I don't know the syntax off hand. You
can turn the same thing on to verify.

Did you debug the stored procedure while MobiLink was executing it?


Your procedure looked fine, lets try changing how you are calling it:
EXEC ml_add_connection_script( '3.0.1.0', 'authenticate_user', -
'BEGIN SpACNumVerify(?,?,?,?); END;' )


--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
http://ianywhere.codexchange.sybase....ctDocumentList

Dear David,
Thanks a lot for your support. Now it is working fine. It
helped me lot and at the correct time. Can you tell me
something about handing this in the front end. i.e i just
want to throw a user defined message as Pop up message box
instead of "User Authentication Value is 3500". coz this
error message definitly confuse our end user. We are using
the DBMLSync.exe provided for .Net front end application.
Again thanks a lot for your help. can you explain me how it
is working now if possible.
Regards,
David

David wrote in news:4405b8d6.7a49.1681692777 (AT) sybase (DOT) com
of sybase.public.sqlanywhere.mobilink:


Now that you are over this issue, create a new thread and restate what
you want to happen and when.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
http://ianywhere.codexchange.sybase....ctDocumentList