[RELEASE] Dragonball Online Release Source

just finished to add msql now it's connected and query are executed.

just have to understand how store SELECT and how replace '?' by the value send to the user

Quote:

---

Originally Posted by maximojoni

[Query Server]
Port = 15000? /? 3306? work?

---

nothing to do, it's the socket port not the sql port.

i can help with that? i just got a virtual private server from hostmysite free for 30days :)
simply google hostmysite vps trial and look for the link that looks like this :)
http://prntscr.com/3slzeb

[Query Server]
Port = 15000

^
like already said its the socket port and for other servers to know which port is needed to access the query server.

server -> query server -> mssql

- - - Updated - - -

Quote:

---

Originally Posted by dbomaster

i can help with that? i just got a virtual private server from hostmysite free for 30days :)
simply google hostmysite vps trial and look for the link that looks like this :)
http://prntscr.com/3slzeb

---

Why would anyone need this?

- - - Updated - - -

Suck my loong penisssssss I finally found out how to echo the god damn results *______________*

- - - Updated - - -

OK now looking into how to create/read procedures... Much work but make it very easy to create queries
How to create procedure for query
http://msdn.microsoft.com/en-US/en-d...#SSMSProcedure
http://www.juniper.net/techpubs/soft...rc-admin7.html

- - - Updated - - -

here is my queryserver.h file

Code:

---

#ifndef _QUERY_SERVER_H_
#define _QUERY_SERVER_H_


#include "NtlSfx.h"
#include "NtlSfxDB.h"

const unsigned int MAX_SIZE_USER_ID = 20;
const unsigned int MAX_SIZE_USER_PW = 20;
const unsigned int MAX_SIZE_CHAR_NAME = 20;
const unsigned int MAX_SIZE_CHAR_DATA = 10;

struct sCHAR_DATA
{
        float fLocX;
        float fLocY;
        float fLocZ;
        int nItemCount;
        BYTE abyData[MAX_SIZE_CHAR_DATA];
};
enum SQL_ID
{
        SP_AccountCreate = 0,
        SP_AccountSelect,

        MAX_SQL_ID
};


//------------------------------------------------------------------
BEGIN_DECLARE_SQLUNIT2( SP_AccountCreate )
        DEFINE_QUERY( "UPDATE account SET account_id = ? WHERE username = ?" )

        // Define the data for BEGIN_COLUMN
        BEGIN_VARIABLE()
                char        m_szUserPW[MAX_SIZE_USER_PW + 1]; // Account ID
                char        m_szUserID[MAX_SIZE_USER_ID + 1]; // Username
        END_VARIABLE()

        // Dont know yet
        BEGIN_COLUMN(2) // 2 = the amount of rows
                COLUMN_ENTRY_STR(m_szUserPW) // Account ID
                COLUMN_ENTRY_STR(m_szUserID) // Username
        END_PARAM()

        // Defines the "?" inside query
        BEGIN_PARAM(2) // 2 = the amount of rows
                PARAM_ENTRY_STR(SQL_PARAM_INPUT, m_szUserPW) // Account ID
                PARAM_ENTRY_STR(SQL_PARAM_INPUT, m_szUserID) // Username
        END_PARAM()
END_DECLARE_SQLUNIT()
//------------------------------------------------------------------
BEGIN_DECLARE_SQLUNIT( SP_AccountSelect, "{ ? = call AccountSelect(?,?,?) }" )

        BEGIN_VARIABLE()
                char        m_szUserID[MAX_SIZE_USER_ID + 1];
                char        m_szUserPW[MAX_SIZE_USER_ID + 1];
                DWORD        m_dwAccountID;
                int                m_nResultCode;
        END_VARIABLE()


        // Define the input on AccountSelect. 1 SQL_PARAM_INPUT -> 1 ? inside the ( )
        //example 2x PARAM_ENTRY_STR(SQL_PARAM_INPUT, XXXXX) = AccountSelect(?, ?)
        //example 1x PARAM_ENTRY_STR(SQL_PARAM_INPUT, XXXXX) = AccountSelect(?)
        BEGIN_PARAM(4)
                PARAM_ENTRY(SQL_PARAM_OUTPUT, m_nResultCode)       
                PARAM_ENTRY_STR(SQL_PARAM_INPUT, m_szUserID)
                PARAM_ENTRY_STR(SQL_PARAM_INPUT, m_szUserPW)
                PARAM_ENTRY(SQL_PARAM_OUTPUT, m_dwAccountID)
        END_PARAM()
END_DECLARE_SQLUNIT()
//------------------------------------------------------------------




CNtlEvent g_exitEvent;

class CQuery_UpdateAccountID : public CNtlQuery
{
public:

        CQuery_UpdateAccountID(const char * lpszUserPW, const char * lpszUserID)
        {
                ZeroMemory( m_szUserID, MAX_SIZE_USER_ID + 1 );
                ZeroMemory( m_szUserPW, MAX_SIZE_USER_PW + 1 );

                strncpy_s( m_szUserID, lpszUserID, MAX_SIZE_USER_ID );
                strncpy_s( m_szUserPW, lpszUserPW, MAX_SIZE_USER_PW );
        }
               
        int ExecuteQuery(CNtlDatabaseConnection * pConnection)
        {
                SQLLEN nRowCount = 0;

                ////---------------------------------------------------------------
        /*        FIND_SQLUNIT( SP_AccountCreate, pConnection, pSqlUnit1 );
                if( NULL == pSqlUnit1 )
                {
                        return NTL_FAIL;
                }

                strncpy_s( pSqlUnit1->m_szUserPW, m_szUserPW, MAX_SIZE_USER_PW );
                strncpy_s( pSqlUnit1->m_szUserID, m_szUserID, MAX_SIZE_USER_ID );
                pSqlUnit1->Exec();

                //printf("ExecuteQuery Done: %i \n", m_dwAccountID );
        */
                ////---------------------------------------------------------------
                ////---------------------------------------------------------------
                FIND_SQLUNIT( SP_AccountSelect, pConnection, pSqlUnit2 );
                if( NULL == pSqlUnit2 )
                {
                        return NTL_FAIL;
                }

                strncpy_s( pSqlUnit2->m_szUserPW, m_szUserPW, MAX_SIZE_USER_ID );
                strncpy_s( pSqlUnit2->m_szUserID, m_szUserID, MAX_SIZE_USER_ID );


                pSqlUnit2->Exec( &nRowCount );
                printf("ExecuteQuery Done: result: %i \n", pSqlUnit2->m_dwAccountID );
               

                return NTL_SUCCESS;
        }


        int ExecuteResult()
        {
                printf("ExecuteResult Done \n" );
               
                g_exitEvent.Notify();

                return NTL_SUCCESS;
        }               


private:
        DWORD                                m_dwAccountID;
        char                                m_dwPassword[MAX_SIZE_USER_PW + 1];

        char                                m_szUserID[MAX_SIZE_USER_ID + 1];

        char                                m_szUserPW[MAX_SIZE_USER_PW + 1];

};


class CSampleSqlUnitHelper : public CNtlSqlUnitHelper
{
public:

        CSampleSqlUnitHelper(SQLUNITID maxSqlUnitID)
                :CNtlSqlUnitHelper(maxSqlUnitID) {}

        virtual BOOL        PreCreateSqlUnit(CNtlDatabaseConnection * pConnection)
        {

                if( FALSE == MAKE_SQLUNIT(SP_AccountCreate, pConnection ) )
                {
                        return FALSE;
                }
                if( FALSE == MAKE_SQLUNIT(SP_AccountSelect, pConnection ) )
                {
                        return FALSE;
                }


                return TRUE;
        }
};


#endif

---

to run query:

Quote:

---

databaseManager.Query( hDB, new CQuery_UpdateAccountID("1234", TEXT("DaneosIsSexy")) );

---

this is for people with knowledge so dont ask what to do with this

my account database
http://www.sendspace.com/file/wvmjyu

- - - Updated - - -

And here is an example query on how to select 2 rows.

Code:

---

BEGIN_DECLARE_SQLUNIT2( SP_AccountSelect )
        DEFINE_QUERY( "SELECT account_id,password FROM account WHERE username = ?" )

        BEGIN_VARIABLE()
                DWORD        m_dwAccountID;
                DWORD        m_dwPassword;
                char        m_szUserID[MAX_SIZE_USER_ID + 1];
        END_VARIABLE()

        // Results I want
        BEGIN_COLUMN(2)
                COLUMN_ENTRY(m_dwAccountID)
                COLUMN_ENTRY(m_dwPassword)
        END_PARAM()

        // Define the ?... (m_szUserID == ?)
        BEGIN_PARAM(1)
                PARAM_ENTRY_STR(SQL_PARAM_INPUT, m_szUserID)
        END_PARAM()
END_DECLARE_SQLUNIT()

---

Code:

---

FIND_SQLUNIT( SP_AccountSelect, pConnection, pSqlUnit2 );
                if( NULL == pSqlUnit2 )
                {
                        return NTL_FAIL;
                }

                strncpy_s( pSqlUnit2->m_szUserID, m_szUserID, MAX_SIZE_USER_ID );

                //SQLINTEGER nResultCount = 1;
                //pSqlUnit2->StoreAndFetch(&nResultCount);


                if( pSqlUnit2->Store() )
                {
                        pSqlUnit2->Fetch();
                        printf("ExecuteQuery Done: ACCOUNT ID: %i, PASSWORD: %s \n", pSqlUnit2->m_dwAccountID, pSqlUnit2->m_dwPassword );
                }

---

for people who want to work on query server:
Query like this

Code:

---

BEGIN_DECLARE_SQLUNIT( SP_CharacterCreate, "{ ? = call CharacterCreate(?, ?, ?) }" )

---

is only for queries which need a result-code.

Lets say for auth login. There you need result code
result code = success
result code = wrong username
result code = wrong password
result code = other things..

in the procedure you have to "choose" which result code is "getting back".

for querys without result code you can use query like this (very easy)

Code:

---

BEGIN_DECLARE_SQLUNIT2( SP_AccountSelect )
DEFINE_QUERY( "SELECT account_id,password FROM account WHERE username = ?" )

---

just view "DBSampleServer.cpp"

a list of the procedures from the database
http://s14.directupload.net/images/140614/o6bjo6bh.jpg

You can also use Navicat for this!
http://s7.directupload.net/images/140614/hepbpnpn.jpg

Now let's make "select or insert etc..." "select ? into ? where ? = ?" :P:

Quote:

---

Originally Posted by ATIDOT3

Now let's make "select or insert etc..." "select ? into ? where ? = ?" :P:

---

what?

imagine if you have to do 1 function foreach diffierent other function in your server...

so for the SELECT :

char iselect[MAX_COLUMN_DATABASE] = "select ? from ? where ? = ?"; <-----> "select * from item where id = 1"

EDIT;

Quote:

---

int m_nResultCode;
PARAM_ENTRY(SQL_PARAM_OUTPUT, m_nResultCode)

---

Don't really understand this, it's the result code from the Mssql function on the database ?

Quote:

---

char iselect[MAX_COLUMN_DATABASE] = "select ? from ? where ? = ?"; <-----> "select * from item where id = 1"

---

way too complex and will never be needed xd

you will receive the result code from the procedure

like this

Code:

---

ALTER PROCEDURE [dbo].[AccountAuth] /* Procedure Name. */
    @m_szUserID varchar(20),
                @m_szUserPW varchar(50)
AS

    SET NOCOUNT ON;

DECLARE @m_nResultCode int /* Set result code as int (number) */
select @m_nResultCode = 100

BEGIN
SELECT username, password
FROM account
WHERE username = @m_szUserID;



END

---

Note: that code there is wrong and does not work

Quote:

---

CREATE PROCEDURE SP_AccountSelect
@uname as varchar(32),
@pword as varchar(32) OUTPUT,
@accid as varchar(32)OUTPUT
AS
SELECT @pword = Password,@accid = AccountID FROM authentication WHERE
Username = @uname AND active = 'yes'
GO

---

this is mine

http://s14.directupload.net/images/140614/ng9vmu7a.jpg

xd

Quote:

---

ALTER PROCEDURE [dbo].[AccountCreate] /* Procedure Name. */
@m_szUserID varchar(32),
@m_szUserPW varchar(32),
@m_nResultCode int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Password varchar(32);


IF EXISTS(SELECT 1 FROM Account WHERE Username = @m_szUserID)
BEGIN
SELECT @user = [Username]
FROM Account
WHERE username = @m_szUserID;


IF (@user = @m_szUserPW)
BEGIN
SET @m_nResultCode = 1; -- 1 for existing
END
ELSE
BEGIN
SET @m_nResultCode = 0; -- 0 for not existing
END
END
ELSE
BEGIN
INSERT INTO Account (
Username,
Password
) VALUES (
@m_szUserID,
@m_szUserPW
)
SET @m_nResultCode = 100; -- 100 ok
END
END

---

Quote:

---

SP_AccountCreate, "{ ? = call AccountCreate(?, ?) }" )

---

Good like that ?

edited-------

for test with sql server

Quote:

---

DECLARE @Out_Result INT;

EXECUTE [dbo].[AccountCreate] @m_szUserID = 'UserName'
,@m_szUserPW = '123456'
,@m_nResultCode = @Out_Result OUTPUT

---

I have like this now.

Code:

---

ALTER PROCEDURE [dbo].[AccountAuth] /* Procedure Name. */
    @m_szUserID varchar(20),
                @m_szUserPW varchar(50),
                @m_nResultCode int OUTPUT
AS
BEGIN

    SET NOCOUNT ON;
                DECLARE @PASsword varchar(50)
                DECLARE @status varchar(10)

        IF EXISTS(SELECT 1 FROM account WHERE username = @m_szUserID)
                BEGIN
                                        SELECT @PASsword = password, @status = status
                                        FROM  account
                                        WHERE  username = @m_szUserID;
                                               
                                                IF  @PASsword = @m_szUserPW)
                                                        BEGIN
                                                                SET @m_nResultCode = 100;
                                                        END
                                                ELSE
                                                        BEGIN
                                                                SET @m_nResultCode = 107;
                                                        END

                                IF  @status = 'BLOCK')
                                        BEGIN
                                                SET @m_nResultCode = 113;
                                        END

                END
        ELSE
                BEGIN
                        SET @m_nResultCode = 108;
                END


END

---

Quote:

---

SP_AccountCreate, "{ ? = call AccountCreate(?, ?) }" )

---

Can't find the procedure, got -8446423 as result, any idea how to call procedure ?

SP_AccountCreate, "{ ? = call AccountCreate(?, ?) }" )

^ with that you call the procedure "AccountCreate"

Quote:

---

BEGIN_DECLARE_SQLUNIT( SP_AccountCreate, "{ ? = call AccountCreate(?, ?) }" )
ALTER PROCEDURE [dbo].[AccountCreate] /* Procedure Name. */

---

Not working.

Of course it wont work. You must edit things too. You cant just copy here and there and think it will work xd