|
Spawn@Melmac wrote: could this be integrated into the ACCESSOR class mechanism I am using at present?
I have no idea what an Accessor is; a bit of explanation and some code would be nice.
Spawn@Melmac wrote: So when I came across the @@IDENTITY I thought I was sorted
That should return the last identity, and it should be possible to execute two SqlCeCommand [^] consecutively.
Something similar to the code below;
using (var con = new SqlCeConnection(connectionString))
using (var cmd = new SqlCeCommand())
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "INSERT ...";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "SELECT @@IDENTITY";
object recordId = cmd.ExecuteScalar();
}
How does an Accessor work? Is it used to execute query's?
I are Troll
|
|
|
|
|
Ok here you go but I should warn you I don't fully understand it myself.
I think of an accessor as a wrapper to the SQL like this (which is an extract from working code)...
class CDBHubDetails
{
public:
TCHAR f_LocSub[255];
int f_HubID;
BEGIN_COLUMN_MAP(CDBHubDetails)
COLUMN_ENTRY(1, f_LocSub)
END_COLUMN_MAP()
BEGIN_PARAM_MAP(CDBHubDetails)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(1, f_HubID)
END_PARAM_MAP()
DEFINE_COMMAND_EX(CDBHubDetails, L" \
SELECT Hub.SubLoc \
FROM Location \
WHERE Hub.id = ?")
};
Now this get's used as follows
CCommand<CAccessor<CDBHubDetails > > rs;
rs.f_HubID = pHub->m_iHubID;
hr = rs.Open(m_oDB->session);
where I can then walk the recordset.
The thing is I have not figured out how to process the additional SELECT command I need to include in the INSERT operation so I can retrieve the ID of the item inserted.
Now I know I am being both optemistic, and lazy, but I was hoping that as the INSERT does not return a recordset itself, the SELECT @@IDENTITY would become the result. Silly me...
The responses thus far have given me some ideas but finding examples I can learn from is proving difficult. MSDN seems devoid of C++ examples in the documentation (although F# is there!).
Thank you for taking the time to look at this for me.
Alan
|
|
|
|
|
Doesn't ring a bell, I'm afraid. Still, executing both commands one after another should do the trick - without a need to combine them. @@IDENTITY should hold it's value until the next INSERT -statement is issued.
I are Troll
|
|
|
|
|
Dear,
I'm looking for a good comparison between ODP.Net and OleDb for Oracle.
So far on google I only found contradictory information on forums where one claims that ODP is faster while the other claims the opposite.
Why would you use ODP over OleDb or vice versa?
Additionally I know that OleDb uses the MDAC (now Windows DAC) layer to talk to the database (correct?), is this also the case for ODP?
many thanks.
V.
|
|
|
|
|
ODP is good. Unless you have differences between your development and production computer. ODP is different for 32 and 64 bit for some weird reason.
Other problem with ODP is that you have to dispose everything and I really mean everything you have created or your server would be down after some duration.
Other than that, I believe it is fine. I do not have a material to read though. This is just by the personal experience of.
|
|
|
|
|
d@nish wrote: Other problem with ODP is that you have to dispose everything and I really mean everything you have created or your server would be down after some duration.
What do you mean exactly, clean up the resources?
V.
|
|
|
|
|
ODP uses a lot of unmanaged code so unless you dispose every object, they will be lying around like forever.
Make sure you dispose every ODP object. For connection, close and then dispose. For command, dispose each parameter explicitly.
|
|
|
|
|
How do we design/plan for using in-memory data to modify and search extensively? what are it's pros cons?
|
|
|
|
|
FEMDEV wrote: what are it's pros cons?
Stating the obvious first; memory is faster, but volatile. Loose the power, you loose the data.
What kind of data? Are you talking about a typical database, flat files, a folder-hierarchy filled with Word-documents and indexed with Google Desktop Search, or something completely different?
In all cases, I'd say that you don't want to move your entire database to a RAM-disk. If you "must", then move only the most frequent read data there - providing an extra cache-layer should prove to be easier than having a buffer in memory that needs be synchronized to disk.
Now that's all been said, have a look at Velocity[^]
I are Troll
|
|
|
|
|
Hi,
How do you write a SQL statement to update records if the count of a field is greater than 1?
Thanks
|
|
|
|
|
update mytable set column='value'
where id in (select id from mytable group by id having (count(id)>1))
Hope it will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Somebody has been kind enough to foresee such question; have a look here[^].
FYI: I found the link by Googling SQL update
EDIT
unless count isn't the name of a field, and you intended to count some rows and use that as a selection criterium... Then look at the other reply.
/EDIT
|
|
|
|
|
try something like this ...
Update myItems<br />
set myField = 'more than 1 count'<br />
where item_class in <br />
(<br />
select item_class<br />
from myItems<br />
group by item_class<br />
having count(*) > 1<br />
)
|
|
|
|
|
Thanks guys. Just what I was looking for.
|
|
|
|
|
Then mark as answer.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi,
I have a large varbinary field in one of my tables, and I would like to download in parts for show a download progress indicator in my application.
How can I split the data sent in a SELECT query?
Thanks
|
|
|
|
|
What do you mean with
solomon85 wrote: split the data sent in a SELECT query?
?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I think he means to retrieve it in chunks so as to be able to display a percentage meter.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
hi all
i want sql statement to delete and drop all my procedures in a data base in sql server 2005
how i can do that?
thanks for all
|
|
|
|
|
Does that include the stored proc you want that deletes all of the stored procs?
You need to do this drop procedure [proc_name] to drop a stored proc.
Create one line for each procedure unless you want to get all of the stored procs without knowing in advance what they are called. I'll leave you to work that one out. Hint: sysobjects
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
to delete all the procedure in your specified database, just execute the following line.
1. select 'drop proc '+name from sys.objects where type='p'
you will get list of procedure like
drop table proc1
drop table proc2
select all and execute
thats all
RAVIKUMAR S
BANGALORE
|
|
|
|
|
Create a cursor that loops through the system view INFORMATION_SCHEMA.ROUTINES or sysobjects
Get the name of the proc in the cursor
construct a drop procedure string DROP PROCEDURE [ProcName]
execute the string
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would suggest backing up the database before you start.
In object explorer go to the database go to the Programmability then Stored Procedures. Open the Object Explorer Details.
You can now script all the stored procedures so that when you delete them you have a copy.
Highlight the stored procedures to be deleted
Right click
Select Script Stored Procedure As/Create To/File
Give file name.
Now you can delete the files that are highlighted by right clicking and selecting delete.
|
|
|
|
|
DECLARE TblCursor CURSOR FOR
SELECT
[name]
FROM
sys.objects
WHERE
[name] like '%' + @fnd + '%'
and is_ms_shipped = 0
and [type] = 'P'
OPEN TblCursor
FETCH next FROM TblCursor INTO @proc
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'DROP PROCEDURE ' + @proc
EXEC @sql
FETCH next FROM TblCursor INTO @proc
END
CLOSE TblCursor
DEALLOCATE TblCursor
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
DECLARE @sProcName SYSNAME
DECLARE @iRowCnt INT, @i INT = 1, @sSQL VARCHAR(255)
DECLARE @tblProc TABLE (Id INT IDENTITY(1,1), Name SYSNAME)
INSERT INTO @tblProc (Name) SELECT name FROM sys.procedures
SET @iRowCnt = @@ROWCOUNT
WHILE @i <= @iRowCnt
BEGIN
SET @sProcName = (SELECT Name FROM @tblProc WHERE Id = @i)
SET @sSQL = 'DROP PROC '+@sProcName
PRINT 'Procedure '+@sProcName+' deleted.'
EXEC (@sSQL)
SET @i = @i + 1
END
|
|
|
|