Jump to content
Blank517

_SQLite_FetchData problem

Recommended Posts

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

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

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

    • By tarretarretarre
      Autoit-Socket-IO
      Introduction
      Autoit-Socket-IO is a event driven TCP/IP wrapper heavily inspired from Socket.IO with focus on user friendliness and long term sustainability.
      I created this UDF because I was fascinated how Socket.IO made a such scary task "reliable and secure networking" so simple for the developer. So this was my main motivation.
      I constantly want to make this UDF faster and better, so if you have any suggestions. Do not hesitate to make requests!
      Features
      Flexiable and easy to understand API Above avarage documentation "Fully featured" examples Security in form of data encryption and middleware-support Limitations
      Speed. Because I want this UDF to be as flexible and simple as possible. Sometimes speed is sacrificed, but that does not mean i don't try to . It is not possible to emit objects mainly because autoit does not support serialization. Only 1D-arrays can be emitted (2D arrays will probably never be supported) Success story
      Since December 2017-now I have used version 1.5.0 in an production environment for 40+ clients with great success, the only downtime is planned windows updates and power outages.
      Getting started
      Download the script from AutoIt or pull it from the official github repo git@github.com:tarreislam/Autoit-Socket-IO.git and checkout the tag 2.0.0 The documentation is located at Docs\index.html Take a look in the examples/ folder Changelog
      Version 2.0.0 (This update break scripts. Please consult the upgrade.md for guidance)
      All global internal variables has been renamed. Added a bunch of new API methods: _Io_RegisterMiddleware, _Io_whoAmI, _Io_IsClient, _Io_IsServer, _Io_getAllByProperty and _Io_getFirstByProperty and some more. Read more about these in the documentation. _Io_socketGetProperty now has a setter method called _Io_socketSetProperty which can be used to set custom properties. _Io_socketGetProperty now has a third parameter "default" which is used when a property is not found Removed _Io_setEventPostScript and _Io_setEventPretScript in favor of _Io_RegisterMiddleware Improved documentation (It still needs some love though) Improved the verbosity of _Io_DevDebug  
      Newest version (2019-09-29!)
      Autoit-Socket-IO-2.0.0.zip
      Older versions (Not supported anymore)
      Autoit-Socket-IO-1.0.0.zip Autoit-Socket-IO-1.1.0.zip Autoit-Socket-IO-1.3.0.zip Autoit-Socket-IO-1.4.0.zip Autoit-Socket-IO-1.5.0.zip
    • By AoRaToS
      I started working on this program in the summer of 2008 then I stopped cause I faced some problems I couldn't overcome back then. Now that I've practiced more and have become a better scripter/programmer I'm releasing the program to the public to get some opinions. I know it's not a new concept but it's the first program I started besides some small stuff I did just for practice! I won't post the source code yet because it's still under construction, although I'm sure I've posted early stages of the code with bugs in the past in some topic...
      What I wanted was a simple, small, serverless program that would work without installation cause I wanted it for where I work, so I ended up with this!
      I have attached some images of various versions, also visit the forum thread.
      The package includes s!mpL3 LAN Messenger and the full change log.
      Current version 2.9.9.1! [04/07/2019]
      Check the Change Log below!
       
       
      http://www.autoitscript.com/forum/index.php?showtopic=88782
       
       
       
      Read the license before using this software.
       
    • By Skysnake
      Hi
      I am trying to set Accelerator keys from an array.
      I select the KEY and CONTROL from a SQLite table, the Array looks like that generated for the Helpfile, but I can't get the CONTROLS to resolve...
      I though about Assign & Eval, but not sure if that's a step in the right direction. IsDeclared shows that the $var exists in Local Scope -1.  
       
      Local $Main = GUICreate("Custom MsgBox", 225, 80) GUICtrlCreateLabel("Please select a button.", 10, 10) Local $idButton_Yes = GUICtrlCreateButton("Yes", 10, 50, 65, 25) Local $idButton_No = GUICtrlCreateButton("No", 80, 50, 65, 25) Local $idButton_Exit = GUICtrlCreateButton("Exit", 150, 50, 65, 25) Local $query, $aResult, $iRows, $iColumns $query = "" ;reset $query = "Select hotkey_key, hotkey_ctrl from mytable where mykeys = 'hotkey' ; " ; ; Query $iRval = _SQLite_GetTable2d($sqliteDb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then Local $sizeofHotkeys = UBound($aResult) - 1 ConsoleWrite("$sizeofHotkeys " & $sizeofHotkeys & @CRLF) If $sizeofHotkeys > 0 Then Local $main__aAccelKeys[$sizeofHotkeys][2] For $i = 0 To $sizeofHotkeys - 1 $j = $i + 1 ; replace friendly text with code -- ! alt + Shift ^ Ctrl # Windows $aResult[$j][0] = StringReplace($aResult[$j][0], "Alt", "!") $aResult[$j][0] = StringReplace($aResult[$j][0], "Shift", "+") $aResult[$j][0] = StringReplace($aResult[$j][0], "Ctrl", "^") $main__aAccelKeys[$i][0] = $aResult[$j][0] ;--- $main__aAccelKeys[$i][1] = $aResult[$j][1] ;--- Next ;~ Row|Col 0|Col 1 ;~ Row 0|F2|$idButton_Yes ;~ Row 1|F3|$idButton_No _DebugArrayDisplay($main__aAccelKeys) Local $rv = GUISetAccelerators($main__aAccelKeys, $Main) GUISetState(@SW_SHOW) ; Display the GUI.  
      Please note that this is a modified Helpfile example.
      The Helpfile specifies (a) WinHandle and (b) last Gui created.
      --> the example uses a control not a WinHandle and (b) what happens with ChildGuis? Also, the HelpFile specifies lower case, yet the examples show "{F1}" upper case?
      Also, is there a way to check the result of the GuiSetAccelerator function? 
      Note, if I add these to lines after the FOR loop, then the F1 works, and the DebugArrayDisplays shows control 4... not it's name... So I am in the right place, but my $vars names do not convert to their control numbers in the GUI
      Next $main__aAccelKeys[$sizeofHotkeys - 1][0] = "{F1}" ; -- -- use the extra row for the F1 $main__aAccelKeys[$sizeofHotkeys - 1][1] = $ChmHLP ;--- Skysnake
    • By Skeletor
      Hi All,
      Here's a really simple question. 
      I ran the code from the helpfile under: _SQLite_Open

      Issue is I end up with an error message: SQLite3.dll Can't be Loaded!
      I placed the *.dll in the include folder, but still nothing. 

      Where must this file be placed.
       
    • By HansHenrik
      ----------
      edit: this is probably in the wrong place, can a moderator move it to wherever it belongs?
      ----------

      is there any way to completely disable TCPTimeout and make TCPRecv() wait indefinitely? maybe setting it to 0 or -1 or something? 
      the docs doesn't seem to mention any way to disable it

      - the underlying C code would set SO_RCVTIMEO to 0 , aka

      DWORD timeout=0;
      setsockopt(s, SOL_SOCKET, SO_RCVTIMEO, &timeout, sizeof(timeout));
×
×
  • Create New...