Blank517

_SQLite_FetchData problem

13 posts in this topic

Hi, when a client sends /logout to the server i want that the server find the id of the client and set his online status to 0

Using 'default' I have no problems, but when I do this with 'blank517' gives me id 10 and then for the database remains online

Database while Blank517 logout:

id  |  username  |  password |   permissions  |  online  |

0   |  default        |    pass1      |           0             |      0       |

1   |  Blank517    |     pass       |           0              |     1       |

Server recv:

Func _Recv_From_Sockets_()
    For $0 = 1 To $max_connections
        $Recv = TCPRecv ($Socket_Data[$0][0],1024)
        If StringLeft($Recv, 1) = "/" Then
            If StringInStr($Recv, "logout") Then
                _SQLite_Query(-1, "SELECT id FROM Users WHERE username = '" & $Socket_Data[$0][1] & "' AND online = '1';", $hQuery)
                While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                    $sMsg &= $aRow[0]; <-- $sMsg = 10 after FetchData
                WEnd
                _SQLite_Exec(-1, "UPDATE Users SET online = '0' WHERE id = '" & $sMsg & "';")
                For $000 = 1 To $max_connections
                    TCPSend($Socket_Data[$000][0], $Socket_Data[$0][1] & " ha effettuato il logout")
                Next
                TCPCloseSocket($Socket_Data[$0][0])
                $sMsg = Null
            EndIf
        Else
            _Broadcast_To_Sockets_ ($Recv)
        EndIf
    Next
EndFunc

*excuse me for my bad english >_< *

Share this post


Link to post
Share on other sites



run this func:

Func _Recv_From_Sockets_()
    For $0 = 1 To $max_connections
        $Recv = TCPRecv ($Socket_Data[$0][0],1024)
        If StringLeft($Recv, 1) = "/" Then
            If StringInStr($Recv, "logout") Then
                _SQLite_Query(-1, "SELECT id FROM Users WHERE username = '" & $Socket_Data[$0][1] & "' AND online = '1';", $hQuery)
                While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                    $sMsg &= $aRow[0]; <-- $sMsg = 10 after FetchData
                WEnd
                $sMsg=StringStripWS($sMsg,3)
                ConsoleWrite($sMsg&@CRLF)
                _SQLite_Exec(-1, "UPDATE Users SET online = '0' WHERE id = '" & $sMsg & "';")
                For $000 = 1 To $max_connections
                    TCPSend($Socket_Data[$000][0], $Socket_Data[$0][1] & " ha effettuato il logout")
                Next
                TCPCloseSocket($Socket_Data[$0][0])
                $sMsg = Null
            EndIf
        Else
            _Broadcast_To_Sockets_ ($Recv)
        EndIf
    Next
EndFunc

and post the output of the console.

Share this post


Link to post
Share on other sites

The output is 11

Share this post


Link to post
Share on other sites

So all records with id=11 will be set to offline.

Share this post


Link to post
Share on other sites
Just now, AutoBert said:

So all records with id=11 will be set to offline.

Ok, but 'Blank517' has id '1' not '11' 

Share this post


Link to post
Share on other sites
11 minutes ago, Blank517 said:

Ok, but 'Blank517' has id '1' not '11' 

Then are 2 records found and '1'&'1' = '11' so test this:

Func _Recv_From_Sockets_()
    For $0 = 1 To $max_connections
        $Recv = TCPRecv ($Socket_Data[$0][0],1024)
        If StringLeft($Recv, 1) = "/" Then
            If StringInStr($Recv, "logout") Then
                _SQLite_Query(-1, "SELECT id FROM Users WHERE username = '" & $Socket_Data[$0][1] & "' AND online = '1';", $hQuery)
                If _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Then
                    $sMsg &= $aRow[0]; <-- $sMsg = 10 after FetchData
                    $sMsg=StringStripWS($sMsg,3)    ;test if needed
                    ConsoleWrite($sMsg&@CRLF)
                    _SQLite_Exec(-1, "UPDATE Users SET online = '0' WHERE id = '" & $sMsg & "';")
                EndIf
                For $000 = 1 To $max_connections
                    TCPSend($Socket_Data[$000][0], $Socket_Data[$0][1] & " ha effettuato il logout")
                Next
                TCPCloseSocket($Socket_Data[$0][0])
                $sMsg = Null
            EndIf
        Else
            _Broadcast_To_Sockets_ ($Recv)
        EndIf
    Next
EndFunc

 

Share this post


Link to post
Share on other sites

Thanks, now it works ^^

Share this post


Link to post
Share on other sites

Last help for sqlite xP

When a user login, even if it is not registered and therefore hasn't an id, the server accepts the connection

_SQLite_Query(-1, "SELECT id FROM Users WHERE username = '" & $Socket_Data[$0][1] & "' AND password = '" & $Socket_Data[$0][2] & "' AND online = 0;", $hQuery)
            If _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Then
                $sMsg &= $aRow[0]
            EndIf
            If $sMsg = "" Then
                ConsoleWrite("[Alert] Connection refused! (" & $Socket_Data[$0][1] & ")")
                TCPSend($Socket_Data[$0][0], "Connection refused!" & @CRLF & "There is a problem with the database:" & @CRLF & " - You're not registered" & @CRLF & " - You're already online")
                TCPCloseSocket($Socket_Data[$0][0])
                Return
            EndIf

 

Share this post


Link to post
Share on other sites

Blank517,

You are concatenating the query results to the variable $msg...try just setting $msg to the results of the query.

...on a smart phone so cant test and typing is atrocious...

Kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
25 minutes ago, kylomas said:

You are concatenating the query results to the variable $msg...try just setting $msg to the results of the query.

The result does not change it always give me a null id

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Blank517,

Are you using a DB shell to interrogate/develop your DB?  SqliteExpert is an excellent tool if you need one.  And it has a free version.

If you do have a DB viewer/shell, verify that ID contains what you think it should.

Also, it does not make sense that you are getting a "11" from fetchdata (post #3).  I'm not sure that problem has been identified yet.

Can you supply a reproducer, DB schema?

kylomas

edit: or your entire script?

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
16 hours ago, kylomas said:

Blank517,

You are concatenating the query results to the variable $msg...try just setting $msg to the results of the query.

...on a smart phone so cant test and typing is atrocious...

Kylomas

 

16 hours ago, Blank517 said:

The result does not change it always give me a null id

 

Solved 

_SQLite_Query(-1, "SELECT id FROM Users WHERE username = '" & $Recv[1] & "' AND password = '" & $Recv[2] & "' AND online = 0;", $hQuery)
            If _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Then
                $sMsg = $aRow[0]
            EndIf
            If $sMsg = Null Then; <-- not $sMsg = "" but = Null
                ConsoleWrite("[Alert] Connection refused! (" & $Recv[1] & ")")
                TCPSend($Socket_Data[$0][0], "Connection refused!" & @CRLF & "There is a problem with the database:" & @CRLF & " - You're not registered" & @CRLF & " - You're already online")
                TCPCloseSocket($Socket_Data[$0][0])
                Return
            EndIf

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • ripdad
      By ripdad
      WSA_NBTCP.au3  (Windows Sockets API - Non-Blocking Transmission Control Protocol)
      Version: 1.00
      Type: UDF
      This is an accumulation of WSA code from many sources and modified to suit myself.
      These functions have been thoroughly tested using a Local Proxy Server, which
      is about the most strenuous test you can use.
      Includes my rendition of how a TCPRecv Timeout should work. Also includes a
      timewait/timeout using Select for TCP Send, which works great for that function.
      You will need a loop to use _WSA_TCPRecv(). An example will be forthcoming in a second post.
      Functions:
      #CURRENT_USER_FUNCTIONS
      _WSA_Cleanup
      _WSA_FormatMessage
      _WSA_GetLastError
      _WSA_TCPAccept
      _WSA_TCPCloseSocket
      _WSA_TCPConnect
      _WSA_TCPListen
      _WSA_TCPRecv
      _WSA_TCPSend

      #INTERNAL_FUNCTIONS
      __TimeoutManager
      __TimeoutReset

      #EXTRA_FUNCTIONS
      _WSA_GetAddrInfo
      _WSA_GetHostByAddr
      _WSAAsyncGetHostByName
      _WSAAsyncGetHostByName_Callback
      _WSA_GetNameInfo
       
      Requirements:
      - AutoIt Versions: 3.3.8.1 thru 3.3.15.0 (32Bit only).
      - TCPStartup() at beginning of script on startup.
      - TCPShutDown() and _WSA_Cleanup() on exit.
      Download UDF: WSA_NBTCP.au3
       
    • Burgs
      By Burgs
      Greetings,
        I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task.  I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...?  The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database.
        I am attempting the following:
      #include <SQLite.au3> #include <SQLite.dll.au3> Global $hDb, $sIn, $sOut ... $sIn = ".tables" & @CRLF _SQLite_SQLiteExe($hDb, $sIn, $sOut) if @error == 0 Then ;Show Table (using SQLite3.dll) Else if @error == 2 Then ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF) Else ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF) EndIf ;@error is "2"...OR NOT... EndIf ;@error is "0"...OR NOT... ...   The error being thrown is "ERROR: Sqlite3.exe file not found" ...
        Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...???  I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated.  Thanks in advance. 
      Regards
       
       
       
       
       
       
       
    • tarretarretarre
      By tarretarretarre
      AutoIt-SocketIo
      Yep yep, this is pretty much an attempt to port the existing project's concept https://socket.io/ to AutoIt's Codebase. So i will not go in to so much detail.
      This is how the communication is done http://i.imgur.com/0mMfsBD.png Each client is isolated to the server http://i.imgur.com/rVO2LFb.png Features
      Easy API VarType Translation (Example: If the server sends an int, the client will receive an int and vice versa) Fully featured examples Data encryption (Using Autoit's UDF Crypt.au3) Limitations / Drawbacks
      It is not possible to Broadcast/Emit objects Only 1D-arrays are allowed to be Broadcasted/Emitted (2D arrays will probably never be supported) Changelog
      Version 1.5.0 (This update DOES NOT break scripts)
      Added AutoIt like docs under Docs\. The docs are generated so its a 1 to 1 reflection of the UDF headers. Print of documentation Added a production ready server example. Added a new method: _Io_DevDebug which will display useful information when debugging. Added a new method: _Io_SetMaxRecvPackageSize which defaults to whatever _Io_setRecvPackageSize is set to. Added a new method: _Io_setOnPrefix which defaults to _On_ Added a new default client & server event called flood. Flood occurs when exceeding the $__g_io_nMaxPacketSize. $__g_io_nMaxPacketSize is set by _Io_SetMaxRecvPackageSize Fixed the 16 parameter limit when sending data with _Io_Emit, _Io_Broadcast, _Io_BroadcastToAll and _Io_BroadcastToRoom. This works on the same premise that AutoIt's Call Does Fixed a TRUNCATION problem when receiving packages which could cause crashes! Fixed a programming error which caused $__g_ionPacketSize to reset to default 4096 if _Io_Connect or _Io_listenwere called after _Io_setRecvPackageSize Fixed _Io_setEventPreScript and _Io_setEventPostScript They didnt work. Lol. Changed how events are fired so the client cannot crash the server by sending the wrong number of parameters (This also allows for optional parameters on callbacks) Changed _Io_On. The second parameter $fCallback can now be set to null. Doing this, the function assumes that the callback is: _On_<eventName>.  
      Changelog History
      Api methods
      Please see the docs provided
      Default events
      Server events
      connection Client events
      banned Server and Client events
      disconnect flood View source on github
       
      Autoit-Socket-IO-1.0.0.zip (OLD!)
      Autoit-Socket-IO-1.1.0.zip (OLD)
      Autoit-Socket-IO-1.3.0.zip (OLD)
      Autoit-Socket-IO-1.4.0.zip (OLD)
      Autoit-Socket-IO-1.5.0.zip (NEWEST 2017-12-20)
    • timmalos
      By timmalos
      Hello all.
      In case this is interesting few of you, I share my AMCP 2.1 protocol UDF in AutoIT. This protocol is used by CasparCG server, which is a Windows and Linux software used to play out professional graphics, audio and video to multiple outputs as a layerbased real-time compositor. It has been in 24/7 broadcast production since 2006. It's free and opensource.
      The UDF I share allows communication between an AutoIt based client and the CasparCG, based on following documentation : http://casparcg.com/wiki/CasparCG_2.1_AMCP_Protocol
      If you want more details on CasparCG :  official WebSite or have a look to this video
      I'm currently building a full Client based on AutoIt, with many features like drag-and-drop layers, but sadly I can't share it right now, might come later. Don't hesitate to ask questions if you have any or need a basic example.
      The only requirement for this UDF is the other Event-driven TCP UDF by Kip
       
       

      AMCP_shared.au3
      TCP.au3
    • kingjacob90
      By kingjacob90
      Hi
      I am trying to send a file over TCP from a TCP client to server. If I run the server and client on the same computer the file is send fine, but if the server is on one computer on the network and the client on another the file is sent in what looks like more than one packet. In other words the server receives 4 msg from the client.
      Why is this, how do I make it send in one go, or what is a way around it?
      Side Note: TCP server has a max of 999999999