| View previous topic :: View next topic |
| Author |
Message |
Henrik
Valued Newbie
Joined: 09 Jul 2000
Posts: 35
Location: Copenhagen, Denmark
|
 Posted: Mon Jan 07, 2002 6:00 am Post subject: Using VDSDB |
 |
|
Hi guys !
Since Tommy has done the VDSDB and this has been on my wish list for quite some time, I thought I'd look into it.
However, I cannot quite get the grip of it, so an example would be nice.
What I need is code to show how to
- Create a new DB (A new MDB file)
- Load an existing MDB file
- Unload / closing a DB so that a new one can be loaded
When I tried to manage this myself, I got an error about ODBC not being able to open Datasource Unknown ?
Any ideas ?
_________________
Henrik Skov
Email: henrikskov@mail.dk |
|
| Back to top |
|
 |
Tommy
Admin Team
Joined: 16 Nov 2002
Posts: 746
Location: The Netherlands
|
| Posted: Mon Jan 07, 2002 4:51 pm Post subject: |
|
|
Hi Henrik,
First of all, you'll need to create an ODBC DSN (datasource) on the system. This can be done programmatically through the DLL:
| Code: |
db setup,"Microsoft Access Driver (*.mdb)",DSN=test,UID=admin,DBQ=C:\test.mdb
|
This sets it up (you can also use ODBC in the Control Panel to change settings related to the DSN), you'll only need to do this once. Important: this requires the file (C:\test.mdb in this case) to exist already. The DLL currently is unable to create new .MDB or other database files, although you'll be able to use a standard blank file (open up MS-Access, create a new database and quit). It's also possible through the ODBC Control Panel, in which case you won't need to launch Access to create the blank database. After this you can access the database file through the alias "test".
Prior to accessing data in a database (or updating data in it), you'll need to connect to it:
| Code: |
db connect,test,admin
|
Then you can send queries to the database using DB QUERY. Queries must be in SQL syntax. SQL is the basic for utilizing almost any database system. A very extensive SQL tutorial is at:
http://www.greentechnologist.org/mirror/w3.one.net/%257Ejhoffman/sqltut.htm
The following SQL snippets are taken from there.
To create a table, for example, you could use:
| Code: |
list create,1
list loadtext,1
"CREATE TABLE ORDERS
"(OWNERID INTEGER NOT NULL,
"ITEMDESIRED CHAR(40) NOT NULL)
db query,@text(1)
list close,1
|
To request data from an existing table, you could use for example:
| Code: |
db query,"SELECT FirstName, LastName, Address, City, State
FROM EmployeeAddressTable;"
|
To retrieve the results from such query, repeat DB FETCHROW until @OK() is set to false. In the meanwhile use @DB(GET, <field name>) to retrieve the results for each field in a row, such as @DB(GET,FirstName).
To add data to an existing table, you could use:
| Code: |
db query,"INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM)
VALUES (01, 21, 'Ottoman');"
|
To update existing data in an existing table, you could use:
| Code: |
db query,"UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair';"
|
And finally, to delete some data, use:
| Code: |
list create,1
list loadtext,1
"DELETE FROM ANTIQUES
"WHERE ITEM = 'Ottoman' AND BUYERID = 01 AND SELLERID = 21;
db query,@text(1)
list close,1
|
Of course those are just some examples to give you an impression of what SQL is and only a fraction of SQL could be explained here.
To disconnect in order to be able to connect to a different database, you could simply use:
I hope this will be of some help.
Best regards,
Tommy |
|
| Back to top |
|
|
Sanjuro
Contributor
Joined: 01 May 2003
Posts: 59
Location: Norfolk-United Kingdom
|
| Posted: Wed May 28, 2003 9:15 am Post subject: |
|
|
Hope this isn't too tiresome but as a newbie to both VDS and Databases in general I figure I need all the help I can get! 
I want to use the VDSDB.DLL extension to connect to a database and query it for a list of users that are associated with the field COMPUTERNAME? I will need to do this from 95 and NT machines. The database is an SQL DB, so i am told. having read the posts in this thread i can see how the extension works for an MDB, but am stuck when it comes to connecting to a SQl server DB? I do not know what part of SQL server i am connecting to, such as a file extension? The part i need help with is the last part of the connection string...DBQ=??????
Hope this question doesn't make me look dumber than i feel.... 
I haven't found any documantation for VDSDB.DLL? I have read the VDSDLL3 Info on it but there is nothing for SQL connections.
Hope you can help me,
Thanks in advance
SANJURO 
_________________
"Apparently three out of four people make up 75 percent of the population. |
|
| Back to top |
|
|
arcray
Valued Contributor
Joined: 13 Jul 2001
Posts: 242
Location: Aude, France
|
| Posted: Wed May 28, 2003 12:02 pm Post subject: |
|
|
The best way is to ensure that you have an ODBC driver installed for your PC that connects to the SQL database.
And then you'll be able to use VDS and VDSDB to pass commands to the database.
You will need to know the syntax the ODBC driver expects you to use and there should be plenty of resources around that can help you.
Andy
_________________
Andrew GRAY
If you don't know I am looking for work, I won't get the job.
andrewrcgray.com |
|
| Back to top |
|
|
Sanjuro
Contributor
Joined: 01 May 2003
Posts: 59
Location: Norfolk-United Kingdom
|
| Posted: Fri May 30, 2003 2:18 pm Post subject: |
|
|
Andy/All,
Thanks for that. My problem lies in the approach i need to take. i need to be able to go to a users machine and run a single file that will install the ODBC drivers to connect to a SQL server on the network.
Then I need to query a couple of tables to return a list of users and a unique ID. These are then stored in variables to be used by a custom backup process that saves user data and settings to a server share \\server\share\%username% ( nearly there)
I can work out how to do the queries i just have problems with the ODBC setup from within VDSDB.dll
I havent purchased yet as i would like to be sure that it can do what i want it to. Does anyone else out there use VDSDB to connect to a SQL server? or can you tell me if there is a help file available upon purchase?
Cheers
All
Sanjuro
_________________
"Apparently three out of four people make up 75 percent of the population. |
|
| Back to top |
|
|
arcray
Valued Contributor
Joined: 13 Jul 2001
Posts: 242
Location: Aude, France
|
| Posted: Fri May 30, 2003 3:23 pm Post subject: |
|
|
Have a look at the following which I use as part of a fresh installation on new machines at clients sites:-
| Code: |
IF @NULL(@REGREAD(LOCAL,Software\FLUS,Location))
info Please choose a location for your data!
REPEAT
%a = @dirdlg(Select the network or local folder that@CR()will/does contain your data,"C:\Program Files\Firstlight\dev\data\Rates\NU")
UNTIL @NOT(@NULL(%a))
IF @NOT(@FILE(%a\flight.mdb))
IF @ASK(You are creating a new FLUS database at %a. Is this OK?)
FILE COPY,"C:\Program Files\Firstlight\dev\installs\flight.mdb",%a\flight.mdb
FILE COPY,"C:\Program Files\Firstlight\dev\installs\RiskTable.mdb",%a\RiskTable.mdb
FILE COPY,"C:\Program Files\Firstlight\dev\installs\PolHeaders.ini",%a\PolHeaders.ini
FILE COPY,"C:\Program Files\Firstlight\dev\installs\Broker.ini",%a\Broker.ini
ELSE
info Installation Process cancelled!
goto Close
END
END
IF @NOT(@OK())
info You must have Administrator rights on this machine to proceed!
goto Close
END
LIST ASSIGN,listdsns,@DB(drivers)
IF @EQUAL(@COUNT(listdsns),0)
WARN You have no odbc drivers installed! System will stop.
STOP
ELSE
IF @MATCH(listdsns,%1)
DB SETUP,%1,DSN=Flight,UID=admin,PWD=XXXXXX,DataDirectory=,DBQ=%a\flight.mdb
IF @OK()
WARN A new Access Database is being created for the Data Tables. Please WAIT!
DIALOG CURSOR,WAIT
GOSUB PROGRESS1
ELSE
WARN A new Access Database was not created for the Data Tables.@CR()Please Report!
INFO You may have to create a system DNS manually
END
DB SETUP,%1,DSN=RiskTable,UID=admin,PWD=XXXXXX,DataDirectory=,DBQ=%a\RiskTable.mdb
IF @OK()
WARN A new Access Database is being created for the Lookup Tables. Please WAIT!
GOSUB PROGRESS1
ELSE
WARN A new Access Database was not created for the Lookup Tables.@CR()Please Report!
INFO You may have to create a system DNS manually
END
ELSE
WARN You do not have a %1 installed
STOP
END
END
INFO Wait is over. Thank you
DIALOG CURSOR
REGISTRY WRITE,LOCAL,Software\FLUS,Drive1,C:\Program Files\Firstlight\dev\data
IF @NOT(@OK())
INFO There is a problem with writing to the value 'Drive1' in the registry
END
REGISTRY WRITE,LOCAL,Software\FLUS,Location,%a
IF @NOT(@OK())
INFO There is a problem with writing to the value 'Location' in the registry
END
END
%%Location = @REGREAD(LOCAL,Software\FLUS,Location)
%Q = @ASK(The location for your data files is:-@CR()@CR()%%Location@CR()@CR()Is this OK?)
IF %Q
INFO You are ready to use the NU QE!
ELSE
REGISTRY WRITE,LOCAL,Software\FLUS,Location
INFO Please try again
goto Restart
END
IF @NOT(@FILE(%%Location\RiskTable.mdb))
FILE COPY,"C:\Program Files\Firstlight\dev\installs\RiskTable.mdb",%%Location\RiskTable.mdb
DB SETUP,%1,DSN=RiskTable,UID=admin,PWD=XXXXXX,DataDirectory=,DBQ=%%Location\RiskTable.mdb
IF @OK()
WARN A new Access Database is being created for the Lookup Tables. Please WAIT!
GOSUB PROGRESS1
ELSE
WARN A new Access Database was not created for the Lookup Tables.@CR()Please Report!
INFO You may have to create a system DNS manually
END
END
FILE COPY,"C:\Program Files\Firstlight\dev\installs\flight.xls",%%Location\flight.xls
WAIT
:close
EXIT
:PROGRESS1
DIALOG SHOW,PROGRESS1
%B = 0
REPEAT
WAIT 0.1
%B = @SUCC(%B)
DIALOG SET,PROGRESS1,%B
UNTIL @EQUAL(%B,99)
DIALOG HIDE,PROGRESS1
EXIT
:ERRORTRAP
INFO There has been an Error No @ERROR(E) at @ERROR(N)!@CR()ODBC Error is as follows:-@CR()%1
INFO Please report!
STOP
EXIT
|
This seems to work OK. It has successfully set up system DSNs on around 200 machines spread around the country
_________________
Andrew GRAY
If you don't know I am looking for work, I won't get the job.
andrewrcgray.com |
|
| Back to top |
|
|
Sanjuro
Contributor
Joined: 01 May 2003
Posts: 59
Location: Norfolk-United Kingdom
|
| Posted: Fri May 30, 2003 5:30 pm Post subject: |
|
|
Andy,
My problem lies in this line.... | Quote: | | DB SETUP,%1,DSN=RiskTable,UID=admin,PWD=XXXXXX,DataDirectory=,DBQ=%a\RiskTable.mdb |
I cannot see how to alter this line for a SQL Server connection to a Database called AeXNS on a remote machine called COBALT. I want to create a system DSN for all users. It will only be used for a single query, but it needs to work simply and quickly.
On this topic i also found out that the ODBCCONF.exe file in the Sys32 folder allows for the same sort of funcionality from the command line. such as odbcconf.exe /a {CONFIGSYSDSN "SQL SERVER" DSN=AeXNS}
But the DSN string here is whats throwing me as well. It creates a DSN in the Data Sources (ODBC) panel but i cannot use this to connect via my script?
Anyways, some headscratching to do here methinks...
Cheers
Sanjuro
_________________
"Apparently three out of four people make up 75 percent of the population. |
|
| Back to top |
|
|
Tommy
Admin Team
Joined: 16 Nov 2002
Posts: 746
Location: The Netherlands
|
| Posted: Sun Jun 01, 2003 10:59 pm Post subject: |
|
|
| Sanjuro wrote: | On this topic i also found out that the ODBCCONF.exe file in the Sys32 folder allows for the same sort of funcionality from the command line. such as odbcconf.exe /a {CONFIGSYSDSN "SQL SERVER" DSN=AeXNS}
|
If this line you mentioned is correct, I would expect the following code to have the same
effect:
DB SETUP,SQL SERVER,DSN=AeXNS
Of course once the DSN is set up either using the DB SETUP command or manually through
the Control Panel, doing this is no longer required. You should simply be able to connect
to the DSN.
Also, the helpfile for the VDSDLL 3 (which includes VDSDB), is contained in the standard
VDSDLL 3 package:
http://www.vdsworld.com/index.php?page=download&fileid=60 |
|
| Back to top |
|
|
Sanjuro
Contributor
Joined: 01 May 2003
Posts: 59
Location: Norfolk-United Kingdom
|
| Posted: Mon Jun 02, 2003 8:12 am Post subject: |
|
|
Time for the tickertape parade!!!!
This worked just fine
Thanks Tommy and all who helped me out here,
Yes the string concerned was
| Quote: |
DB SETUP,SQL SERVER,DSN=AeXNS
|
I had tried it this way but with the comma placeholders for the other parts of the command options eg
DB SETUP, SQL SERVER,DSN=AeXNS, UID=??,PW=??,DataDirectory=,DBQ=????????
Thanks again
Sanjuro
_________________
"Apparently three out of four people make up 75 percent of the population. |
|
| Back to top |
|
|
Sanjuro
Contributor
Joined: 01 May 2003
Posts: 59
Location: Norfolk-United Kingdom
|
| Posted: Tue Jun 03, 2003 10:54 am Post subject: |
|
|
Have found another way for those of you that do SQL stuff through VDS.
I used the following command in a batch file:
| Code: |
OSQL.exe -E -S <servername> -d <sql db name> -Q"<SELECT name FROM My_Table WHERE NOT(name IS NULL)" -o c:\outputfile.csv
|
This allows me to call the .BAT to connect to the DB and run a query without setting up a DSN at all. It also pipes the output to a file of my choosing. So long as my credentials are OK that is.
I used the OSQL.exe from its original location but i think if i move it somewhere else i would need to take the appropriate DLL's with it
Sanjuro[/code]
_________________
"Apparently three out of four people make up 75 percent of the population. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
@vdsworld