SQL + C++ ODBC help please :((((

Hawick · July 11, 2019

So I've got this piece of C++ code,

class C_prDS_LoginAccessor
{
public:

	LONG		m_RETURN_VALUE;
	char		m_acc[en_max_lil+1];
	short		m_forest ;
	int			m_temp_uid ;
	int			m_uid ;


	DEFINE_COMMAND_EX(C_prDS_LoginAccessor, L"{ ? = CALL dbo._prDS_Login(?,?,?,?) }")


		BEGIN_PARAM_MAP(C_prDS_LoginAccessor)
			SET_PARAM_TYPE(DBPARAMIO_OUTPUT)
			COLUMN_ENTRY(1, m_RETURN_VALUE)
			SET_PARAM_TYPE(DBPARAMIO_INPUT | DBPARAMIO_OUTPUT )
			COLUMN_ENTRY(2, m_acc)
			SET_PARAM_TYPE(DBPARAMIO_INPUT)
			COLUMN_ENTRY(3, m_forest)
			SET_PARAM_TYPE(DBPARAMIO_INPUT)
			COLUMN_ENTRY(4, m_temp_uid)
			SET_PARAM_TYPE(DBPARAMIO_INPUT | DBPARAMIO_OUTPUT)
			COLUMN_ENTRY(5, m_uid)			
		END_PARAM_MAP()
};

It's a sort of sql accessor in C++. It calls a stored procedure in the DB but the problem is, this particular stored procedure I do not have. It's missing. I think this information is enough to translate this code into SQL stored procedure command but I'm very stupid when it comes to MSSQL. My SQL knowledge is like zero so please help :'((

Here's a template of how it should kinda look like.

Additional info:

1.) I think "m_RETURN_VALUE" returns some error code when the login fails or it can also indicate a successful login. I don't know about this, it should be 0 or 1. However it says "LONG" in data type so uh...no idea what this is.

2.) the prefix "m_" should be removed. So in SQL it should be like this "RETURN_VALUE", "acc", "forest"...


CREATE PROCEDURE [dbo].[_prDS_Login]
	@RETURN_VALUE bigint
	@uid	int,
	@acc char 	OUTPUT,		-- ?? 
	@forest smallint,			-- SERVER KEY	
	@temp_uid int 				-- USER GUID
AS

	DECLARE	@Count	INT
	,	@user_id varchar(60)

	SET NOCOUNT ON 
	SET LOCK_TIMEOUT 2000


	SELECT @acc = member_id FROM Tbl_Member_Login WHERE @acc_uid = @acc_uid

--	SELECT @Count = @@rowcount

	IF EXISTS ( SELECT  1  FROM Tbl_Connect WHERE @acc_uid = @acc_uid)
		SET @Count = 1
	ELSE
		SET @Count = 0

	BEGIN TRAN

		IF @Count <> 0 
			BEGIN
				UPDATE Tbl_Connect WITH(UPDLOCK)
				SET server_guid = @forest, 
				member_id = @acc,
				cur_state = 1, 
				login_time = getdate(),
				temp_id = @temp 
				WHERE @acc_uid = @acc_uid
			END

	IF @@error <> 0 
		ROLLBACK TRAN
	ELSE
		COMMIT TRAN


	IF @forest = 0
	BEGIN
		SELECT TOP 1 @user_id =[user_id] FROM [DATA_0].dbo.ND_V01_Charac WHERE acc_id = @acc_uid and delete_flag = 0
		IF @acc <> @user_id
			UPDATE [DATA_0].dbo.ND_V01_Charac SET [user_id]= @acc WHERE acc_id = @acc_uid 
	END

This is just an example of how it should look like. So, again, I'm very bad at SQL but I must pull this off - please someone help

Dat Guy · July 12, 2019

I assume that this is some kind of homework?

JacobFW · July 12, 2019

I'll admit I don't know the full details of how parameters work with the C++ ODBC api, but this

 ? = CALL dbo._prDS_Login(?,?,?,?)

looks wrong. The first question mark would indicate that the return value of the function is a parameter, which I don't think is right. As far as I'm aware Parameters are only used to pass data to the database, not retrieve data from it.

In case you don't know, the second piece of code you showed is the code that you run on the database to make the procedure. You only have to run it once and it will stay in place. Before you can call the procedure, you need to execute that code so that you can use it.

If you have a Management studio you can connect to the database and execute the code, then look at the list of user defined functions to make sure it's there. Otherwise I believe you should be able to pass the create procedure code through the odbc api and it should work just the same.