Remote Server Mode Problem

[Marty Francom]

    I have a probelm after getting connected to the database on
my "server".  It is not a true server but Client machines on a
pier to pier LAN.
     I can connect and open the database.  But my router was
blocking  port 51234           The only port that
I could get to work was port 80.  I am beginning to think
that the problem I have may be caused by my router.
I have a Comcast Cable Modem - Gateway/Router
combination devise.   
THE PROBLEM:
     I get connected OK and even was able to write a record.
But,   I get error messages when  I try to get read any data.    I made
some screen prints of this messages.  If you have a moment to
look at them, maybe they will mean something to you.
NOTE:    If I run local mode then there is no problem
in reading the data.
    Here's some screen prints of the error messages.
...Marty
 

[Rolf Brandt]

Hi Marty,

which indexes does your database have?  If you have defined a field as primary index then there might be no rowid. I think I had experienced the same problem once. Then you get this cryptic error message.

When it says 'near CT' that means the error is in you query near SELECT (that's where the CT comes from). Next to SELECT is rowid in your query.

Rolf

[Fred Meier]

You shold get error -13 if you have assigned Integer Primary Key to a column and you ask for RowId, *.   Post your Create Table statement and Select statement.

[Marty Francom]

Why would this ONLY happen when in REMOTE server mode?

Everthing runs fine in LOCAL mode.

[Matt Humphreys]

can you post the offending code please marty?

[Fredrick Ughimi]

Hello Fred,

>>You shold get error -13 if you have assigned Integer Primary Key to a column and you ask for RowId, *.   Post your Create Table statement and Select statement.

Are you saying one would get error -13 in remote mode if one does this?

Code Select Expand


slExe "Create Table If Not Exists T1 (F1 Integer Primary Key, F2)"
slSEL "SELECT RowID as RecordNo, * FROM T1 WHERE F1 = '" + Str$ + "'"


Please clarify.

Best Regards,

[Fred Meier]

No, because you added the As clause, which is the correct way to avoid error
-13.  Check out the "D" modchar on the slSel, that is another way (not
recommended) to avoid error -13.  Your statement without the As clause
will result in error -13. 

Code Select Expand

   slSEL "SELECT RowID, * FROM T1"

[Fredrick Ughimi]

Fred,

Thank you for the clarification because I use these codes in all parts of my program.

Best regards,

[Marty Francom]

You shold get error -13 if you have assigned Integer Primary Key to a column and you ask for RowId, *.   Post your Create Table statement and Select statement.

Fred,
   Here's the section of code the error occurs in:

Code Select Expand


       
       If Trim$(sTxt) = "" Then sTxt = "A"
       If Trim$(sFld) = "" Then sFld = "DrName"
       Select Case sFld
          Case "DrName", "DrDEA", "DrNPI", "DrPhoneOff"   'String
             Sql1 = "SELECT rowid, * FROM Doctors WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY  [" & sFld & "]"
          Case Else               'Numerical
             Sql1 = "SELECT rowid, * FROM Doctors WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY  [" & sFld & "]"   
       End Select
       
       If  direction = "<" Then
          Sql1 = Sql1 & " DESC LIMIT 1"
       Else
          Sql1 = Sql1 & " LIMIT 1"
       End If
   
       DrClearMask (1)
       FF_Control_SetText (HWND_FormDOCinfo_lblDOCsqlCmd, Sql1)
   
        'MsgBox Sql1,, "before Step1"
       
       'Step 1 - Find exact or closest match
       'slOpen gDBName
       slSel Sql1,0,"E1" 
       lErr = slGetError
       If Val(lErr) Then
          MsgBox lErr & "-Search Step1", ,"Error"
          Exit Function   
       End If
   


Should I change my SELECT statement to add the " As RecordNo "   like so:

Code Select Expand


Sql1 = "SELECT rowid as RecordNo, * FROM Doctors WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY  [" & sFld & "]


[Fred Meier]

You didn't post your Create statement.   Do you have a column defined with Integer Primary Key?

If yes, then there is no reason to select RowID cause it is same as the above column name.  If you still want to select both (wast of time) then yes the "as RecordNo" should work.

[Marty Francom]

Fred,
   After making the change to add the  " As RecordNo,"    I still get the same error message.   

Note:   The program runs fine in LOCAL MODE  I only get this problem when it's REMOTE MODE.

Why would the program run fine in LOCAL mode and error in REMOTE mode?

Here's the error message:

[Marty Francom]

Fred,
   Does this help.  Screen print.   Also attached database (it's small)  if you can see the info you are asking by looking at the database.

[Fred Meier]

The following runs fine in both local and remote mode against the database you posted.
You don't need the as RecordNo since you are not using Integer Primary Key.

Code Select Expand

   slConnect
   slOpen "Marty.Sld"
   slSel "SELECT RowID, * FROM CfgRec WHERE [CfNum] >= '1' ORDER BY [CfNum] LIMIT 1"

I have tried many things but can not get error 1 = near "CT": syntex error. 

I suggest you compile and run the above 3 statement program using remote LocalHost and see if you still get same error, cause I do not.

[Rolf Brandt]

Marty,

instead of :
slSel "SELECT RowID, * FROM CfgRec WHERE [CfNum] >= '1' ORDER BY [CfNum] LIMIT 1"

try this:
slSel "SELECT * FROM CfgRec WHERE [CfNum] >= '1' ORDER BY [CfNum] LIMIT 1"

I assume you have defined a field as primary index. In that case rowid will not be available but your indexed field will take that plaxe. rowid will not be available then. I tried that and I get the same error.

[Marty Francom]

Fred,
   I did what you suggested.  And still get the same error. 

I could zip up the FF3 project file and email it to you if you have the time to take a look.  I understand that this is well beyong normal SQLitening support, and I would be more than happy to compensate you for your time.

Here's the startup code:

Code Select Expand


Function FF_WINMAIN( ByVal hInstance     As Dword, _
                     ByVal hPrevInstance As Dword, _
                     ByVal lpCmdLine     As Asciiz Ptr, _
                     ByVal iCmdShow      As Long ) As Long

   Local t As Long
   Local sFld As String
   Local sVal As String 
   Local sPort As String
   Local sIP As String
   Local sDBpath  As String
   Local sDBname  As String   
   Local msg As String 
   
   'gDBName = "ApotheSQL.db3"
   gIniFile = Exe.Path$ & "ApotheSQL.ini"
   sDBname = FF_INI_GetKey (gIniFile, "Settings", "DBname", "ApotheSQL.db3" )
   sDBpath = FF_INI_GetKey (gIniFile, "Settings", "DBpath", Exe.Path$ )
   sIP = FF_INI_GetKey (gIniFile, "Settings", "IP", "" )
   sPort = FF_INI_GetKey (gIniFile, "Settings", "Port", "" )
   
   If Len(sIP) > 5 Then
       ' open on server
       slConnect (sIP, Val(sPort))
       gDBopen = slOpen (sDBname, "E")
       msg = "Server" & $CrLf & sIP & $CrLf & sPort & $CrLf & sDBname & $CrLf & Str$(gDBopen)
      ' Fred's suggested test
      slSel "SELECT RowID, * FROM CfgRec WHERE [CfNum] >= '1' ORDER BY [CfNum] LIMIT 1"
      ? "Select statement"   
   Else
       ' open on client (no server)
       gDBopen = slOpen (sDBpath & sDBname, "E")
       msg = "Local" & $CrLf & sDBpath & $CrLf & sDBname & $CrLf & Str$(gDBopen)
   End If   
   If gDBopen <> 0  Then
     msg =  msg & $CrLf & "slOpen Failed: error= " & Str$(gDBopen) 
     MsgBox msg, %MB_TASKMODAL Or %MB_ICONERROR, "ERROR"
     Function = %True : Exit Function
   Else
     msg = msg & $CrLf & "Database: " & $CrLf  & sDBName & $CrLf & " Opened Successfully"
     MsgBox msg, %MB_TASKMODAL, "Success"
   End If   

   sFld = "DgInUse, DrInUse, InsInUse, PrcInUse, PtInUse, SgInUse,  CfInUse, RxInUse"
   sVal = ":test" & $Nul & ":" & $Nul & ":" & $Nul & ":" & $Nul & _
          ":" & $Nul & ":" & $Nul & ":" & $Nul & ":"
   slExe slBuildInsertOrUpdate("LastRecNums", sVal, sFld, "RowID= 1")
   ' If this function returns TRUE (non-zero) then the actual WinMain will exit
   ' thus ending the program. You can do program initialization in this function.

   Function = %FALSE    'return %TRUE if you want the program to end.

End Function



I