ODBC Column Type Error with Bigint

Hello All,
I found an issue and wanted osme advice or guidance
I have found an issue in the ODBC API when getting the column type of a
BIGINT. When the column type is defined as BIGINT in the table then all
works fine. The column type is 127 (x7F) and size of 8 bytes
But if the I use convert to convert the column type eg ( convert(bigint,
colname ) ) or if I sum the column in a group by query the column type
returned is no 127 but 38 (x26) which is SQLINTN but the column size is 8
bytes.
The problem then arises when srv_setcoldata ,srv_setcollen and srv_sendrow
all fail.
If anyone has the xp_gettable_odbc source which comes with SQL Server and
tests the issue like this
master..xp_gettable_odbc '(select convert(bigint,0) as tt ) as dd'
no results will be returned but this
master..xp_gettable_odbc '(select convert(int,0) as tt ) as dd' will return
one row as it should
Has anyone else come across this issue if so any fixes?
Kind regards
Greg OHello Greg,
Based on my research, this seems to be a limitation of Open Data Service
(ODS). It only support 4 bytes int and there is no support of bigint.
You could refer to "Data types" topic in BOL for details.
http://msdn.microsoft.com/library/d...-us/odssql/ods_
6_ref_10_4n5k.asp
Hope this is helpful.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "GregO" <grego@.community.nospam>
| Subject: ODBC Column Type Error with Bigint
| Date: Mon, 7 Nov 2005 18:52:42 +1100
| Lines: 29
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <uCLoZ#24FHA.1536@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: dsl-202-173-157-78.vic.westnet.com.au 202.173.157.78
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:129572
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hello All,
| I found an issue and wanted osme advice or guidance
|
| I have found an issue in the ODBC API when getting the column type of a
| BIGINT. When the column type is defined as BIGINT in the table then all
| works fine. The column type is 127 (x7F) and size of 8 bytes
|
| But if the I use convert to convert the column type eg ( convert(bigint,
| colname ) ) or if I sum the column in a group by query the column type
| returned is no 127 but 38 (x26) which is SQLINTN but the column size is 8
| bytes.
|
| The problem then arises when srv_setcoldata ,srv_setcollen and
srv_sendrow
| all fail.
|
| If anyone has the xp_gettable_odbc source which comes with SQL Server and
| tests the issue like this
| master..xp_gettable_odbc '(select convert(bigint,0) as tt ) as dd'
|
| no results will be returned but this
| master..xp_gettable_odbc '(select convert(int,0) as tt ) as dd' will
return
| one row as it should
|
| Has anyone else come across this issue if so any fixes?
|
| Kind regards
| Greg O
|
|
|

ODBC API: How to get list of database triggers?

Is there a way to retrieve a list of all database triggers using the
ODBC API?
I'm looking for something like SQLProcedures that can retrieve a list
of all database procedures.
Are triggers not supported by the ODBC catalog functions?
Why not'
I CANNOT use any database specific software such as the MS SQLServer
DMO object. Only the ODBC API functions."aRIEL" wrote...

> Is there a way to retrieve a list of all database
> triggers using the ODBC API?
> I'm looking for something like SQLProcedures that
> can retrieve a list of all database procedures.
> Are triggers not supported by the ODBC catalog functions?
> Why not'
In some cases you'll probably can't get anything comprehensible from it, as
not *all* databases support triggers.

> I CANNOT use any database specific software such
> as the MS SQLServer DMO object. Only the ODBC API functions.
Why? From a VB perspective you'd probably be better off using some OLE DB
provider, or something other possible to use in the concept of ADO. From
Java Perspective you'd be better off using thin JDBC-drivers instead of a
JDBC/ODBC-bridge...
Anyway...
Your actual question does not have much to do with some of the newsgroups
you've posted to.
Anyway, as one of the groups you've posted to is comp.lang.java.databases,
and I read your question there, I'll give you an answer from the Java
perspective.
I think what you search for could be accomplished in Java with the following
quick example:
// Open a connection to the database
Connection conn = DriverManager.getConnection(url);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet proc = dbmd.getProcedures(null, null, "%");
// Printout data on stored procedures
while(proc.next())
{
String dbObjectCatalog = proc.getString(1);
String dbObjectSchema = proc.getString(2);
String dbObjectName = proc.getString(3);
String dbObjectRemarks = proc.getString(7);
String dbObjectType = proc.getString(8); // really a short, read the
docs
System.out.println("" + dbObjectType + ": " + dbObjectName);
System.out.println(" Catalog: " + dbObjectCatalog);
System.out.println(" Schema: " + dbObjectSchema);
System.out.println(" Remarks: " + dbObjectRemarks);
}
Note that you'll get all procedures, not only triggers, but that shouldn't
be so hard to figure out through the "type".
// Bjorn A

ODBC API: How to get list of database triggers?

Is there a way to retrieve a list of all database triggers using the
ODBC API?
I'm looking for something like SQLProcedures that can retrieve a list
of all database procedures.
Are triggers not supported by the ODBC catalog functions?
Why not?
I CANNOT use any database specific software such as the MS SQLServer
DMO object. Only the ODBC API functions.
"aRIEL" wrote...

> Is there a way to retrieve a list of all database
> triggers using the ODBC API?
> I'm looking for something like SQLProcedures that
> can retrieve a list of all database procedures.
> Are triggers not supported by the ODBC catalog functions?
> Why not?
In some cases you'll probably can't get anything comprehensible from it, as
not *all* databases support triggers.

> I CANNOT use any database specific software such
> as the MS SQLServer DMO object. Only the ODBC API functions.
Why? From a VB perspective you'd probably be better off using some OLE DB
provider, or something other possible to use in the concept of ADO. From
Java Perspective you'd be better off using thin JDBC-drivers instead of a
JDBC/ODBC-bridge...
Anyway...
Your actual question does not have much to do with some of the newsgroups
you've posted to.
Anyway, as one of the groups you've posted to is comp.lang.java.databases,
and I read your question there, I'll give you an answer from the Java
perspective.
I think what you search for could be accomplished in Java with the following
quick example:
// Open a connection to the database
Connection conn = DriverManager.getConnection(url);
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet proc = dbmd.getProcedures(null, null, "%");
// Printout data on stored procedures
while(proc.next())
{
String dbObjectCatalog = proc.getString(1);
String dbObjectSchema = proc.getString(2);
String dbObjectName = proc.getString(3);
String dbObjectRemarks = proc.getString(7);
String dbObjectType = proc.getString(8); // really a short, read the
docs
System.out.println("" + dbObjectType + ": " + dbObjectName);
System.out.println(" Catalog: " + dbObjectCatalog);
System.out.println(" Schema: " + dbObjectSchema);
System.out.println(" Remarks: " + dbObjectRemarks);
}
Note that you'll get all procedures, not only triggers, but that shouldn't
be so hard to figure out through the "type".
// Bjorn A

ODBC API Question

Hi all,
Quick question about the ODBC API. Is there a function in the ODBC API that
I can call to determine what parameters a particular driver requires? For
instance, ODBC Administrator knows to allow you to select, create, repair or
compact an MS Access Database; but for SQL Server it offers different
options, including default database, use ANSI quoted identifiers, etc. Does
ODBC Administrator determine this via a function in the ODBC API, or is this
hard-coded into the ODBC Administrator?
Thanks,
Michael C.
There are various API calls that you can make to figure this out,
ODBCAD32.EXE does this dynamically. Best is to use ODBC Test
http://msdn.microsoft.com/library/de...t_overview.asp
that comes with the MDAC SDK or the Platform SDK, it allows you to use all
these APIs interactively from a UI, so you can play around with them,
otherwise they are all documented in the programmer reference guide.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Michael C" <michaelc@.nospam.org> wrote in message
news:QAXNc.16090$09.2465831@.news4.srv.hcvlny.cv.ne t...
> Hi all,
> Quick question about the ODBC API. Is there a function in the ODBC API
> that
> I can call to determine what parameters a particular driver requires? For
> instance, ODBC Administrator knows to allow you to select, create, repair
> or
> compact an MS Access Database; but for SQL Server it offers different
> options, including default database, use ANSI quoted identifiers, etc.
> Does
> ODBC Administrator determine this via a function in the ODBC API, or is
> this
> hard-coded into the ODBC Administrator?
> Thanks,
> Michael C.
>

ODBC API Question

Does anyone know if there's a way, using SQL Server ODBC Bulk Copy
extensions (bcp_bind, bcp_sendrow, bcp_batch, etc.) to determine exactly
which row/rows were not imported if an error occurs (like a Violation of
Primary Key, etc.) With SQLBulkOperations it can be done with a status
array, but I don't like the fact that I would have to read the entire table
into memory and iterate it using SQLFetch/SQLFetchScroll to update/insert
new rows using SQLBulkOperations. Any ideas appreciated.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uK2jd9i5GHA.3444@.TK2MSFTNGP02.phx.gbl...
> Does anyone know if there's a way, using SQL Server ODBC Bulk
> Copy extensions (bcp_bind, bcp_sendrow, bcp_batch, etc.) to
> determine exactly which row/rows were not imported if an error
> occurs (like a Violation of Primary Key, etc.) With
> SQLBulkOperations it can be done with a status array, but I
> don't like the fact that I would have to read the entire table
> into memory and iterate it using SQLFetch/SQLFetchScroll to
> update/insert new rows using SQLBulkOperations. Any ideas
> appreciated.
We have the same problem and haven't found a reasonable solution.
- Arnie
|||"Arnie" <99yoda@.newsgroup.nospam> wrote in message
news:%23AwERXv5GHA.5108@.TK2MSFTNGP03.phx.gbl...
> We have the same problem and haven't found a reasonable solution.
Aggravating, isn't it? I made a little app that tried to limit the rows
required and fetch-scroll through them 2,000 at a time. For instance, if I
have a file with ID #s for each record in the file like 1, 2, 10, 100, 4000,
9999, then I only need to pull over the rows in the table with ID #s between
1 and 9999. It breaks down when I have a huge spread, like if the file only
had two rows, for ID #s 1 and 9999. Then I'm pulling 9997 rows across that
I don't need.
I am also playing with SELECTing only x (maybe 2,000?) rows at a time into
the cursor. As I go through the file above, I pull rows with ID #s 1 -
2000, then I pull 4000 - 5999 and finally jump up to 9999 - 11998 and that's
it. I'm still trying to find the "sweet-spot" where I'll get the best
efficiency for the fewest SELECTs. It seems like it is working and should
be pretty efficient as long as the flat file is sorted properly, but it adds
to the complexity by about a zillion percent, espcially if your primary key
is composed of multiple columns populated from the file...

ODBC API Question

Hi all,
Quick question about the ODBC API. Is there a function in the ODBC API that
I can call to determine what parameters a particular driver requires? For
instance, ODBC Administrator knows to allow you to select, create, repair or
compact an MS Access Database; but for SQL Server it offers different
options, including default database, use ANSI quoted identifiers, etc. Does
ODBC Administrator determine this via a function in the ODBC API, or is this
hard-coded into the ODBC Administrator?
Thanks,
Michael C.There are various API calls that you can make to figure this out,
ODBCAD32.EXE does this dynamically. Best is to use ODBC Test
t_overview.asp" target="_blank">http://msdn.microsoft.com/library/d...
t_overview.asp
that comes with the MDAC SDK or the Platform SDK, it allows you to use all
these APIs interactively from a UI, so you can play around with them,
otherwise they are all documented in the programmer reference guide.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Michael C" <michaelc@.nospam.org> wrote in message
news:QAXNc.16090$09.2465831@.news4.srv.hcvlny.cv.net...
> Hi all,
> Quick question about the ODBC API. Is there a function in the ODBC API
> that
> I can call to determine what parameters a particular driver requires? For
> instance, ODBC Administrator knows to allow you to select, create, repair
> or
> compact an MS Access Database; but for SQL Server it offers different
> options, including default database, use ANSI quoted identifiers, etc.
> Does
> ODBC Administrator determine this via a function in the ODBC API, or is
> this
> hard-coded into the ODBC Administrator?
> Thanks,
> Michael C.
>

ODBC API Question

Does anyone know if there's a way, using SQL Server ODBC Bulk Copy
extensions (bcp_bind, bcp_sendrow, bcp_batch, etc.) to determine exactly
which row/rows were not imported if an error occurs (like a Violation of
Primary Key, etc.) With SQLBulkOperations it can be done with a status
array, but I don't like the fact that I would have to read the entire table
into memory and iterate it using SQLFetch/SQLFetchScroll to update/insert
new rows using SQLBulkOperations. Any ideas appreciated."Mike C#" <xyz@.xyz.com> wrote in message
news:uK2jd9i5GHA.3444@.TK2MSFTNGP02.phx.gbl...
> Does anyone know if there's a way, using SQL Server ODBC Bulk
> Copy extensions (bcp_bind, bcp_sendrow, bcp_batch, etc.) to
> determine exactly which row/rows were not imported if an error
> occurs (like a Violation of Primary Key, etc.) With
> SQLBulkOperations it can be done with a status array, but I
> don't like the fact that I would have to read the entire table
> into memory and iterate it using SQLFetch/SQLFetchScroll to
> update/insert new rows using SQLBulkOperations. Any ideas
> appreciated.
We have the same problem and haven't found a reasonable solution.
- Arnie|||"Arnie" <99yoda@.newsgroup.nospam> wrote in message
news:%23AwERXv5GHA.5108@.TK2MSFTNGP03.phx.gbl...
> We have the same problem and haven't found a reasonable solution.
Aggravating, isn't it? I made a little app that tried to limit the rows
required and fetch-scroll through them 2,000 at a time. For instance, if I
have a file with ID #s for each record in the file like 1, 2, 10, 100, 4000,
9999, then I only need to pull over the rows in the table with ID #s between
1 and 9999. It breaks down when I have a huge spread, like if the file only
had two rows, for ID #s 1 and 9999. Then I'm pulling 9997 rows across that
I don't need.
I am also playing with SELECTing only x (maybe 2,000?) rows at a time into
the cursor. As I go through the file above, I pull rows with ID #s 1 -
2000, then I pull 4000 - 5999 and finally jump up to 9999 - 11998 and that's
it. I'm still trying to find the "sweet-spot" where I'll get the best
efficiency for the fewest SELECTs. It seems like it is working and should
be pretty efficient as long as the flat file is sorted properly, but it adds
to the complexity by about a zillion percent, espcially if your primary key
is composed of multiple columns populated from the file...

ODBC API Bulk Update Question

Hi all,
Quick question - I'm writing a program that uses the ODBC API to bulk load
data into SQL Server. I have nailed the Bulk Insert, no problems. Now I
need to use Update By Bookmark, but am having trouble. Does anyone know
where I can find ODBC Update By Bookmark samples? The SQL BulkOperations
sample application on MSDN is inadequate, incomplete and buggy.
ThanksNever mind, figured it out, works great.
"Michael C" wrote:
> Hi all,
> Quick question - I'm writing a program that uses the ODBC API to bulk load
> data into SQL Server. I have nailed the Bulk Insert, no problems. Now I
> need to use Update By Bookmark, but am having trouble. Does anyone know
> where I can find ODBC Update By Bookmark samples? The SQL BulkOperations
> sample application on MSDN is inadequate, incomplete and buggy.
> Thanks