Re: Oracle 8i question ... - Database Discussion Boards

1-Oct-01 6:58

Can anyone please help with a problem I have. I'm trying to use ADO code within an Access database to retrieve certain records from a table. when the code runs, it tells me that the database has been placed in state by user X that prevents it from being locked or edited. Do I have to open the current database I'm working on from code before I can use recordsets? Can anyone provide sample code to retrieve some data from table test? Thanks in advance

Problem returning ADO::_Connection from COM component to MFC client

AliBaba24-Sep-01 12:15

AliBaba

24-Sep-01 12:15

I have a COM method that returns a db connection pointer, as defined below
GetDBConnection(_Connection **pdbReturnValue)
{
.
.
.
*pdbReturnValue = m_pConnection.Detach();
return S_OK;
}

And my two test client are defined as follow
VB
Dim pMyInterface As New MyInterface
Dim pConn As New ADODB.Connection

Set pConn = pMyInterface.GetDBConnection
pConn->Execute "select * from rt_user", NULL, adCmdText

Set pConn = Nothing
Set pMyInterface = Nothing

----------------------------------------------------------------------------------
VC
CoInitialize(NULL);

HRESULT hResult;
CComPtr<imyinterface> pIMyInterface;
_ConnectionPtr pConnection;

hResult = pIMyInterface.CoCreateInstance(CLSID_MyInterface,
NULL, CLSCTX_LOCAL_SERVER);
pConnection = pIMyInterface->GetRealTimeDBConnection();
pConnection->Execute("select * from rt_user", NULL, adCmdText);
pConnection->Close();
pIMyInterface.Release();

CoUninitialize();

The problem occurs when trying execute the sql command in VC client.
The error message received is -> "A null reference pointer was passed to the stub"
This works without a hitch in VB. What am I doing wrong? or have left out? I
understand that VB does alot of the marshalling in the back end. In this case,
what would be the equivalent marshalling technique? Thanks in advance for
your help.

P.S. The component is running under Component Service Manager as a Server.

AliBaba

Re: Problem returning ADO::_Connection from COM component to MFC client

Rashid Thadha25-Sep-01 5:37

Rashid Thadha

25-Sep-01 5:37

use queryinterface to return the connectionptr

e.g.

m_pConnection->QueryInterface(__uuidof(_Command),(void **)pdbReturnValue);

instead of

*pdbReturnValue = m_pConnection.Detach();

in your

GetDBConnection com method

Cool | :cool:

Re: Problem returning ADO::_Connection from COM component to MFC client

AliBaba25-Sep-01 17:34

AliBaba

25-Sep-01 17:34

I have tried that also, but it didn't solve the problem.

I have also tried
CComPtr<_Connection> m_pConnection;
.
.
.
m_pConnection.CopyTo(*pdbReturnValue);
but it also failed to return the correct connection pointer.

ADO Named Parameters in ADO 2.6

jkgh18-Sep-01 5:43

jkgh

18-Sep-01 5:43

Contrary to the (hallowed) Bill Vaughn's article in SQL server mag for April 2001 and elsewhere I'm finding that it doesn't matter what you call the stored procedure Return Value parameter.

Is anyone else find the same?

eternal student
Give a man a fish and you feed him for a day; teach him to use the 'net and he won't bother you for weeks.

Oracle 8i question ...

Hadi Rezaee16-Sep-01 21:41

Hadi Rezaee

16-Sep-01 21:41

Hi all,
For creating database and table and field i must know SQL+ ?
Not any other way ?

My month article: Game programming by DirectX by Lan Mader.
Please visit in: www.geocities.com/hadi_rezaie/index.html

Hadi Rezaie

Re: Oracle 8i question ...

Ray Kinsella16-Sep-01 21:54

Ray Kinsella

16-Sep-01 21:54

Use the Schema manger provided with the client software,
its called 'DBA Studio'.

Regards

Ray

"Je Suis Mort De Rire"

Re: Oracle 8i question ...

Rashid Thadha16-Sep-01 23:40

Rashid Thadha

16-Sep-01 23:40

use the free version of toad (oracle equivalent to Enterprise Manager) from www.toadsoft.com

Top Oracle tool

MSDASQL & SyBase...

14-Sep-01 11:34

Attempting to connect to a Sybase backend using C#, OLE and ADO.NET. Using VC#.NET I can make the data connection using OLE DB Provider for ODBC (MSDASQL) but I get a 'System.NotSupportedException' when using MSDASQL.1 as the Provider when opening a connection. MSDN help states that "Support for the OLE DB Provider for ODBC (MSDASQL) is disabled."
Does anyone have any ideas on how to circumvent this?
Thanks

Calling SQL Server 2000 Experts - Can anyone provide inputs ?

ManaInfy14-Sep-01 0:02

ManaInfy

14-Sep-01 0:02

I am trying to do the following !

Open one session of Query Analyser Window

Begin Trans
Update Table1 Set ...

But I don't commit !

Open another session of Query Analyser Window

Select from Table1

Select gets Blocked unless Update is committed !

CAN'T I DO A SELECT WHEN UPDATES/INSERTS Occur ?

I Can do with NOLOCK but we don't want uncommitted data to appear !

I have tried with all transaction isolation levels.

Anyone can help ?

Thanks
Anand

ODBC and CRecordSet::Update

Eric MILLAUD13-Sep-01 22:21

Eric MILLAUD

13-Sep-01 22:21

Hi When i use the function CRecordSet::Update, i have an exception.
Besides, when i use the function Crecordset::MoveFirst, I don't read the first record.
Anyone can help me please.
I send you my code.
Thanks

try
{
/* APP->m_TmSet -> Open ();*/
CTmSet tmset;
if (tmset.IsOpen () ) tmset.Close () ;
tmset.Open();
MessageBox(NULL,"passe","passe3",MB_OK);
//if ( APP->m_TmSet -> CanAppend () )
if (tmset.CanAppend () )
{
MessageBox(NULL,"avant new","passe",MB_OK);
//tmset.AddNew();
tmset.MoveFirst();
tmset.Requery () ;
//tmset.Edit();
for (int n=0;n<20;n++)
{
char buf[10];
MessageBox(NULL,tmset.m_TM_NAME,"nom",MB_OK);
wsprintf(buf,"%ld",tmset.m_TM_ID);
MessageBox(NULL,buf,"id",MB_OK);
tmset.MoveNext();
}
int a=tmset.GetRecordCount();

wsprintf(buf,"%ld",a);
MessageBox(NULL,buf,"nb record",MB_OK);
/*tmset.m_BAY_CODE =TmSql->numBay ;
tmset.m_TM_ID =TmSql->numTm ;
tmset. m_TM_NAME=TmSql->libTm ;
tmset.m_TM_UNIT =TmSql->libUnit;
tmset.m_TM_ARCHIVE =TmSql->FlgArchive ;
tmset.m_TM_STATE_CURRENT =0;
tmset.m_TM_DATE_CURRENT =0 ;
tmset.m_TM_DATE_OK_CURRENT=0 ;*/

tmset.m_BAY_CODE =0 ;
tmset.m_TM_ID =0 ;
tmset. m_TM_NAME="a" ;
tmset.m_TM_UNIT ="b";
tmset.m_TM_ARCHIVE =0 ;
tmset.m_TM_STATE_CURRENT =0;
tmset.m_TM_DATE_CURRENT =0 ;
tmset.m_TM_DATE_OK_CURRENT=0 ;

if (tmset.CanUpdate () !=0)
{
MessageBox(NULL,"avant Update ","passe",MB_OK);
tmset.Update();
MessageBox(NULL,"après Update","passe",MB_OK);
}
if ( tmset.IsEOF ()!=0 )
tmset.MoveLast ();

tmset.Requery () ;
tmset.MoveLast () ;
MessageBox(NULL,"ajoute3","passe",MB_OK);
// UpdateData ( FALSE ) ;
}
else
AfxMessageBox ( "Can Not Append a Record" ) ;
}
catch( CDBException* e )
{
AfxMessageBox( e->m_nRetCode,
MB_ICONEXCLAMATION );
// Delete the incomplete recordset object
/* delete m_pSet;
m_pSet = NULL;*/
e->Delete();
}

Re: ODBC and CRecordSet::Update

Rashid Thadha13-Sep-01 22:35

Rashid Thadha

13-Sep-01 22:35

You need to call AddNew or Edit Methods before you call Update. Your code currently has them commented out.

Access to SQL Server synchronization

Craig Miller13-Sep-01 7:12

Craig Miller

13-Sep-01 7:12

Hi,

I'm trying to synchronize a client side Access database with a backend SQL server database. I've combed through the MSDN and all I can find if Replication. Does anyone know if it's possible to synchronize an Access database with SQL Server?

Any help would be greatly appreciated.

Thanks,

Craig

Re: Access to SQL Server synchronization

11-Oct-01 22:18

Yes it is possible.
But you ave 2 choices.

Your access database is an ADP project with the MSDE engine and it's like a réplication between 2 SQL server databases.

If your access database is a Jet, your publication database must generate a character base snapshot.

Then in your Access database , you have to go in the tools menu then replication then pull subscription and follow the indications

I can't find IT

Bigge13-Sep-01 5:04

Bigge

13-Sep-01 5:04

This code aint working and i dont no why, anyone who can look at it an see i they find somesthing wrong.

The Procedure is:
Create procedure AdoTestA"
(@pkMbtID varchar(50))"
as
INSERT INTO MBT (pkMbtID, MbtType) Values (@pkMbtID, '122')
return

The Code:
_bstr_t bstr (L"Provider=SQLOLEDB.1; Data Source=M02WS-17-051;Initial Catalog=TEMC_MEASSQL;uid=sa");

pCon.CreateInstance(__uuidof(ADODB::Connection));
pCommand.CreateInstance(__uuidof(ADODB::Command));
Con->Open(bstr, (BSTR)NULL, (BSTR)NULL, -1);
pCommand->ActiveConnection = pCon;
pCommand->CommandText = bstrSP;
pCommand->CommandType = ADODB::adCmdStoredProc;

pCommand->Parameters->Append(pCommand->CreateParameter( L"@pkMbtID",ADODB::adVarChar, ADODB::adParamInput, 50, _variant_t("Smith")));
_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
pCommand->Execute( &vNull, &vNull, ADODB::adCmdUnknown);

I can compile it but when execute it say abnormal program.....

MVH
Bigge

Re: I can't find IT

Rashid Thadha13-Sep-01 12:22

Rashid Thadha

13-Sep-01 12:22

put some exception handling round it, atleast this way it won't crash the program

try
{
}
catch(_com_error e)
{
TRACE("Erroe %s", (LPCSTR)e.description());
}
catch(...)
{
}

also instead of using &vNull, use vtMissing variable which is define in the ado headers.

Re: I can't find IT

Bigge13-Sep-01 17:04

Bigge

13-Sep-01 17:04

From now I will always use "Try and catch" even with small and simple ADO connections, i found the problem.
Thanks Rashid

This was very useful:

catch( _com_error &e){
_bstr_t bstrSource(e.Source());
_bstr_t bs = _bstr_t(" Error: ") + _bstr_t(e.Error()) + _bstr_t(" Msg: ")
+ _bstr_t(e.ErrorMessage()) + _bstr_t(" Description: ")
+ _bstr_t(e.Description());

MessageBox(0,bs,bstrSource, MB_OK);

How to use ORDER by clause

Chris Meech13-Sep-01 3:41

Chris Meech

13-Sep-01 3:41

Ordinarily I would create a view to solve this problem, but I'm unable to do that in this case because of parameters supplied to the WHERE clause. I have a query;

select fielda,
       fieldb,
       fieldc
  from viewA
 where date_field between Date1 and Date2
union
select fielda,
       fieldb,
       fieldc,
  from viewB
 where date_field < Date2
union ... 3 more similar select/union

I need to have the resultant rows ordered in a particular sequence and so normally I would create a view, query the view and order the results of that query. In this instance, because of how the Date1 and Date2 are supplied, I can not use a view.
Is there any way to ORDER the entire resultant set of rows when you use a union of multiple queries.

Thanks.
Chris

Re: How to use ORDER by clause

Carlos Antollini13-Sep-01 3:59

Carlos Antollini

13-Sep-01 3:59

Use a SubQuery.
Select Field1, Field1 From (query with Unions) Order By Field1

Cheers!!!!

Carlos Antollini.

Re: How to use ORDER by clause

Chris Meech13-Sep-01 4:26

Chris Meech

13-Sep-01 4:26

Thanks for the response, Carlos. I'm using Oracle 8I and had tried to use that but continually got errors from the parser. Once I put the brace characters around the query with the unions, it worked like a champ. None of my books even gave an example such as yours. You helped me out a lot.

Thanks again.

Chris

SQL Server 2000 and Windows 98

Jesper S8-Sep-01 12:49

Jesper S

8-Sep-01 12:49

Hi,

I´m connecting to a SQL Server 2000 placed on a MS Windows 2000 Server via LAN from a MS Windows 98 computer. My program (MS VC++ 6.0, ADO, OLE DB)connects OK, but eventually crashes and also adds 2 mysterious characters in different fields in the database.
If I run the program from a MS Windows 2000 computer via LAN-network everything works perfectly!

What is wrong/different with the MS Windows 98 computer???? I´ve tried almost everything - installing MDAC 2.6 RTM, DCOM98 1.3 and so on... on the MS Windows 98 computer. Nothing helps!?!

I sure hope someone is able to help me out!

Jesper Smedegaard

SQL query troubles: SELECT [TOP x STARTING AT n]

David Wulff7-Sep-01 6:20

David Wulff

7-Sep-01 6:20

I need to be able to do the following using an SQL statement:

SELECT TOP 25 * FROM [Table] WHERE [Condition] ORDER BY [Column]

Except I want to be able to specify the start position, i.e. start n rows into the table and then extract the 25 rows I need.

I don’t know how many rows will be in the table, and there is no field that has any form of incremental value (so I can’t just say [Condition] = [Table].[Column] > n).

The table might contain many thousands of rows, so it is unfeasible to retrieve them all then ignore the one’s I don’t want.

So, I guess my question is how can I retrieve x rows from the table, starting from row n, based on the results of my ORDER BY clause.

This must be possible, surely?

David Wulff
dwulff@battleaxesoftware.com

Re: SQL query troubles: SELECT [TOP x STARTING AT n]

Not Active7-Sep-01 11:33

Not Active

7-Sep-01 11:33

I know you can use SET ROWCOUNT to limit the number of effected rows. I'm not sure if there is anything to set the start position. Interesting question though.

Re: SQL query troubles: SELECT [TOP x STARTING AT n]

Anders Molin10-Sep-01 3:04

Anders Molin

10-Sep-01 3:04

You can not do that, sorry. (I have also needed it)

- Anders

Money talks, but all mine ever says is "Goodbye!"

Re: SQL query troubles: SELECT [TOP x STARTING AT n]

David Wulff10-Sep-01 8:41

David Wulff

10-Sep-01 8:41

I had guessed as much Frown | :(

Why oh why did the original designers of SQL not think people would need that functionality? It's common sense if you just want to retrieve, say, 20 rows from a fifty million row table, that you would rather not return the first 49,999,980 only to ignore them.

David Wulff
dwulff@battleaxesoftware.com

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.