Page 1 of 1 1
Topic Options
#202310 - 2011-05-23 09:44 PM Get Database Name
Dr_Rick Offline
Fresh Scripter

Registered: 2009-07-05
Posts: 38
Loc: Guam
I am needing to query a standalone Microsoft Database(.mdb) and return the database name and the last time it was accessed. The table name for the name is tblDataBaseInfo and the field is DataBaseName. I am needing a text string that I can populate a ListView. I have seen many examples of accessing databases but some seem more that what is actually required for this script.
The database is stored locally on the workstation in the applications folder.
_________________________
Dr Rick
Computer Specialist

Top
#202311 - 2011-05-23 10:01 PM Re: Get Database Name [Re: Dr_Rick]
NTDOC Administrator Offline
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 Moderator Offline
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).

 Code:
$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 Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK
GetExtFileProperties() may also be of help...
http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=160880

Top
#202316 - 2011-05-24 03:19 AM Re: Get Database Name [Re: ShaneEP]
Dr_Rick Offline
Fresh Scripter

Registered: 2009-07-05
Posts: 38
Loc: Guam
CitrixMan
I tried using the extended file property function and was not able to get the internal database name. I was however successful in getting it to return when I used the example you provided. The trouble I think I was having is the difference between the hyphen and the quotation marks. I can see this as being a very useful tool and I am very thankful for your help. I will have to get a handle on the differences between them and master it. The database application that we are using has hard coded calls to a unique database name. The database cannot be cleared and as a result not very portable. The KixForm script I am developing will allow the end user to toggle between databases before launching the application. The toggle will automatically rename the file the same as the internal database name and display the same in a ListBox to allow them to select which database they want to use.
_________________________
Dr Rick
Computer Specialist

Top
#202319 - 2011-05-24 04:12 AM Re: Get Database Name [Re: Dr_Rick]
ShaneEP Moderator Offline
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.

 Code:
$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
#202322 - 2011-05-24 02:09 PM Re: Get Database Name [Re: NTDOC]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4401
Loc: New Jersey
Just an FYI - the entire collection of Jens' database UDFs can be downloaded as a single UDF Library from my web site. DBLib is available under the Resources section, in the Kixtart UDF Library.

Since most of these UDFs are used together, I've found it useful to put them all in one file for most situations.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#202338 - 2011-05-26 02:49 AM Re: Get Database Name [Re: ShaneEP]
Dr_Rick Offline
Fresh Scripter

Registered: 2009-07-05
Posts: 38
Loc: Guam
I have completed the script and as promised have included a link to it below. I am working on getting it to read an config file and allow it to be customised so it can be used to store the database files elsewhere. I would appreciate any comments and am interested in doing things more efficiently in future scripts of this kind. Thanks again for your help.

iToggle II Script
_________________________
Dr Rick
Computer Specialist

Top
#202339 - 2011-05-26 04:03 AM Re: Get Database Name [Re: Dr_Rick]
ShaneEP Moderator Offline
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.

 Code:
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
 Code:
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 Administrator Offline
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". \:D

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#202377 - 2011-05-29 03:33 AM Re: Get Database Name [Re: ShaneEP]
Dr_Rick Offline
Fresh Scripter

Registered: 2009-07-05
Posts: 38
Loc: Guam
Thanks for looking at it and I will check out the lines and modify my script accordingly. I have changed it to test the InternalName string for invalid characters that are not allowed when naming files. Wouldn't want a user to attempt to change the file name and it fail because of invalid characters.
_________________________
Dr Rick
Computer Specialist

Top
#202378 - 2011-05-29 03:39 AM Re: Get Database Name [Re: Glenn Barnas]
Dr_Rick Offline
Fresh Scripter

Registered: 2009-07-05
Posts: 38
Loc: Guam
Thanks for looking at the script. I will carefully examine my script for the things that you noticed and adjust accordingly. I appreciate the forward compatibility information. I would like to see my scripts have functionality in future operating systems. I have used sanity check but gotten out of the habit of using it. I agree that in scripts that are complex that explicit is necessary as it is indeed difficult to trouble shoot errors.
_________________________
Dr Rick
Computer Specialist

Top
#202379 - 2011-05-29 05:01 AM Re: Get Database Name [Re: Dr_Rick]
Dr_Rick Offline
Fresh Scripter

Registered: 2009-07-05
Posts: 38
Loc: Guam
I added the $nul statements and I liked the stability it added to the presentation of the console window. I also corrected the misspelling of Kixtart in the script. I found that instead of using the %programfiles% variable I was able to read the registry key for the exact location of the installed application in case it was installed with a different name. I corrected the missing next in the For loop and ran the script without errors. Again I appreciated all the help.
_________________________
Dr Rick
Computer Specialist

Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 1003 anonymous users online.
Newest Members
StuTheCoder, M_Moore, BeeEm, min_seow, Audio
17884 Registered Users

Generated in 0.076 seconds in which 0.049 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org