Old 20th February 2007, 15:17   #1
internetfreakz
Junior Member
 
Join Date: Feb 2007
Posts: 11
Oracle: Detect if user exist or not

Hello,

Is there some code (for an nsi script) to detect if a user already exist in an oracle database when i give the username and a database?

Who can I implement that?
internetfreakz is offline   Reply With Quote
Old 20th February 2007, 15:44   #2
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
Save the following script to checkuserexists.sql:
code:

SET VERIFY OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE
VARIABLE NUM NUMBER;
BEGIN
BEGIN
SELECT COUNT(*) INTO :NUM FROM ALL_USERS WHERE USERNAME=UPPER('&1');
EXCEPTION WHEN OTHERS THEN
:NUM := SQLCODE;
END;
END;
/
EXIT :NUM



You can run it as follows:
code:

SetOutPath $TEMP
File checkuserexists.sql
Push $1
nsExec::Exec "sqlplus.exe -S sys/manager@orcl @checkuserexists.sql SOMEUSER"
Pop $1
IntCmp $1 0 not_exist oracleerror
IntCmp $1 1 0 0 oracleerror
MessageBox MB_OK "User exists"
goto done
not_exist:
MessageBox MB_OK "User doesn't exist"
goto done
oracleerror:
MessageBox MB_OK|MB_ICONSTOP "Oracle Error: $1"
done:
Pop $1



Replace sqlplus.exe with the correct path, and SOMEUSER with the user name.
Replace sys/manager@orcl with the correct oracle login information- this should ideally be SYSTEM.

Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Old 21st February 2007, 00:23   #3
internetfreakz
Junior Member
 
Join Date: Feb 2007
Posts: 11
Thanks for your answer but is it possible there's something wrong? When I run the script, I get always the message "user exists", even if I give a user that's not in my database
internetfreakz is offline   Reply With Quote
Old 21st February 2007, 00:36   #4
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
Works for me. Attach your script.

Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Old 21st February 2007, 02:49   #5
internetfreakz
Junior Member
 
Join Date: Feb 2007
Posts: 11
Hellow,

My complete nsi script is in attach.

First, the function for the custom page 'Oracle' will be executed, then the function for the custom page 'sql'.

After that, the wizard starts to execute my first section 'pisad'.

In the next section 'database', i will check if a given user (in the custom page 'sql') is already exists or not.

There, I get everytime 'user exist'.

Thanks for your response!
Attached Files
File Type: txt installer.txt (12.6 KB, 388 views)
internetfreakz is offline   Reply With Quote
Old 21st February 2007, 04:10   #6
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
It appears to me that checkuserexists.sql is not being extracted to $TEMP properly.
This code looks suspicious:
code:

File /r "sql"

; De locatie van sqlplus bepalen
StrCpy $0 "$oradir\BIN\sqlplus.exe"

GetFullPathName /SHORT $6 $INSTDIR
StrCpy $4 "$6\sql\checkuserexists.sql"

SetOutPath $TEMP
File checkuserexists.sql
Push $1
nsExec::Exec "$0 -S system/$db_passsystem@XE @checkuserexists.sql $db_user"



Is checkuserexists.sql in the same directory as the script or is it in the sql subdirectory?

Perhaps it should be:
code:

File /r "sql"

; De locatie van sqlplus bepalen
StrCpy $0 "$oradir\BIN\sqlplus.exe"

GetFullPathName /SHORT $6 $INSTDIR
StrCpy $4 "$6\sql\checkuserexists.sql"

Push $1
nsExec::Exec "$0 -S system/$db_passsystem@XE @$4 $db_user"


Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Old 21st February 2007, 08:45   #7
internetfreakz
Junior Member
 
Join Date: Feb 2007
Posts: 11
I changed my database section to the following but the problem is not solved with that

Section "Database" Database
MessageBox MB_OK "Database sectie uitvoeren - Eerst handmatig de gebruiker nog aanmaken!"

SetOutPath "$INSTDIR"
File /r "sql"

; De locatie van sqlplus bepalen
StrCpy $0 "$oradir\BIN\sqlplus.exe"

GetFullPathName /SHORT $6 $INSTDIR
StrCpy $4 "$6\sql\checkuserexists.sql"

Push $1
nsExec::Exec "$0 -S system/$db_passsystem@XE @$4 $db_user"
Pop $1
IntCmp $1 0 not_exist oracleerror
IntCmp $1 1 0 0 oracleerror
MessageBox MB_OK "User exists"
goto done
not_exist:
MessageBox MB_OK "User doesn't exist"
goto done
oracleerror:
MessageBox MB_OK|MB_ICONSTOP "Oracle Error: $1"
done:
Pop $1

;GetFullPathName /SHORT $6 $INSTDIR

; Na eventueel die twee scripts te hebben uitgevoerd moeten er ook nog scripts voor de
; applicatie van PISAD worden geïnstalleerd.

GetFullPathName /SHORT $6 $INSTDIR
StrCpy $4 "$6\sql\opvullen_tab.sql"
nsExec::ExecToLog '$0 $db_user/$db_passuser@XE @"$4"'
internetfreakz is offline   Reply With Quote
Old 21st February 2007, 13:06   #8
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
Attach your sql.ini file.

Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Old 21st February 2007, 13:16   #9
internetfreakz
Junior Member
 
Join Date: Feb 2007
Posts: 11
I have rename the file to .txt just to upload it
Attached Files
File Type: txt sql.txt (1.0 KB, 295 views)
internetfreakz is offline   Reply With Quote
Old 21st February 2007, 13:19   #10
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
FYI, using the registry to find the Oracle home is not reliable starting from Oracle 10g. You should use C:\Program Files\Oracle\Inventory\ContentsXML\inventory.xml.

Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Old 21st February 2007, 13:27   #11
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
I don't know what to tell you - it works for me with your sql.ini.
Here's something to try:
Before actually executing the sql, pop up a messagebox with the command string:
code:

MessageBox MB_OK "$0 -S system/$db_passsystem@XE @$4 $db_user"



Then execute that exact command from the DOS prompt, followed by:
code:

echo %ERRORLEVEL%



The ERRORLEVEL should show you the return code from the sql.
If the user exists, you will see 1 otherwise you will see 0.

Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Old 21st February 2007, 15:15   #12
internetfreakz
Junior Member
 
Join Date: Feb 2007
Posts: 11
Hello,

The messagebox gives me the command that we expect but the echo gives me a value 0

When I do the query at the sql prompt, I get the correct value (1) ...

There is a 'time out' of 1 to 2 minutes between the messagebox and the echo, is this normal?

And is there a simple script to extract the Oracle dir from the file C:\Program Files\Oracle\Inventory\ContentsXML\inventory.xml ?
internetfreakz is offline   Reply With Quote
Old 21st February 2007, 16:44   #13
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
Quote:
Originally posted by internetfreakz
Hello,

The messagebox gives me the command that we expect but the echo gives me a value 0

When I do the query at the sql prompt, I get the correct value (1) ...
Strange. I don't see this problem. Maybe it is some nsExec problem. Try executing it using Exec.

Quote:
There is a 'time out' of 1 to 2 minutes between the messagebox and the echo, is this normal?
No, it should not take that long.

Quote:
And is there a simple script to extract the Oracle dir from the file C:\Program Files\Oracle\Inventory\ContentsXML\inventory.xml ?
You can probably parse it using the xml plugin.
Extract the home(s) from the xml file and then retrieve the directory. There may be multiple homes if the user has installed multiple versions of Oracle.

Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Old 21st February 2007, 17:50   #14
internetfreakz
Junior Member
 
Join Date: Feb 2007
Posts: 11
Quote:
Originally posted by iceman_k
Strange. I don't see this problem. Maybe it is some nsExec problem. Try executing it using Exec.


Same problem

Quote:
No, it should not take that long.
Some problem with de db? But what kind of problem, any idea?

Quote:
You can probably parse it using the xml plugin.
Extract the home(s) from the xml file and then retrieve the directory. There may be multiple homes if the user has installed multiple versions of Oracle.
[/quote]

Hmm, that's new for me but I take a look at that in a few hours

One question yes: How can I find the xml file when the user installs his Oracle software in an other directory than the default? (That was the reason why I look in the register...)
internetfreakz is offline   Reply With Quote
Old 21st February 2007, 18:49   #15
iceman_k
NSIS Dev
 
iceman_k's Avatar
 
Join Date: Feb 2003
Location: Boston, MA, U.S.A.
Posts: 455
ReadRegStr HKLM Software\Oracle inst_loc

Cheers,
Iceman_K

EclipseNSIS - An NSIS IDE for the Eclipse Platform | My contributions to the wiki
iceman_k is offline   Reply With Quote
Reply
Go Back   Winamp Forums > Developer Center > NSIS Discussion

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump