Best way to merge/process commands on SQLCE

Spawn Melmac

Greetings, Apologies if this is a dumb question but I'm afraid my SQL knowledge is a little limited. Anyway I am using SQLCE as I don't need anything bigger at the moment but I have a problem when processing the following command

INSERT INTO [Contact]
([Name]
,[Tel]
,[Mobile]
,[Email])
VALUES
(N'Name'
,N'Tel'
,N'Mobile'
,N'Email');
SELECT @@IDENTITY AS ContactID

My problem is that although this functions correctly when issued by hand, my encapsulation of OLEDB only seems to permit a single command. Now this is the only occasion when I need to issue multiple commands so it seems a shame to have to mess up my existing code just for this instance. So can anyone suggest how I might merge the above into a single command that INSERTs and gives me the ID of the inserted data? Or is there a simple way with OLEDB & MFC that I can process multiple recordsets without adding too much overhead. Any thoughts or suggestions would be appreciated. Many thanks

Alan

David Mujica

Here is what I've done in the past ... SQLtext = "Insert .... blah blah Select scope_identity()" ' This returned the identity column value that was just created. ID = sqlCmd.ExecuteScalar()

Lost User

It should be possible as two separate commands, each encapsulated in their own SqlCeCommand class. An ExecuteScalar on the second select, re-using the connection of the previous command.

I are Troll :suss:

Spawn Melmac

Sorry if this seems a dumb question but could this be integrated into the ACCESSOR class mechanism I am using at present? I have geared everything to use accessor classes which is working fine for everything but the INSERT operations. It is just annoying because I know the insert is working but it does not give me back the ID of the item that was inserting. So when I came across the @@IDENTITY I thought I was sorted and as the INSERT does not return any results it looked like I could bunch the commands into the same accessor. Life is never as simple as I expected it to be :(

Alan

Lost User

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();

// execute a second command;
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 :suss:

Spawn Melmac

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:
// Data Elements
TCHAR f_LocSub[255];
int f_HubID; // Input field

// Column binding map
BEGIN_COLUMN_MAP(CDBHubDetails)
COLUMN_ENTRY(1, f_LocSub)
END_COLUMN_MAP()

// Parameter binding 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;													// pull the HubID from the CDevice object
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

Lost User

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 :suss: