Showing posts with label odbc. Show all posts
Showing posts with label odbc. Show all posts

Monday, March 12, 2012

How to use ODBC to find a record by key in a larger result set

I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
working fine in a report style program and it's very fast. In Maintenance
programs, I need to find a specific record and/or scroll from there. I can
get the scrolling next and prior working ok and I can get the calling up a
single account ok but scrolling immediately after a single record rowset fin
d
only works if I issue another select statement for the entire file. Of cours
e
the first sqlfetch gets the first record and not the first record after the
one that was just on screen. In other words I need to know how to retain the
absolute row position in the file some way so my scrolling can start from
there.Briefly, you need to bind column 0 (the bookmark column). There are some
set up steps you need to take (Setting 'use bookmarks' values to ON, etc.)
I'm not sure how much experience you have, but you should be able to find
some useful information on http://msdn.microsoft.com by searching for "ODBC
BOOKMARK". For example, I found an interesting article at
http://msdn.microsoft.com/library/d...-us/odbc/htm/od
bcscrolling_by_bookmark.asp
| Thread-Topic: How to use ODBC to find a record by key in a larger result
set
| thread-index: AcW5Qb9KPSrQ97YTT32NMc+1qaZ1Iw==
| X-WBNR-Posting-Host: 70.61.11.50
| From: "examnotes" <BobMTenn@.discussions.microsoft.com>
| Subject: How to use ODBC to find a record by key in a larger result set
| Date: Wed, 14 Sep 2005 08:34:08 -0700
| Lines: 10
| Message-ID: <BFB597E8-F6B0-4541-8345-52B400B58E86@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2708
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
| working fine in a report style program and it's very fast. In Maintenance
| programs, I need to find a specific record and/or scroll from there. I
can
| get the scrolling next and prior working ok and I can get the calling up
a
| single account ok but scrolling immediately after a single record rowset
find
| only works if I issue another select statement for the entire file. Of
course
| the first sqlfetch gets the first record and not the first record after
the
| one that was just on screen. In other words I need to know how to retain
the
| absolute row position in the file some way so my scrolling can start from
| there.
|

How to use ODBC to find a record by key in a larger result set

I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
working fine in a report style program and it's very fast. In Maintenance
programs, I need to find a specific record and/or scroll from there. I can
get the scrolling next and prior working ok and I can get the calling up a
single account ok but scrolling immediately after a single record rowset find
only works if I issue another select statement for the entire file. Of course
the first sqlfetch gets the first record and not the first record after the
one that was just on screen. In other words I need to know how to retain the
absolute row position in the file some way so my scrolling can start from
there.
Briefly, you need to bind column 0 (the bookmark column). There are some
set up steps you need to take (Setting 'use bookmarks' values to ON, etc.)
I'm not sure how much experience you have, but you should be able to find
some useful information on http://msdn.microsoft.com by searching for "ODBC
BOOKMARK". For example, I found an interesting article at
http://msdn.microsoft.com/library/de...us/odbc/htm/od
bcscrolling_by_bookmark.asp
| Thread-Topic: How to use ODBC to find a record by key in a larger result
set
| thread-index: AcW5Qb9KPSrQ97YTT32NMc+1qaZ1Iw==
| X-WBNR-Posting-Host: 70.61.11.50
| From: "=?Utf-8?B?Qm9iTVRlbm4=?=" <BobMTenn@.discussions.microsoft.com>
| Subject: How to use ODBC to find a record by key in a larger result set
| Date: Wed, 14 Sep 2005 08:34:08 -0700
| Lines: 10
| Message-ID: <BFB597E8-F6B0-4541-8345-52B400B58E86@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2708
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
| working fine in a report style program and it's very fast. In Maintenance
| programs, I need to find a specific record and/or scroll from there. I
can
| get the scrolling next and prior working ok and I can get the calling up
a
| single account ok but scrolling immediately after a single record rowset
find
| only works if I issue another select statement for the entire file. Of
course
| the first sqlfetch gets the first record and not the first record after
the
| one that was just on screen. In other words I need to know how to retain
the
| absolute row position in the file some way so my scrolling can start from
| there.
|

How to use ODBC SQLSetConnectAttr from C# ?

Hi,

I'm having problems dealing with some large datatypes in a C# application using odbc.

Basically certain datatypes, such as db2 CLOB, are not recognized as 'valid' by the odbc driver.

It seems the fix is to use the SQLSetConnectAttr function using the appropriate key/value pair to 'map' those to datatypes known to ODBC but I'm not sure how to use it in C# code. The template seems to be more an unmanaged C type API with handles being passed etc...

SQLRETURN SQLSetConnectAttr (
SQLHDBC ConnectionHandle, /* hdbc */
SQLINTEGER Attribute, /* fOption */
SQLPOINTER ValuePtr, /* pvParam */
SQLINTEGER StringLength); /* fStrLen */

I tried creating an OdbcCommand with that function as the command text but without success.

Could somebody drop a line with a C# example of how to do that ?

Thank you,

MM

I don't think you can get to ODBC connection attributes if you're using the ODBC managed provider.

Have you considered using the LongDataCompat keyword in the db2cli.ini config file? You should be able to achieve this via the drivers setup dialog via the ODBC control panel when you configur the DSN rather than editing the file directly. It maps to SQL_ATTR_LONGDATA_COMPAT which is the attribute you're probably trying to set programatically.

|||

Yes,

I did try that, actually followed the instructions of the ibm db2 boulder site and edited the file directly. Unfortunately it didn't work.

Changing the parameter in the DSN does work, but :

1- You have to do it for each DSN (and I'm dealing with numerous DBs)

2- The piece of code I wanted to use it for is to be deployed on machines I do not control and it would be better to let the app handle the data type rather than having the user fiddle with their DSN config.

Any suggestion as to how to do that ?

Thanks,

MM

|||

The next thing to try would be setting the attribute via a connection string keyword. The ODBC managed provider will pass this though to the driver, so this is a fairly safe and clean approach where the driver provides an appropriate keyword.

Failing this - and only if you're absolutely desperate - you might try reflection with the ODBC managed provider to try to gain access to the underlying ODBC connection handle. This would be completely unsupported (if it worked at all) and there is no guarantee that it wouldn't change in the future.

|||

Chris,

I have tried passing the info as an extra connection attribute at the end of the connection string, to no avail.

But I was really poking around for a solution, so I'm not sure I used a correct syntax. Also I'm not sure if the placement matters (ie after the password...). I just stuck it at the end.

Would you know what the correct syntax is ?

Thanks,

MM

|||It should be 'LongDataCompat=1' and can appear anywhere in the connection string, so the end would be fine. Connection string items are seperated by semi-colons. Is this what you tried?|||

Chris,

It worked, you're a genius!

I had tried adding ';SQL_ATTR_LONGDATA_COMPAT=SQL_LD_COMPAT_YES'

I thought LONGDATACOMPAT=1 was to be used in the db2cli.ini file and SQL_ATTR... = SQL_LD... was the key/value pair to be used in the connection string based on the info off the IBM DB2 website.

I still don't understand why it's not but at least it's working!

Thank you,

MM

|||

Hi MM,

My reading was that you can use ini file entries in connection strings. If there's a conflict the connection string wins.

I'm pleased to hear you app is working.

How to use ODBC SQLSetConnectAttr from C# ?

Hi,

I'm having problems dealing with some large datatypes in a C# application using odbc.

Basically certain datatypes, such as db2 CLOB, are not recognized as 'valid' by the odbc driver.

It seems the fix is to use the SQLSetConnectAttr function using the appropriate key/value pair to 'map' those to datatypes known to ODBC but I'm not sure how to use it in C# code. The template seems to be more an unmanaged C type API with handles being passed etc...

SQLRETURN SQLSetConnectAttr (
SQLHDBC ConnectionHandle, /* hdbc */
SQLINTEGER Attribute, /* fOption */
SQLPOINTER ValuePtr, /* pvParam */
SQLINTEGER StringLength); /* fStrLen */

I tried creating an OdbcCommand with that function as the command text but without success.

Could somebody drop a line with a C# example of how to do that ?

Thank you,

MM

I don't think you can get to ODBC connection attributes if you're using the ODBC managed provider.

Have you considered using the LongDataCompat keyword in the db2cli.ini config file? You should be able to achieve this via the drivers setup dialog via the ODBC control panel when you configur the DSN rather than editing the file directly. It maps to SQL_ATTR_LONGDATA_COMPAT which is the attribute you're probably trying to set programatically.

|||

Yes,

I did try that, actually followed the instructions of the ibm db2 boulder site and edited the file directly. Unfortunately it didn't work.

Changing the parameter in the DSN does work, but :

1- You have to do it for each DSN (and I'm dealing with numerous DBs)

2- The piece of code I wanted to use it for is to be deployed on machines I do not control and it would be better to let the app handle the data type rather than having the user fiddle with their DSN config.

Any suggestion as to how to do that ?

Thanks,

MM

|||

The next thing to try would be setting the attribute via a connection string keyword. The ODBC managed provider will pass this though to the driver, so this is a fairly safe and clean approach where the driver provides an appropriate keyword.

Failing this - and only if you're absolutely desperate - you might try reflection with the ODBC managed provider to try to gain access to the underlying ODBC connection handle. This would be completely unsupported (if it worked at all) and there is no guarantee that it wouldn't change in the future.

|||

Chris,

I have tried passing the info as an extra connection attribute at the end of the connection string, to no avail.

But I was really poking around for a solution, so I'm not sure I used a correct syntax. Also I'm not sure if the placement matters (ie after the password...). I just stuck it at the end.

Would you know what the correct syntax is ?

Thanks,

MM

|||It should be 'LongDataCompat=1' and can appear anywhere in the connection string, so the end would be fine. Connection string items are seperated by semi-colons. Is this what you tried?|||

Chris,

It worked, you're a genius!

I had tried adding ';SQL_ATTR_LONGDATA_COMPAT=SQL_LD_COMPAT_YES'

I thought LONGDATACOMPAT=1 was to be used in the db2cli.ini file and SQL_ATTR... = SQL_LD... was the key/value pair to be used in the connection string based on the info off the IBM DB2 website.

I still don't understand why it's not but at least it's working!

Thank you,

MM

|||

Hi MM,

My reading was that you can use ini file entries in connection strings. If there's a conflict the connection string wins.

I'm pleased to hear you app is working.

How to use ODBC datasources in SQL Server 2005 x64

I migrated from SQL Server 2000 Standard to SQL Server 2005 Standard
x64. Now i try to get the linked Servers working, but it looks like
there is no way to use ODBC Data sources as linked servers. Yes of
course, i ve got 64-Bit ODBC Drviers installed and tested.
The MSDASQL (ole db provider for ODBC data sources) is missing.
SQL Server 2005 x64 does not support ODBC Data sources as linked
servers!! Is it really true?
tia
Gregor StefkaGregor Stefka schrieb:
>
> I migrated from SQL Server 2000 Standard to SQL Server 2005 Standard
> x64. Now i try to get the linked Servers working, but it looks like
> there is no way to use ODBC Data sources as linked servers. Yes of
> course, i ve got 64-Bit ODBC Drviers installed and tested.
> The MSDASQL (ole db provider for ODBC data sources) is missing.
> SQL Server 2005 x64 does not support ODBC Data sources as linked
> servers!! Is it really true?
>
> tia
> Gregor Stefka
I cant find any information on microsoft.com.
can anyone clarify this?
is there odbc support (MSDASQL.DLL) for SSIS / linkedServers in SQL
Server 2005 x64?

Sunday, February 19, 2012

How to use 32-bit ODBC driver (sybase) at an ETL application running (deployed into SQL Server)

I am developing an ETL system on a 32-bit machine let's say, called 'dwdev'. The application connects to a Sybase database as a source system using ODBC connection. I am able to run this application directly from Visual studio 2005 and i am able to deploy it into SQL Server 2005 and run it from Server as a job. Both work. At this server i am connecting to a SQL Server instance called 'dwdev'.

When i transfer my application onto a 64-bit machine, let's say called 'dwtest', i am able to run my application from Visual Studio 2005 properly and it works, but when i deploy it into SQL Server (instance is called 'dwtest') and try to run it as a job just like the development machine, it gives an error and stops executing.

I was facing 'data source' type of error when i was first trying it from Visual Studio, but i solved this problem by setting Run64bitRunTime settings to FALSE on the Project's properties Debugging Page, at 64-bit machine. now it uses 32-bit runtime then 32-bit Sybase ODBC driver. But i cannot force the application to use 32 bit runtime when i am executing it from SQL Server as a job.

How can i do that? is there an alternative method?

Thanks for your help...

The solution in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603814&SiteID=1

worked for me(Thanks to Michael Entin) when i execute my application on SQL Server as a Job (SQL Server Agent) calling command line dtexec utility. I defined my job step as TYPE: Operation System (CmdExec) , RUN AS: SQL Agent Service Account

I used the follwing string as my command:

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe /sq EXECUTE_ALL_S0_JOBS_FROM_SQL_SERVER_MASTER /ser dwtest

but i still wonder whether there is a solution for defining job step as a SQL Server Integration Services Package and executing the package EXECUTE_ALL_S0_JOBS_FROM_SQL_SERVER_MASTER in a 32-bit mode under SQL Server Agent?

Thanks