|
Home > Archive > SQL Anywhere Mobile > August 2005 > Modify_user apparently doesn't, in Oracle
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Modify_user apparently doesn't, in Oracle
|
|
| Breck Carter [TeamSybase] 2005-08-17, 9:31 am |
| How do I get a modify_user script call to an Oracle stored procedure
to actually *change* the current user name?
It produces this message, and carries on using the old user name:
The user name "must be changed fb31db02-eaf6-4654-b325-f0e64a800a6a"
has been modified by the Modify_User script; now the user name is
"must be changed fb31db02-eaf6-4654-b325-f0e64a800a6a".
SQL Anywhere 9.0.2.3124
Oracle9i Release 9.2.0.1.0
iAnywhere Solutions 9 - Oracle Wire Protocol 4.20.00.81
The procedure works OK when tested directly; i.e., a call in SQL*Plus
proves that the modified value is being returned OK via the paramter.
Here is the script and procedure definition:
=====
CALL &MOBILINK_OWNER.. ml_add_connection_sc
ript ( 'v1', 'modify_user',
'
CALL &MOBILINK_OWNER..ml_modify_user ( ? )
' );
CREATE OR REPLACE PROCEDURE &MOBILINK_OWNER..ml_modify_user (
v_ml_username IN OUT VARCHAR2 )
IS
v_id NUMBER;
BEGIN
-- This event fires as part of the user authentication, before
modify last_download_timest
amp,
-- and before begin_synchronizatio
n.
-- 1234567890123456
IF SUBSTR ( v_ml_username, 1, 16 ) = 'must be changed ' THEN
-- Generate a new database id, save it, and assign it as the new
MobiLink user name.
SELECT &MOBILINK_OWNER.. MOBILE_DATABASE_ID_S
EQ.NEXTVAL
INTO v_id
FROM DUAL;
INSERT INTO &MOBILINK_OWNER..MOBILE_DATABASE_ID ( ID ) VALUES (
v_id );
-- SELECT * FROM ML_VMS.MOBILE_DATABASE_ID;
v_ml_username := TO_CHAR ( v_id );
END IF;
END; -- ml_modify_user
/
=====
Here is the MobiLink server log:
=====
I. 08/16 18:59:47. Adaptive Server Anywhere MobiLink Version
9.0.2.3124
I. 08/16 18:59:47.
I. 08/16 18:59:47. Copyright © 1989-2004 Sybase, Inc.
Portions Copyright © 2002-2004, iAnywhere
Solutions, Inc.
I. 08/16 18:59:47. All rights reserved. All unpublished rights
reserved.
I. 08/16 18:59:47.
I. 08/16 18:59:47. This software contains confidential and trade
secret information of
I. 08/16 18:59:47. iAnywhere Solutions, Inc.
Use, duplication or disclosure of the software and
documentation
I. 08/16 18:59:47. by the U.S. Government is subject to restrictions
set forth in a license
I. 08/16 18:59:47. agreement between the Government and iAnywhere
Solutions, Inc. or
I. 08/16 18:59:47. other written agreement specifying the Government's
rights to use the
I. 08/16 18:59:47. software and any applicable FAR provisions, for
example, FAR 52.227-19.
I. 08/16 18:59:47.
I. 08/16 18:59:47. iAnywhere Solutions, Inc., One Sybase Drive,
Dublin, CA 94568, USA
I. 08/16 18:59:47.
I. 08/16 18:59:47. This product is not licensed.
I. 08/16 18:59:47. <Main>: MobiLink server started
I. 08/16 18:59:47. <Main>: Option 1: -c
I. 08/16 18:59:47. <Main>: Option 2:
DSN=ORA_PAVILION;UID
=ML_VMS;pwd=********
I. 08/16 18:59:47. <Main>: Option 3: -v
I. 08/16 18:59:47. <Main>: Option 4: cfhnpstu
I. 08/16 18:59:47. <Main>: Option 5: -zu+
I. 08/16 18:59:47. <Main>: Verbose logging: show script names when
invoked
I. 08/16 18:59:47. <Main>: Verbose logging: show script contents when
invoked
I. 08/16 18:59:47. <Main>: Verbose logging: show schema for each table
I. 08/16 18:59:47. <Main>: Verbose logging: show an error when the
first read of a synchronization fails
I. 08/16 18:59:47. <Main>: Verbose logging: show translated SQL for
prepared statements
I. 08/16 18:59:47. <Main>: Verbose logging: show rowcount values
I. 08/16 18:59:47. <Main>: Individual database connections will be
closed after synchronization errors.
I. 08/16 18:59:47. <Main>: BLOB cache size: 524288
I. 08/16 18:59:47. <Main>: Maximum number of BLOB bytes to compare:
4294967295
I. 08/16 18:59:47. <Main>: Maximum number of database connections: 6
I. 08/16 18:59:47. <Main>: Maximum number of deadlock retries: 10
I. 08/16 18:59:47. <Main>: Timeout for inactive database connections:
60 minutes
I. 08/16 18:59:47. <Main>: Maximum delay between retries after
deadlock: 30 seconds
I. 08/16 18:59:47. <Main>: Rowset size: 10
I. 08/16 18:59:47. <Main>: Upload cache size: 524288 bytes
I. 08/16 18:59:47. <Main>: Download cache memory size: 1048576 bytes
I. 08/16 18:59:47. <Main>: Download cache directory size: 10485760
bytes
I. 08/16 18:59:47. <Main>: Number of worker threads: 5
I. 08/16 18:59:47. <Main>: Maximum number of threads uploading
concurrently: 5
W. 08/16 18:59:47. <Main>: Warning: Unknown users will be added
automatically (when there is no authenticate_user script).
I. 08/16 18:59:47. <Main>: Example scripts will not be automatically
generated for unknown versions.
I. 08/16 18:59:47. <Main>: Local file for remote synchronization logs:
'dbmlsrv.mle'
I. 08/16 18:59:47. <Main>: Stream specifier 1: TCPIP
W. 08/16 18:59:47. <Main>: Warning: [10073] Windows XP Firewall is
enabled, and the MobiLink synchronization server is not in the
exception list. Clients on remote machines may not be able to connect
to this server.
I. 08/16 18:59:47. <Main>: ODBC DBMS Name: Oracle
I. 08/16 18:59:47. <Main>: ODBC DBMS Version: 09.02.0000 Oracle
9.2.0.1.0
I. 08/16 18:59:47. <Main>: ODBC DBMS Driver Version: 04.20.0081
(B0067, U0062)
E. 08/16 18:59:47. <Main>: Error: [-10002] ODBC: [DataDirect][ODBC
Oracle Wire Protocol driver][Oracle]ORA-00942: table or view does not
exist (ODBC State = 42S02, Native error code = 942)
W. 08/16 18:59:47. <Main>: Warning: [10075] Unable to access ORACLE
table 'v_$transaction'. Access is needed in order to ensure no data
is missed if using timestamp-based downloads
I. 08/16 18:59:47. <Main>: ODBC Version supported by the driver: 3.52
I. 08/16 18:59:47. <Main>: ODBC isolation set to: Read Committed
I. 08/16 18:59:47. <Main>: Connected
I. 08/16 18:59:48. <Main>: Translated SQL:
SELECT COUNT(*) FROM ml_scripts_modified
I. 08/16 18:59:48. <thread 1.1>: Ready to handle requests
I. 08/16 18:59:48. <thread 1.3>: Ready to handle requests
I. 08/16 18:59:48. <thread 1.2>: Ready to handle requests
I. 08/16 18:59:48. <thread 1.4>: Ready to handle requests
I. 08/16 18:59:48. <thread 1.5>: Ready to handle requests
I. 08/16 19:04:09. <thread 1.5>: Working on a request
I. 08/16 19:04:09. <thread 1.5>: Translated SQL:
SELECT last_modified FROM ml_scripts_modified
I. 08/16 19:04:09. <thread 1.5>: ml_scripts_modified last modified at:
2005-08-16 17:45:40
I. 08/16 19:04:09. <thread 1.5>: Translated SQL:
SELECT version_id FROM ml_script_version WHERE name
= ?
I. 08/16 19:04:09. <thread 1.5>: ASA synchronization request from:
must be changed fb31db02-eaf6-4654-b325-f0e64a800a6a (version: v1)
E. 08/16 19:04:09. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Error: [-10002] ODBC:
[DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00942: table
or view does not exist (ODBC State = 42S02, Native error code = 942)
W. 08/16 19:04:09. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Warning: [10075] Unable to
access ORACLE table 'v_$transaction'. Access is needed in order to
ensure no data is missed if using timestamp-based downloads
I. 08/16 19:04:09. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: ODBC isolation set to: Read
Committed
I. 08/16 19:04:09. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Translated SQL:
SELECT t1.event, t2.script_language, t2.script FROM
ml_connection_script
t1, ml_script t2 WHERE t1.version_id = ? AND
t1.script_id = t2.script_id
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: begin_connection <connection>
(no script)
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: COMMIT Transaction:
begin_connection
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Translated SQL:
SELECT hashed_password FROM ml_user WHERE name = ?
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: User name 'must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a' not found in the ml_user table,
inserting a new entry
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Cached ODBC statement:
SELECT hashed_password FROM ml_user WHERE name = ?
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Translated SQL:
SELECT MAX( user_id ) + 1 FROM ml_user
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Translated SQL:
INSERT INTO ml_user
(user_id,name,commit
_state,hashed_passwo
rd) VALUES(?,?,?,?)
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Translated SQL:
SELECT user_id FROM ml_user WHERE name = ?
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: modify_user <connection>
CALL ML_VMS.ml_modify_user ( ? )
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: Translated SQL:
CALL ML_VMS.ml_modify_user ( ? )
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: The user name "must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a" has been modified by the
Modify_User script; now the user name is "must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a".
I. 08/16 19:04:10. <1.5> [must be changed
fb31db02-eaf6-4654-b325-f0e64a800a6a]: COMMIT Transaction:
authenticate_user
=====
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| David Fishburn 2005-08-17, 1:24 pm |
| "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:7b86g1hjtc947rm
dtlknd5nn9f92rcs7he@
4ax.com of
sybase.public.sqlanywhere.mobilink:
Can you give us an idea of why you are doing this.
--
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
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
| |
| Breck Carter [TeamSybase] 2005-08-17, 1:24 pm |
| On 17 Aug 2005 09:33:19 -0700, David Fishburn
<fishburn_spam@off.ianywhere.com> wrote:
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> news:7b86g1hjtc947rm
dtlknd5nn9f92rcs7he@
4ax.com of
>sybase.public.sqlanywhere.mobilink:
>
>Can you give us an idea of why you are doing this.
Because the MobiLink user name / global database id is supposed to be
automatically assigned 1, 2, 3 with no user input or customization of
the remote database. A random value (GUID) is calculated on the remote
for the first sync, and then changed to the real value on the
consolidated.
Back in 2003 this worked fine on an ASA consolidated...
I. 08/31 14:47:17. Adaptive Server Anywhere MobiLink Version
8.0.2.4122
....
I. 08/31 14:47:24. <1.4> [must be changed
84746bcf-2e3d-42e2-a9b3-7b213da2d80d]: The user name "must be changed
84746bcf-2e3d-42e2-a9b3-7b213da2d80d" has been modified by the
Modify_User script; now the user name is "1".
Now it doesn't seem to work with Oracle...
The user name "must be changed fb31db02-eaf6-4654-b325-f0e64a800a6a"
has been modified by the Modify_User script; now the user name is
"must be changed fb31db02-eaf6-4654-b325-f0e64a800a6a".
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2005-08-19, 8:26 pm |
| Breck, the modify_user functionality is tested every time we run our test
suites against Oracle, and it seems to work great for us.
I. 08/19 16:24:41. <1.5> & #91;modify_user_sync
_site_1]: modify_user <connection>
{ call modify_user_Modify_u
ser ( ? ) }
I. 08/19 16:24:41. <1.5> & #91;modify_user_sync
_site_1]: Translated SQL:
BEGIN modify_user_Modify_u
ser ( ? ); END;
####################
####################
##################
I. 08/19 16:24:41. <1.5> & #91;modify_user_sync
_site_1]: The user name
" modify_user_sync_sit
e_1" has been modified by the Modify_User script; now
the user name is "1004".
####################
####################
##################
I. 08/19 16:24:41. <1.5> & #91;modify_user_sync
_site_1]: COMMIT Transaction:
authenticate_user
I haven't had time to try to reproduce your issue (I'm been busy with my
TechWave talks), but I did notice some differences between how our tests run
and how you call the stored procedure.
1) Inside the stored procedure we use a SELECT ... INTO, instead of the
assignment ( v_ml_username := TO_CHAR ( v_id ) )
2) Our ml_add_connection_sc
ript command uses the ODBC convention for stored
procedure calls
{ call modify_user_Modify_u
ser ( ? ) }
but you use just the call command. Note that when Oracle translates the
ODBC standard, it ends up as
BEGIN modify_user_Modify_u
ser ( ? ); END;
which is a very different syntax from your call. I suspect using that using
the ODBC standard for stored procedure calling will resolve the problem
you're seeing.
--
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/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:9kr6g1lftkag5mf
msongdmpgl53f5nkhi8@
4ax.com...
> On 17 Aug 2005 09:33:19 -0700, David Fishburn
> <fishburn_spam@off.ianywhere.com> wrote:
>
>
> Because the MobiLink user name / global database id is supposed to be
> automatically assigned 1, 2, 3 with no user input or customization of
> the remote database. A random value (GUID) is calculated on the remote
> for the first sync, and then changed to the real value on the
> consolidated.
>
> Back in 2003 this worked fine on an ASA consolidated...
>
> I. 08/31 14:47:17. Adaptive Server Anywhere MobiLink Version
> 8.0.2.4122
> ...
> I. 08/31 14:47:24. <1.4> [must be changed
> 84746bcf-2e3d-42e2-a9b3-7b213da2d80d]: The user name "must be changed
> 84746bcf-2e3d-42e2-a9b3-7b213da2d80d" has been modified by the
> Modify_User script; now the user name is "1".
>
> Now it doesn't seem to work with Oracle...
>
> The user name "must be changed fb31db02-eaf6-4654-b325-f0e64a800a6a"
> has been modified by the Modify_User script; now the user name is
> "must be changed fb31db02-eaf6-4654-b325-f0e64a800a6a".
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
|
|
|
|
|