isql and bcp
August 4, 2006 9:41 AM Subscribe
MS SQL Server Filter: I'm supporting a pretty old process that loads data into and out of SQL Server 6.5. The process uses isql and bcp from a SQL Server 2000 installation. Is there anywhere I can download these utilities for free, perhaps as part of a "SQL Server Client" tool? I have searched the M$ website...
With a default install of SQL Express you should find bcp.exe at C:\Program Files\Microsoft SQL Server\90\Tools\Binn
However, I could not find isql, and I'm not familair with it. Good luck.
posted by poppo at 10:06 AM on August 4, 2006
However, I could not find isql, and I'm not familair with it. Good luck.
posted by poppo at 10:06 AM on August 4, 2006
Response by poster: Thanks poppo... I have downloaded that and found that bcp indeed works. I can even download the express from SQL Server 2000, but it doesn't have isql. The newer one has osql, but substituting that command doesn't allow me to connect to the older database. grr...
posted by joecacti at 10:27 AM on August 4, 2006
posted by joecacti at 10:27 AM on August 4, 2006
You can write your own isql/bcp utilities pretty easily, using the database's C API (and of course, a free compiler).
When I've done anything like this, I've always put a C++ wrapper arouind the C API (i've done this for both Sybase and Oracle). The only real effort comes from dealing with heterogeneous data, but for isql and bcp, you'll pretty much want to get all the data as char anyway, and most db APIs will have a way to specify that at the API level.
Basically, you send a comamd (a C string, a const char* const) to the db, and it sends back something that basically looks like a cursor, which you call something like next() on to get each next row. I always used the Template Method Pattern to define the skeleton process, and sub-classed to actually handle the data. (All you're going to need to do, for bcp or idsql, is print out the row, so that'll be a simple subclass.)
The skeleton is pretty simple: get the command, send it, check for errors, get the result cursor, check for errors, iterate the cursor, check for errors, clean-up. If you use C++ exceptions, you can abbreviate each check for errors ito a thow, e.g.,
foo() {
enum DB_RESULT r = API_call() ;
if( r != SUCCESS throw new SQL_Exception( r, "exception here at foo" ) ;
}
The C API will ask you to allocate memory, or will allocate memory itself, depending on the API, into which it copies the actual column vaklues for each row. That'll be somethjing like an array of void* (but each API will gavie it a special type) which you'll have to cast, based on meta-data, to the right type.
So for isql, you'll cast to char*, and display it:
for( int i = 0 ; i < COLS; ++i )
cout < static_cast char*>( current_row[ i ] ) < ;br>
And for bcp, you'll do almost the same:
for( int i = 0 ; i < COLS; ++i )
cout < static_cast char*>( current_row[ i ] ) < | ;br>
(Indeed, templateize your "outputWriter" subclass on separator , and just write the class once, right?)
Of course, you can also do this in a more platform nuetral way in Java, and you can probably find exisiting C. C++, and Java code for simple isqls for most dbs.
posted by orthogonality at 3:37 PM on August 4, 2006
When I've done anything like this, I've always put a C++ wrapper arouind the C API (i've done this for both Sybase and Oracle). The only real effort comes from dealing with heterogeneous data, but for isql and bcp, you'll pretty much want to get all the data as char anyway, and most db APIs will have a way to specify that at the API level.
Basically, you send a comamd (a C string, a const char* const) to the db, and it sends back something that basically looks like a cursor, which you call something like next() on to get each next row. I always used the Template Method Pattern to define the skeleton process, and sub-classed to actually handle the data. (All you're going to need to do, for bcp or idsql, is print out the row, so that'll be a simple subclass.)
The skeleton is pretty simple: get the command, send it, check for errors, get the result cursor, check for errors, iterate the cursor, check for errors, clean-up. If you use C++ exceptions, you can abbreviate each check for errors ito a thow, e.g.,
foo() {
enum DB_RESULT r = API_call() ;
if( r != SUCCESS throw new SQL_Exception( r, "exception here at foo" ) ;
}
The C API will ask you to allocate memory, or will allocate memory itself, depending on the API, into which it copies the actual column vaklues for each row. That'll be somethjing like an array of void* (but each API will gavie it a special type) which you'll have to cast, based on meta-data, to the right type.
So for isql, you'll cast to char*, and display it:
for( int i = 0 ; i < COLS; ++i )
cout < static_cast char*>( current_row[ i ] ) < ;br>
And for bcp, you'll do almost the same:
for( int i = 0 ; i < COLS; ++i )
cout < static_cast char*>( current_row[ i ] ) < | ;br>
(Indeed, templateize your "outputWriter" subclass on separator , and just write the class once, right?)
Of course, you can also do this in a more platform nuetral way in Java, and you can probably find exisiting C. C++, and Java code for simple isqls for most dbs.
posted by orthogonality at 3:37 PM on August 4, 2006
This thread is closed to new comments.
posted by poppo at 10:04 AM on August 4, 2006