Simplest Way to Test ODBC on WIndows

Question

With unixODBC you can use a simple command line utility called "isql" to test your connection and permissions of some queries. Without having to write extra code or install libs or bloated programs, is there a simple way to open up X data source send some sql commands and be done with it?

Doing this on the command line would be preferable.

Josh Bode · Answer 1 · 2014-03-07T21:08:29.303

31

One way to create a quick test query in Windows via an ODBC connection is using the DQY format.

To achieve this, create a DQY file (e.g. test.dqy) containing the magic first two lines (XLODBC and 1) as below, followed by your ODBC connection string on the third line and your query on the fourth line (all on one line), e.g.:

XLODBC
1
Driver={Microsoft ODBC for Oracle};server=DB;uid=scott;pwd=tiger;
SELECT COUNT(1) n FROM emp

Then, if you open the file by double-clicking it, it will open in Excel and populate the worksheet with the results of the query.

edited Mar 07 '14 at 21:08

answered Mar 07 '14 at 12:37

Josh Bode

3,037
1
22
16

3

Installing excel isn't feasible. But, very awesome tip. – Rescommunes Sep 11 '14 at 17:40
awesome tip. :) – Carlos R Jul 05 '17 at 12:50

score 15 · Answer 2 · answered Apr 10 '13 at 20:21

15

Make a file SOMEFILENAME.udl then double click on it and set it up as an ODBC connection object, username, pwd, target server

answered Apr 10 '13 at 20:21

Jeannine Menger

187
1
3

2

The question was about how to execute an sql statement. Not create a connection object. – Rescommunes Apr 13 '13 at 04:46
Actually this was very helpful. – James Young Nov 09 '16 at 19:30

geographika · Answer 3 · 2011-10-17T07:33:57.830

11

You can use the "Test Connection" feature after creating the ODBC connection through Control Panel > Administrative Tools > Data Sources.

To test a SQL command itself you could try:

http://www.sqledit.com/odbc/runner.html

http://www.sqledit.com/sqlrun.zip

Or (perhaps easier and more useful in the long run) you can make a test ASP.NET or PHP page in a couple minutes to run SQL statement yourself through IIS.

edited Oct 17 '11 at 07:33

answered Apr 24 '10 at 00:51

geographika

6,098
2
33
50

2

I don't want to just test the connection i want to test permissions of some queries. I edited the post to make that clearer. – Rescommunes Apr 24 '10 at 01:41
Its for other persons software which uses ODBC to connect to mysql so OLE is not really an option... SQL Runner looks like success. Thanks for the help – Rescommunes Apr 24 '10 at 21:16

score 10 · Answer 4 · answered Apr 27 '10 at 19:51

10

For ad hoc queries, the ODBC Test utility is pretty handy. Its design and interface is more oriented toward testing various parts of the ODBC API. But it works quite nicely for running queries and showing the output. It is part of the Microsoft Data Access Components.

To run a query, you can click the connect button (or use ctrl-F), choose a data source, type a query, then ctrl-E to execute it and ctrl-R to display the results (e.g., if it is a SELECT or something that returns a cursor).

answered Apr 27 '10 at 19:51

Mark Wilkins

39,254
5
53
106

Installed but, search (all folders) failed to find the executable Odbcte32.exe. – Rescommunes Apr 29 '10 at 03:51
@Rescoummunes: I wonder if they removed it ... I see a few references that say it is in the MDAC. There are other non-microsoft sites that list that as a download, but I would be hesitant to use one of those. Maybe I can poke around more today. – Mark Wilkins Apr 29 '10 at 12:04
3

@Rescommunes: I just now followed the link I posted, downloaded mdac28sdk.msi and ran the install on a clean vmware image. It put odbcte32.exe into C:\Program Files\Microsoft Data Access SDK 2.8\Tools\x86. – Mark Wilkins Apr 29 '10 at 21:31
You can use [Universal Extractor](http://legroom.net/software/uniextract) to extract the MSI and get the contents. – ƘɌỈSƬƠƑ Mar 03 '15 at 08:33
MDAC2.8 was still available today. – user2063329 May 03 '18 at 02:49

GuillaumeN · Answer 5 · 2019-09-13T17:47:52.877

It's been a while but since I precisely have the answer to the question, I'll share it and maybe someone will benefit from it.

Jaime de Los Hoyos wrote a very nice program to precisely do that: ODBC Query Tool.
Unfortunately, Jaime's website is defunct but you can still find the program and its source code at this location:
https://sourceforge.net/projects/odbc-query-tool/files/latest_release/

The program is GUI based and consists of a single executable file, no need to install anything on the machine.

Jaime's profile : https://stackoverflow.com/users/878998/jaime-de-los-hoyos-m
Jaime's posts on a forum talking about his program : https://forum.powerbasic.com/forum/user-to-user-discussions/source-code/48266-odbc-query-tool-retrieve-information-from-any-database-easily

score 1 · Answer 6 · answered Jun 19 '17 at 16:35

1

a simple way is:

create a fake "*.UDL" file on desktop

(UDL files are described here: https://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx.

in case you can also customized it as explained there. )

answered Jun 19 '17 at 16:35

ingconti

9,213
2
51
39

Simplest Way to Test ODBC on WIndows

6 Answers6

Linked