|
...if you'll excuse the topic line ...
what a day- sifted through miles of docs, to still be stuck on this one:
I have a stored procedure sp_ProductRegionalDetails wich takes 2 arguments- @productID and @regionID. This will return a row containing the details of that product, localized (brand etc.) to that region. The thing is, if it does not find an entry for that region, it will search the wider 'parent' region of @regionID, and so-on. Works a treat.
I now need a rowset containing all of the products (their details) in a region. I have a column of distinct ProductIDs and a single RegionID. I need to call sp_ProductRegionDetails with each ProductID and the RegionID, and append the results somehow to the others...
Did I explain my problem..?
thanks for any help
Nick
|
|
|
|
|
yeah baby
|
|
|
|
|
Did you tried to use a temporary table or recursion? I am sorry that I cannot offer you more details since I don't know the specifics, but here is how I'd wrote it:
(1) Recursion. This assumes that your region table cannot find a loop (from a region parent you cannot get back to the same region parent).
Anyway, if this happens, select into a temporary table all the regions you can find starting with a parent, eliminate duplicates and you'll get a table containing the parent region candidates (either our passed ID or an ancestor), and also you can create another temporary table with all the details you need joining the #parents with #products (or @productID, if only a product interests you).
Create Procedure sp_ProductRegionalDetails
@productID int,
@regionID int
As
Declare @lRetVal int
Select @lRetVal = 1
Declare @fExitLoop Bit
Select @fExitLoop = 0
Declare @LoopProductID int
Declare @LoopRegionID int
Select @LoopProductID = @productID, @LoopRegionID = @regionID
While (@fExitLoop = 0)
Begin
Select [... Product regional details ...] From [... Tables, Joins ...] Where
ProductIdField = @LoopProductID And
RegionIdField = @LoopRegionID And
And [... additional conditions, etc ...]
If [The field interesting for me are retrieved]
Begin
Select @fExitLoop = 1
Select @lRetVal = 0
End
Else
Begin
Select @LoopRegionID = Exec sp_ParentRegion @LoopRegionID
If @LoopRegionID Is Null
Begin
Select @fExitLoop = 1
End
End
End
Return @lRetVal
(2) Temporary tables.
Create Procedure sp_ProductRegionalDetails
@productID int,
@regionID int
As
Declare @lRetVal int
Select @lRetVal = 1
[Create a temporary table containing all regions]
-- Create Table #regions (regionID int, ToSelect bit default(0) ) - for example
[Populate this table from our starting point: @regionID - the table will be populated in the natural order, i.e. starts with our regionID, if will find something then return, if not advance to the next parent etc.]
[Eliminate duplicated regionID]
[Query temporary table by joining with @productID]
[Retrieve results and mark in the temporary table ToSelect = 1 where informations you need were found]
[For the first entry in the temporary table with ToSelect = 1 get this row; this is our first row]
[If all the rows in temporary table have ToSelect = 0 --> no results found and return failure]
[If at least one row has ToSelect = 1, return the results associated with this regionID]
Return @lRetVal
If you need all the possible results, I suggest you to create (C++ mode) the temporary table from code, use in sp, read results after sp and drop tabel after. Something like:
VOID GetRegionInfo(DWORD *pdwResultCount, LP_PRODUCTRESULTS *ppResult)
{
CreateTemporaryTable(szTempTableName);
CallSP(szStoredProc, szSPName, pParams);
SelectResultsFromTemporaryTable(pdwResultCount, ppResult);
DropTemporaryTable(szTempTableName);
}
All the best,
Sardaukar
|
|
|
|
|
I'm very new to ADO... The problem I have is I can easily read an Access2000 database, but I can't write to it. I do an AddNew, then update the bound classes memver variables, and then call Update(). As soon as I call update, I get a crash. Please anyone got any ideas why? Here is the code it use...
<br />
_bstr_t strCnn(DSN_CONNECT_STRING);<br />
<br />
HRESULT hr = S_OK;<br />
IADORecordBinding *picRsPatient = NULL;
<br />
try<br />
{<br />
TESTHR(m_pConnection.CreateInstance(__uuidof(Connection)));<br />
m_pConnection->Open(strCnn,"","",adConnectUnspecified);<br />
<br />
TESTHR(m_pRstPatient.CreateInstance(__uuidof(Recordset)));<br />
m_pRstPatient->Open("Patient", <br />
_variant_t((IDispatch *)m_pConnection,true), adOpenKeyset,<br />
adLockOptimistic, adCmdTable);<br />
<br />
TESTHR(m_pRstPatient->QueryInterface(<br />
__uuidof(IADORecordBinding),(LPVOID*)&picRsPatient));<br />
TESTHR(picRsPatient->BindToRecordset(&m_rsPatient));<br />
<br />
}<br />
<br />
catch(_com_error &e)<br />
{<br />
PrintProviderError(m_pConnection);<br />
PrintComError(e);<br />
}<br />
<br />
m_pRstPatient->AddNew();<br />
strcpy(m_rsPatient.m_szPatientName, "James bond");<br />
strcpy(m_rsPatient.m_szPatientNumber, "007");<br />
m_pRstPatient->Update();<br />
<br />
if(m_pRstPatient)<br />
m_pRstPatient->Close();<br />
if(m_pConnection)<br />
m_pConnection->Close();<br />
Thanks
|
|
|
|
|
Cant Point out Exactly what the error is.I Had a similar error.
Check If the Database has any constraints in it(foreign etc).
Also check if recordset parameters are supported by the database.
|
|
|
|
|
I found what is was. The database was empty - it had no records in it at all. Supposedly you need a dummy record for it to work
|
|
|
|
|
I use VFP98 open a database named abc.dbf,only spend 5 second include show all the data in database.
But,when I use ODBC API open the abc.dbf,and show all the data ,It spend much more times,and
make the computer shutdown!!(There is not enough memory!!)
Thanks!
hi
|
|
|
|
|
how are you using the odbc stuff?
you posting doesnt give enough details to say much about what is causing the problems
---
"every year we invent better idiot proof systems and every year they invent better idiots"
|
|
|
|
|
Sorry,the following is the detail!
Notice: The abc.dbf has 500 million records!!!!
The code is too long,I can only give you virtual code!(Using VC++ with ODBC API)
1.SQLDriverConnect(); //connect the database abc.dbf .
2.SQLSetStmtOption(...SQL_CURSOR_DYNAMIC);//Create a scrollable cursor.
3.SQLExecDirect(); //Execute SQL directly.(Select * from tablename)
4.SQLNumResultCols(); //Get selected column numbers.(The fields count)
5.SQLDescribeCol(); //Save column describe in an object array.
6.SQLBindCol(); //Bind every column(fields) with a variable which described in step 5.Saved in an object array.
7.SQLFetchScroll(...SQL_FETCH_LAST);//Move cursor to last record,to get selected records count!It related to step 6.
8.Use ListView show the records data which saved in an object array in setp 6.
Notice: whith the 500 million records.
Setp 1 to 6 spend a few second,but in step 7,it take much more time ,and make my
compter shutdown because there is not enough memory!
Thanks!
hi
|
|
|
|
|
Why can't you just use "select count(*) from ..." instead of traversing 500 million records?
Tomasz Sowinski -- http://www.shooltz.com.pl
|
|
|
|
|
Thanks!
You can resolve the record numbers of the table,but how to show every record data much more fast like VFP98.
In my way,I use SQLFetchScroll(...SQL_FETCH_NEXT) to get and show record data one by one,It takes much more time!I want to make it fast,how can I do?
hi
|
|
|
|
|
You have to use some caching scheme. You simply can't store 500 million records from your table in RAM. Read records in the background thread to improve UI response time.
Tomasz Sowinski -- http://www.shooltz.com.pl
|
|
|
|
|
Thanks!
I will try it,and then talk about it!
hi
|
|
|
|
|
But today I use SQL Server open the same .dbf(conver to .mdb),the SQL Server can not locate the last record in time.But VFP locate the last record in a few seconds.Somebody tell me than VFP use a special technology ,such as RUSHMORE.....
Who can tell me how about RUSHMORE?What is the relationship with the SQL?
hi
|
|
|
|
|
Does anyone know how to pass parameters into an sql command?
Either prompting or just straight in?
Sample code or KB would be very useful.
Thankyou verymuch
Richard.
|
|
|
|
|
what I know is you can difine a string variable as shown below
dim SqlSt as string
dim Db as ADODB.Connection
db= new ADODB.connection
Sqlst ="INSERT INTO TABLE1, Name VARCHAR(10),Surname, VARCHAR(1)"
db.execute slqst
|
|
|
|
|
Does anyone know how to pass parameters into an sql command?
Either prompting or just straight in?
Sample code or KB would be very useful.
Thankyou verymuch
Richard.
|
|
|
|
|
Hello,
I can't find an elegant solution for the following using ASP2.0 with MS SQL 7.0 SP2:
I would like to select the last 10 rows of a query without using RecordSet.MovePrevious nor using FETCH
anyone has an idea ?
Thanks,
Steven
|
|
|
|
|
if you have a key that can be used for a sort order (ORDER BY) then you can say something like:
SELECT TOP 10 whatever FROM whatever ORDER BY key DESC
it will give the first 10 items entered into the table, ie, the bottom 10 of the recordset
---
"every year we invent better idiot proof systems and every year they invent better idiots"
|
|
|
|
|
Thanks Lauren,
I figured that out myself too
The problem is that I want the last ten rows displayed in ascending order without having to use RecordSet.Previous.
The recordset should contain the last ten rows (I know the value of last item in the table regarding to the query) in ascending order so I can display them using
while vRS.EOF = False Then
<display vrs.fields("value").value="">
vRs.MoveNext
...and I don't want to swim through a big recordset using FETCH...
any ideas ?
Thanks,
Steven
|
|
|
|
|
are you processing the results on the server before display? if so you could do the previous suggestion and simply run a flip algorithm on the results set in memory
reasons like this stop me using recordset objects
---
"every year we invent better idiot proof systems and every year they invent better idiots"
|
|
|
|
|
Hello,
Maybe this will help you.
SELECT a.member_no
FROM adult AS a
WHERE a.member_no IN
(SELECT TOP 10 b.member_no
FROM adult AS b
ORDER BY b.member_no desc)
ORDER BY a.member_no
It's not elegant.
It works!!
Edwin.
|
|
|
|
|
Hi there,
I was wondering if there is anyway to set the size of SQL Server log files that you can see under the "Management" folder.
thanks
|
|
|
|
|
I've created an MFC application using DAO to access a DBASE database. It works great on my system, but when trying to run the MFC application on a clean win98 system, i get a
"Unable to initialize DAO/Jet db engine." error. Does anyone know how i can fix this?!?! I've tried installing the newest version of MDAC...but this did'nt help.
|
|
|
|
|
I have installed MDAC 2.5 on a development machine and am trying to use the OLE DB consumer templates to access an Oracle database. Oddly it seems that I also have to have installed the Oracle client in order to get this to work. Does anyone have an example of how open a datasource using the microsoft OLE DB provider. I'm using the following, but it will only work if the Oracle client is installed also
HRESULT hr;
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
// dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("PASSWORD"));
// dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("USERID"));
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("DB_NAME"));
// dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_COMPLETE);
// dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR(""));
// hr = db.Open(_T("OraOLEDB.Oracle.1"), &dbinit);
hr = db.Open(_T("MSDAORA.1"), &dbinit);
Any comments are welcome. Thanks.
Chris
|
|
|
|