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

    • 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
      Every Broadcast/Emit is limited to a maximum of 16 parameters Every Callback Function has to have the same number of parameters as the Broadcasted/Emited event 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.4.0 (This update DOES NOT break scripts)
      Added a new server method: _Io_getSockets which will return an array of all sockets. See more in the doc Added a banning-system, see more at: _Io_getBanlist, _Io_Ban, _Io_Sanction, _Io_IsBanned Added a new default event for clients banned. See more at default events Added two new client and server methods _Io_setEventPreScript And _Io_setEventPostScript. The intent for these is to not DRY when doing debug \ tasks that requires to be ran before or after events. Added a new client and server method _Io_ClearEvents. Added a third optional parameter to _Io_On called $socket, you may only pass the socket returned from _Io_Listen or _Io_Connect. The intent for this change is to allow for server + client in the same envoirment. Added a second parameter to _Io_Loop called $WhoAmI which should used with the new enums $_IO_SERVER and $_IO_CLIENT. The intent for this change is to allow for server + client in the same envoirment. Added a new client method _Io_TransferSocket. Added a new server method _Io_getActiveSocketCount. Optimations, avoiding Redims and unnecessary nested arrays as good as possible etc. Version 1.3.0 (This update DOES NOT break scripts)
      Got rid of unnecessary Redims with sockets and subscriptions in the main loop (This increased write performence greatly) Changed $iMaxDeadSocketsBeforeTidy from 100 to 1000 Changed _Io_setRecvPackageSize($nPackageSize = 2048) to _Io_setRecvPackageSize($nPackageSize = 4096) because 2017. Added Tests for both subscriptions and the automatic TidyUp Added a new server method: _Io_getMaxConnections Added a new server method: _Io_getMaxDeadSocketsCount Added a fifth parameter to the _Io_Listen method called $iMaxConnections which defaults to 100000. If the iMaxConnection + 1 user connects, they will be instantly disconnected. Added a parameter to _Io_Disconnect called $socket which defaults to null. If the iMaxConnections + 1 client connects, they will be instantly disconnected. Version 1.2.0 (This update DOES NOT break scripts)
      Added an option to set the packet-size of TCP-transports, see _Io_setRecvPackageSize Got rid of unnecessary StringLen's in _Io_loop Changed __Io_TidyUp to _Io_TidyUp and added it to the public Api reference list. Changed $iMaxDeadSocketsBeforeTidy default value from 1000 to 100 and added an option to disable it, read more at _Io_Listen Changed $bAutoReconnect from False to True. Fixed gitignore epicZ fail Improvemend Documentation Version 1.1.0 (This update DOES NOT break scripts)
      Fixed bug when Emitting / Broadcasting without any parameters causing a $fCallback crash Optimized Package-handling once again. Added 1D-Array support (Endless nestning). Added Subscriptions (See _Io_Subscribe _Io_Unsubscribe and _Io_BroadcastToRoom). Added new example for subscriptions (Be sure to use different room names when joining with clients) Added Unit testing (See Tests\Runner.au3 and Tests\Tests.au3, to run tests you need a udf found here: https://github.com/tarreislam/Autoit-Unit-Tester) Version 1.0.0
      (This update DOES NOT break scripts) Added data encryption (Using Autoit's UDF Crypt.au3) See more at _Io_EnableEncryption Added new method _Io_Disconnect which can be used with both servers and clients Improved package-handling to increase performance Increased the limit of Broadcasted/Emit parameters from 10 to 16 Api methods
      Server methods
      _Io_Listen($iPort, $iAddress = @IPAddress1, $iMaxPendingConnections = Default, $iMaxDeadSocketsBeforeTidy = 1000, $iMaxConnections = 100000) _Io_Subscribe(ByRef $socket, $sRoomName) _Io_Unsubscribe(ByRef $socket, $sRoomName = null) _Io_Broadcast(ByRef $socket, $sEventName, $p1, $p2, ...$p16)  
      _Io_BroadcastToAll(ByRef $socket, $sEventName, $p1, $p2, ...$p16) _Io_BroadcastToRoom(ByRef $socket, $sDesiredRoomName, $sEventName, $p1, $p2, ...$p16) _Io_socketGetProperty(ByRef $socket, $sProp = Default) _Io_getSockets($bForceUpdate = False, $socket = $__g_io_mySocket, $whoAmI = $__g_io_whoami) _Io_getDeadSocketCount() _Io_getSocketsCount() _Io_getActiveSocketCount() _Io_getMaxConnections() _Io_getMaxDeadSocketsCount() _Io_getBanlist($iEntry = Default) _Io_Ban($socketOrIp, $nTime = 3600, $sReason = "Banned", $sIssuedBy = "system") _Io_Sanction($socketOrIp) _Io_IsBanned($socketOrIp) _Io_TidyUp() Client methods
      _Io_Connect($iAddress, $iPort, $bAutoReconnect = True) _Io_Reconnect(ByRef $socket) Server and Client methods
      _Io_setEventPreScript($fCallback) _Io_setEventPostScript($fCallback) _Io_getVer() _Io_On(Const $sEventName, Const $fCallback, $socket = $__g_io_mySocket) _Io_Emit(ByRef $socket, $sEventName, $p1, $p2, ...$p16) _Io_Loop(ByRef $socket, $whoAmI = $__g_io_whoami) _Io_LoopFacade() _Io_EnableEncryption($sFileOrKey, $CryptAlgId = $CALG_AES_256) _Io_Disconnect($socket = null) _Io_setRecvPackageSize($nPackageSize = 4096) _Io_ClearEvents() _Io_TransferSocket(ByRef $from, ByRef $to) Default events
      Server events
      connection Client events
      banned Server and Client events
      disconnect 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 (NEWEST 2017-08-11)
    • 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
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening guys
      I am working on a little project, in which I have to retrieve 8000+ rows of data from a table, from a SQLite database, to populate a combobox 
      This is what I tried 'til now, but it still takes about 12 seconds to populate the combobox.
      _SQLite_Exec($objDatabase, "BEGIN TRANSACTION;") If _SQLite_GetTable($objDatabase, "SELECT DISTINCT Comune FROM LISTA_COMUNI;", $arrRisultatoQuery, $intRighe, $intColonne) = $SQLITE_OK Then For $i = 2 To UBound($arrRisultatoQuery) - 1 If $i < UBound($arrRisultatoQuery) - 1 Then GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i] & "|") Else GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i]) EndIf Next _SQLite_Exec($objDatabase, "COMMIT;") Are there any other solution to retrieve 8000+ records from a SQLite database?
      Thank you very much  
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning community!
      I am working on a script which read from a text file ( .txt ) and should import all the content in a SQLite3 DB, in order to execute some queries that should be difficult to execute on a text file.
      So, I was looking for something very very fast, because the file could be very large ( I don't know exaclty how much can became big, but I know a lot of rows, it's a log file ... )
      I found the "Import method", but I don't know If I can implement it in a query ( @jchd, it's your turn!  )
      Do you know some methods that I can implement in my script to have a very very fast import of thousands and thousands rows in a SQLite3 DB?
      Thanks a lot
      Francesco