ODBC driver for SQL server using mfc

Hi,
I am currently using ORACLE 9i but I am trying to move to SQL server.
Below is my code, I am getting very strenge error, I can retrieve all the
data from database, but when I do .Edit(), the code crashes. Eventhough, I
am opening the recordset dynaset, it still does not let me edit the
recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
version 3.520.9030.0). Is my ODBC drive old?
******************my code*********************************
m_compInStream = new CComponentsInStream(Database);
m_compInStream->Open(CRecordset::dynaset,"select * from
tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
COMPONENTSID",CRecordset::none);
m_compInStream->Edit(); //Crashes here...
************************************************** ***********
Your SQL Server version is the SQL 8.0 RTM version. I beleive the latest is
SP4, so you could try a newer one. Try
http://www.microsoft.com/downloads/r...etext=sql+serv
er&DisplayLang=en
You could also try doing a search on http://msdn.microsoft.com/ using
keywords "ODBC MFC". Perhaps there is some difference between Oracle and
SQL that doesn't allow this query.
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: ODBC driver for SQL server using mfc
| Date: Tue, 13 Sep 2005 00:18:07 -0500
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <uPipLKCuFHA.1028@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2700
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am currently using ORACLE 9i but I am trying to move to SQL server.
| Below is my code, I am getting very strenge error, I can retrieve all the
| data from database, but when I do .Edit(), the code crashes. Eventhough, I
| am opening the recordset dynaset, it still does not let me edit the
| recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
| version 3.520.9030.0). Is my ODBC drive old?
|
| ******************my code*********************************
| m_compInStream = new CComponentsInStream(Database);
| m_compInStream->Open(CRecordset::dynaset,"select * from
| tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
| COMPONENTSID",CRecordset::none);
| m_compInStream->Edit(); //Crashes here...
| ************************************************** ***********
|
|
|
|

ODBC driver for SQL server using mfc

Hi,
I am currently using ORACLE 9i but I am trying to move to SQL server.
Below is my code, I am getting very strenge error, I can retrieve all the
data from database, but when I do .Edit(), the code crashes. Eventhough, I
am opening the recordset dynaset, it still does not let me edit the
recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
version 3.520.9030.0). Is my ODBC drive old?
******************my code*********************************
m_compInStream = new CComponentsInStream(Database);
m_compInStream->Open(CRecordset::dynaset,"select * from
tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
COMPONENTSID",CRecordset::none);
m_compInStream->Edit(); //Crashes here...
****************************************
*********************Your SQL Server version is the SQL 8.0 RTM version. I beleive the latest is
SP4, so you could try a newer one. Try
http://www.microsoft.com/downloads/...eetext=sql+serv
er&DisplayLang=en
You could also try doing a search on http://msdn.microsoft.com/ using
keywords "ODBC MFC". Perhaps there is some difference between Oracle and
SQL that doesn't allow this query.
| From: "Bhavin Patel" <bpatel@.epcon.com>
| Subject: ODBC driver for SQL server using mfc
| Date: Tue, 13 Sep 2005 00:18:07 -0500
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <uPipLKCuFHA.1028@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: h86.74.29.71.ip.alltel.net 71.29.74.86
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2700
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hi,
| I am currently using ORACLE 9i but I am trying to move to SQL server.
| Below is my code, I am getting very strenge error, I can retrieve all the
| data from database, but when I do .Edit(), the code crashes. Eventhough, I
| am opening the recordset dynaset, it still does not let me edit the
| recordset. I have SQL server(8.00.194), MDAC(2.71.9030.0), ODBC32.dll(
| version 3.520.9030.0). Is my ODBC drive old?
|
| ******************my code*********************************
| m_compInStream = new CComponentsInStream(Database);
| m_compInStream->Open(CRecordset::dynaset,"select * from
| tblComponentsInStream where FLUIDSTREAMSID = '12' ORDER BY
| COMPONENTSID",CRecordset::none);
| m_compInStream->Edit(); //Crashes here...
| ****************************************
*********************
|
|
|
|sql

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

Obtaining the last record

Let's say I need to be able to dynamically retrieve the last record in a table (ChData) in order to be able to continuously monitor the flow of data. Since theoretically there is no such thing defined in SQL, let's say I have an index column - ChTimestamp - (which, for some odd reason, is a DOUBLE), which grows monotonically. Thus, I want the row with the largest ChTimestamp value. The data columns would be Ch0, Ch1, and so on. Let's also assume that for some reason I am limited to using the LabVIEW database connectivity toolkit and because of that, I don't have full use of the SELECT statement but I have to supply the table and column names for the "SELECT FROM " and only the WHERE and the like predicates can be freely specified (I don't know how freely). What would be an elegant query to achieve the desired result? Thanks!
Kamenelegant query to achieve your result:select phonenumber
from dbms_vendors
where dbms in ('sql server','oracle','mysql','postgresql','sybase','db 2')
sorry, couldn't help it

:cool:|||select * from chdata
where
chtimestamp=(select max(chtimestamp) from chdata)

or am I missing something?

Alternatively you could write a trigger to update a known row in another version of the table with a single row so you can have a simpler where clause.

select * from mystupidtable where x='1'|||SELECT TOP 1 * FROM Table1 ORDER BY chTimestamp DESC|||Thank you very much, LoztInSpace and Peso. No it's not a homework, I'm a professional, it's just that I have to deal with so many different technologies, programming languages, APIs, etc., that sometimes it's just easier (not to mention quicker) to ask those who deal with it every day instead of trying to jump start my fried brain. :o

Kamen :angel:|||Maybe it is time to make your niche?|||Actually, small companies appreciate multivalent engineers like me. Plus, I'm too old to change. :cool:
Kamen

Obtaining Procedure Declaration

Where does SQL Server 2000 store the definition of stored procedures and
views? Is there a system table that I can query and retrieve the definition
,
like it shows up when scripting from the QA Object Browser?Kevin Bowker wrote:
> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the definiti
on,
> like it shows up when scripting from the QA Object Browser?
syscomments table contains full text of views, procedures and other objects.|||One of my colleagues apparently does better footwork than I do and came up
with this for those who may be interested:
select routine_definition
from information_schema.routines
where routine_type='Procedure'
"Kevin Bowker" wrote:

> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the definiti
on,
> like it shows up when scripting from the QA Object Browser?|||I would use sp_helptext in 2000 and previous. This will account for
procedures larger than 8K that span multiple rows in syscomments and
INFORMATION_SCHEMA.ROUTINES.
In SQL Server 2005, you can use the new OBJECT_DEFINITION() function, or the
new catalog view sys.procedures
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:73D65E14-A411-4FD7-96FB-A85CAC02EF5F@.microsoft.com...
> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the
> definition,
> like it shows up when scripting from the QA Object Browser?

object_id function returns null

Hello,
I've been using object_id(object_name) function in some queries to retrieve
tables' ids without problems. However, those queries have started to fail
because object_id function returns null when the table name is like this one:
'MyPrefix.SampleTable'. I've checked sysobjects and the table exists there
and it has a valid id.
Any idea how to solve this? What are my options? Should I rewrite my queries
and use sysobject.name to perform JOINs?
I'm using SQL Server 2000.
Thanks in advance.Thank you very much for you help. It works as you said.
I'm experiencing the issue in a customer's database.
Do you think it's safe to replace every call to object_id(table_name) with
object_id('"' + table_name + '"') or do you think I could face any collateral
effect?
Thanks again.
"Tibor Karaszi" wrote:
> Is "MyPrefix" part of the table name or is it the owner of the table. If the former, you need to
> quote it in OBJECT_ID, just the same way as when you refer to the table in a SELECT statement. But
> the recommendation is to stick within the limits of standard identifiers when you name objects
> (which among other thing doesn't allow a dot in the name):
> CREATE TABLE "My.Table"(c1 int)
> --Returns NULL, SQL Server assumes "My" is the owner
> SELECT OBJECT_ID('My.Table')
> --Returns the object id
> SELECT OBJECT_ID('"My.Table"')
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jos G" <JGeer@.nospamplease.onobox.com> wrote in message
> news:75E956AC-BC7F-4A77-A149-3C0AD2B3E220@.microsoft.com...
> > Hello,
> >
> > I've been using object_id(object_name) function in some queries to retrieve
> > tables' ids without problems. However, those queries have started to fail
> > because object_id function returns null when the table name is like this one:
> > 'MyPrefix.SampleTable'. I've checked sysobjects and the table exists there
> > and it has a valid id.
> >
> > Any idea how to solve this? What are my options? Should I rewrite my queries
> > and use sysobject.name to perform JOINs?
> >
> > I'm using SQL Server 2000.
> >
> > Thanks in advance.
>|||Is "MyPrefix" part of the table name or is it the owner of the table. If the former, you need to
quote it in OBJECT_ID, just the same way as when you refer to the table in a SELECT statement. But
the recommendation is to stick within the limits of standard identifiers when you name objects
(which among other thing doesn't allow a dot in the name):
CREATE TABLE "My.Table"(c1 int)
--Returns NULL, SQL Server assumes "My" is the owner
SELECT OBJECT_ID('My.Table')
--Returns the object id
SELECT OBJECT_ID('"My.Table"')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jos G" <JGeer@.nospamplease.onobox.com> wrote in message
news:75E956AC-BC7F-4A77-A149-3C0AD2B3E220@.microsoft.com...
> Hello,
> I've been using object_id(object_name) function in some queries to retrieve
> tables' ids without problems. However, those queries have started to fail
> because object_id function returns null when the table name is like this one:
> 'MyPrefix.SampleTable'. I've checked sysobjects and the table exists there
> and it has a valid id.
> Any idea how to solve this? What are my options? Should I rewrite my queries
> and use sysobject.name to perform JOINs?
> I'm using SQL Server 2000.
> Thanks in advance.|||> Do you think it's safe to replace every call to object_id(table_name) with
> object_id('"' + table_name + '"') or do you think I could face any collateral
> effect?
It depends. If the "object_name" part has the owner in it, you can't just quote the whole lot, you'd
have to quote each part:
CREATE TABLE "theTable"(c1 int)
--Returns object id
SELECT OBJECT_ID('"theTable"')
--Returns NULL
SELECT OBJECT_ID('"dbo.Table"')
--Should be
SELECT OBJECT_ID('"dbo"."Table"')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jos G" <JGeer@.nospamplease.onobox.com> wrote in message
news:6311228F-E9CD-4304-A3EE-292FAD6E6FFA@.microsoft.com...
> Thank you very much for you help. It works as you said.
> I'm experiencing the issue in a customer's database.
> Do you think it's safe to replace every call to object_id(table_name) with
> object_id('"' + table_name + '"') or do you think I could face any collateral
> effect?
> Thanks again.
>
> "Tibor Karaszi" wrote:
>> Is "MyPrefix" part of the table name or is it the owner of the table. If the former, you need to
>> quote it in OBJECT_ID, just the same way as when you refer to the table in a SELECT statement.
>> But
>> the recommendation is to stick within the limits of standard identifiers when you name objects
>> (which among other thing doesn't allow a dot in the name):
>> CREATE TABLE "My.Table"(c1 int)
>> --Returns NULL, SQL Server assumes "My" is the owner
>> SELECT OBJECT_ID('My.Table')
>> --Returns the object id
>> SELECT OBJECT_ID('"My.Table"')
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jos G" <JGeer@.nospamplease.onobox.com> wrote in message
>> news:75E956AC-BC7F-4A77-A149-3C0AD2B3E220@.microsoft.com...
>> > Hello,
>> >
>> > I've been using object_id(object_name) function in some queries to retrieve
>> > tables' ids without problems. However, those queries have started to fail
>> > because object_id function returns null when the table name is like this one:
>> > 'MyPrefix.SampleTable'. I've checked sysobjects and the table exists there
>> > and it has a valid id.
>> >
>> > Any idea how to solve this? What are my options? Should I rewrite my queries
>> > and use sysobject.name to perform JOINs?
>> >
>> > I'm using SQL Server 2000.
>> >
>> > Thanks in advance.
>>|||So the name of the table if MyPrefix.SomeTable, therefore MyPrefix is
not the owner right ? Normally, it is supposed to be the owner rather
than part of the name. Anyway, try to use the command with putting the
name in brackets.
OBJECT_ID('[MyPrefix.SampleTable]')
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--