#202311 - 2011-05-23 10:01 PM
Re: Get Database Name
[Re: Dr_Rick]
|
NTDOC
Administrator
   
Registered: 2000-07-28
Posts: 11628
Loc: CA
|
Hi Rick,
Please see the following UDF list which contains User Defined Functions to work with databases.
http://www.kixtart.org/udf/
Here are some of the listed ones and there may be others.
DBCommand DBConnClose DBConnOpen DBExecuteSQL DBGetRecordset DBRecordsetClose DBRecordsetOpen MySQLODBC MySQLODBC2 ODBCACCESS ODBCORACLE ODBCSQL ODBCVFP
|
|
Top
|
|
|
|
#202312 - 2011-05-23 10:23 PM
Re: Get Database Name
[Re: NTDOC]
|
ShaneEP
MM club member
   
Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK
|
To further elaborate on Doc's suggestion...If the info does indeed reside in the table/field you say...This should work (just change the DBQ portion to the path of the mdb).
$DBName = Join(DBCommand("DRIVER={Microsoft Access Driver (*.mdb)};DBQ="+@ScriptDir+"\db1.mdb","SELECT DataBaseName FROM tblDataBaseInfo"),"")
? $DBName
get $
function DBCommand($ConnDSN,$sql)
Dim $objConn, $adStateOpen
dim $Conntimeout, $CmdTimeout
dim $cmdCommand, $rsRecordset
dim $Records, $FinalRecords
dim $adCmdText, $adLockReadOnly, $adOpenStatic
dim $row, $rows, $column, $columns
$ConnDSN=trim($ConnDSN)
if not $ConnDSN
exit 87
endif
$sql=trim($sql)
if not $sql
exit 87
endif
; default database parameters
$adStateOpen=1
$ConnTimeout=15
$CmdTimeout=30
$adCmdText = 1
$adOpenStatic = 3
$adLockReadOnly = 1
; open the database connection
$objConn = CreateObject("ADODB.Connection")
if @ERROR
exit @ERROR
endif
$objConn.ConnectionTimeout = $ConnTimeout
if @ERROR
exit @ERROR
endif
$objConn.CommandTimeout = $CmdTimeout
if @ERROR
exit @ERROR
endif
$objConn.Open($ConnDSN)
if @ERROR
exit @ERROR
endif
if not $objConn.State=$adStateOpen
$objConn=''
$DBCommand=''
exit @ERROR
endif
; create the database command object
$cmdCommand = CreateObject('ADODB.Command')
if @ERROR
exit @ERROR
endif
$cmdCommand.ActiveConnection = $objConn
if @ERROR
exit @ERROR
endif
$cmdCommand.CommandType = $adCmdText
if @ERROR
exit @ERROR
endif
$cmdCommand.CommandText = $sql
if @ERROR
$DBCommand=@ERROR
exit @ERROR
endif
if instr($sql,'SELECT')=1
; create the recordset object
$rsRecordSet = CreateObject('ADODB.Recordset')
if @ERROR
exit @ERROR
endif
$rsRecordset.CursorType = $adOpenStatic
if @ERROR
exit @ERROR
endif
$rsRecordset.LockType = $adLockReadOnly
if @ERROR
exit @ERROR
endif
$rsRecordset.Open($cmdCommand)
if @ERROR
exit @ERROR
endif
if $rsRecordset.EOF and $rsRecordSet.BOF
; recordset is empty
$FinalRecords=''
else
if @ERROR
exit @ERROR
endif
; retrieve all records at once and transpose into tabular format
$Records = $rsRecordset.GetRows()
$columns=ubound($records,1)
$rows=ubound($records,2)
redim $FinalRecords[$rows,$columns]
for $row=0 to $rows
for $column=0 to $columns
$FinalRecords[$row,$column]=$records[$column,$row]
next
next
endif
; close recordset
if $rsRecordset.state=$adStateOpen
$rsRecordset.Close()
if @ERROR
exit @ERROR
endif
endif
$rsRecordset=''
$cmdCommand=''
$DBCommand=$FinalRecords
else
$rsRecordset=$cmdCommand.Execute()
$cmdCommand=''
$rsRecordset=''
if @ERROR
exit @ERROR
endif
$DBCommand=0
endif
; close the database connection
If $objConn.State = $adStateOpen
$objConn.Close()
if @ERROR
exit @ERROR
endif
EndIf
$objConn=''
exit 0
endfunction
|
|
Top
|
|
|
|
#202313 - 2011-05-23 10:33 PM
Re: Get Database Name
[Re: ShaneEP]
|
ShaneEP
MM club member
   
Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK
|
|
|
Top
|
|
|
|
#202319 - 2011-05-24 04:12 AM
Re: Get Database Name
[Re: Dr_Rick]
|
ShaneEP
MM club member
   
Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK
|
Glad the example worked for you. And I had actually mentioned the extended file properties as perhaps a simple way of getting the last access date/time.
$lastaccess = GetExtFileProperties("C:\path\to\mdb\file.mdb", "Date Accessed")
function GetExtFileProperties($FQFN, $attribute)
dim $objShell, $objFolder,$i,$found
if exist($FQFN)
$objShell=CreateObject("Shell.Application")
$objFolder=$objShell.Namespace(left($FQFN,instrrev($FQFN,"\")))
if $objFolder
if vartypename($attribute)="string"
While $i<267 and $found=0
if $attribute=$objFolder.GetDetailsOf($objFolder.Items, $i)
$attribute=$i
$found=1
endif
$i=$i+1
loop
endif
if vartypename($attribute)="long" ; Number
$GetExtFileProperties=$objFolder.GetDetailsOf($objFolder.ParseName(right($FQFN,len($FQFN)-instrrev($FQFN,"\"))),$attribute)
else
exit -1
endif
else
exit @error
endif
else
exit 2
endif
endfunction
|
|
Top
|
|
|
|
#202339 - 2011-05-26 04:03 AM
Re: Get Database Name
[Re: Dr_Rick]
|
ShaneEP
MM club member
   
Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK
|
Hey Rick...Nice simple script! Just a few changes I would recommend from a quick look through...
I would get rid of the console related code at the beginning, along with a few $nul='s, to avoid the console popping open for a second.
Break On
$nul = SetOption("HideCursor", "ON")
$nul = SetOption("DisableDebugging", "ON")
$nul = SetOption("NoVarsInStrings", "OFF")
$nul = SetOption("Explicit", "OFF")
I noticed there was a FOR EACH loop in the CheckInstance() function...but no corresponding NEXT.
In the InstKixForms() function, there is an unneeded ELSE...just change the IF line to
If Not Exist("%windir%\system32\Kixforms.Dll") and delete the ELSE.
|
|
Top
|
|
|
|
#202350 - 2011-05-26 03:50 PM
Re: Get Database Name
[Re: Dr_Rick]
|
Glenn Barnas
KiX Supporter
   
Registered: 2003-01-28
Posts: 4401
Loc: New Jersey
|
Just a few additional comments..
Defining "C:\Program Files\...' is not a good practice - use the appropriate environment var instead. This may work on THIS installation, but what if they install Citrix, Term Services, or decide to install apps to a different drive letter? Even better would be to implement the optional config file - if it is there, read the program location. If it is defined, it overrides the default of %PROGRAMFILES%. Also - if this app is installed on a 64-bit OS, the location will change to "Program Files X86". Using a variable for the root path will make it easier to detect the platform and thus the appropriate root path.
After running Sanity it looks like you are mostly adhering to NoVarsInStrings, so I'd suggest turning it ON and resolving the 2-3 errant strings that have vars in them. Sanity also identified an unbalanced For loop. Should you run sanity, be aware that it will complain about mismatched parens for most of your multi-line messagebox functions. These are not issues, but Sanity won't span multiple lines when performing this check.
Enabling Explicit - especially as scripts become more complex - is a good idea and worth the effort. Declaring the vars with Dim (and Global for all the form references) may seem like a pain until you try to troubleshoot an errant global that's being changed somewhere.
Installing KixForms.dll to System32 is ill-advised, especially when working with Vista and newer platforms. Create a "Program Files\DrRick\bin" folder, copy the DLL there and register it from that path. Works just the same and avoids all the UAC/security issues of newer platforms.
!!!HUGE ISSUE!!! Your "About" screen refers to "KixTart" (Twice!!). This is a product that may occasionally emerge from Ruud's kitchen but never from his computer lab! The proper name is "KiXtart" or simply "Kixtart". 
Glenn
_________________________
Actually I am a Rocket Scientist!
|
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 1003 anonymous users online.
|
|
|