Seemed data lost in remote mode?

[cyberazor]

When I connected the server and then test statement "SELECT * FROM sqlite_master;" with sample.db3, it will return some error data, but it works fine in local mode.
Am I code wrong with slGetRow?

[Rolf Brandt]

Could you post the code for the slGetRow part?

Rolf

[cyberazor]

Hi Rolf, thank you for reply.

I used a class from this post:
http://www.sqlitening.com/support/index.php?topic=3169.msg16265#msg16265

I just call the GetDataTable() function to retrieve some rows to fill a DataGridView conrol (VB.Net):

Code Select Expand

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Show()
        Dim sqlitening As New Class1
        sqlitening.connect("LocalHost", 926, "E2", "sample.db3", "")
        DataGridView1.DataSource = sqlitening.GetDataTable("SELECT * FROM sqlite_master;", "E2")
        DataGridView1.Refresh()
    End Sub
End Class

Once a time I met a mistake looked alike, for which I reduced the timeout setting in connection, then I reset the configuration to default so I can retrieve a complete DataTable. But now this method failed.

[cyberazor]

Here is my test project which is target to .Net Framework 4.

In serverside I just changed the port to 926.

[D. Wilson]

Have you tried to loop through row by row to see if the data is there. My thought is the datasource property may not recongize the data,

[cyberazor]

Hi Wilson,

I traced each row of the first column to a textbox, but the result is just like before.

[cyberazor]

I changed the test statement "SELECT * FROM sqlite_master;" and test as follows:

Right: "SELECT type FROM sqlite_master;"
Right: "SELECT type,name FROM sqlite_master;"
Right: "SELECT type,name,tbl_name FROM sqlite_master;"
Right: "SELECT type,name,tbl_name,rootpage FROM sqlite_master;"
WRONG: "SELECT type,name,tbl_name,rootpage,sql FROM sqlite_master;"
WRONG: "SELECT type,sql,tbl_name,rootpage FROM sqlite_master;"

So in this case it seemed something wrong while server return data and/or client recognize the returns?
Maybe the [sql] column is too long or have carriage return characters?

[cyberazor]

I test "select rowid,* from parts;". It returns more than 34700000 rows (which should be 10000)... then I Interrupted it...

[D. Wilson]

My guess is there is an issue with the column/row delimiter. I do not know how the delimiters are added when you try and retrieve the entire table.

I do not use the the command you used. I use the 'special' version of the dll. I loop through the rows and process each value by using the field name. I wish I could be more helpful.

In my experience I have never had an issue the SQLitening losing data. If you have proper error checking you can determine if there is an issue when updating records. And I have never lost information when retrieving data from the database.

[cyberazor]

My guess is there is an issue with the column/row delimiter.

I suppose. So is there anyway to log or assign the delimiter?

I use the 'special' version of the dll. I loop through the rows and process each value by using the field name.

I used SQLiteningS.dll too.

If you have proper error checking you can determine if there is an issue when updating records.

Importing data is all successed, and can be read out in local mode, but failed in c/s mode with the same database file.

Thank you for reply, I'm now trying to reinstall the system of server, hope it's helpful...

[cj]

I wrote a SQLitening function executor a couple of years ago and this is the results of running
SELECT * FROM sqlite_master from a server running on another machine on the internet:

Code Select Expand


table  parts  parts  2  CREATE TABLE [parts] (
  [MANUF] TEXT NOT NULL,
  [REDREF] TEXT,
  [PRODUCT] TEXT,
  [LANGUAGE] TEXT,
  [CPU_OS] TEXT,
  [MEDIA] TEXT,
  [TYPE] TEXT,
  [PGROUP] TEXT,
  [PRICE] NUMERIC) 
index  MANUF  parts  3  CREATE INDEX [MANUF] ON [parts] ([MANUF]) 
table  t1  t1  1316  CREATE TABLE t1(f1) 


select count(*) from parts;
returns 10000

select rowid from parts;
returns 3880 lines not all sequential.

Not sure what is going on, investigating.
Large recordsets are being truncated running client/server and running locally.

File size 1,346,560 before vacuum
File size 1,324,032 after vacuum on server and also locally.

Loaded parts table of sample.db3 into SQLite Expert Professional and
there are 10,000 rows.  Now investigating why my function executor only returns 3880 rows.

[cyberazor]

Hi Rolf,

By testing your VB6 project in this post:
http://www.sqlitening.com/support/index.php?topic=3433.msg17545#msg17545
the data error still occured, please see the last 2 rows.

Both server and client files are shifted to v1.54.

[cyberazor]

Hi cj,

select count(*) from parts;
returns 10000

Me too.

select rowid from parts;
returns 3880 lines not all sequential.

My result is 10000 rows just right.

But while I test:

Code Select Expand

select rowid,* from parts;
the result rows seems infinitely returned (more than 34700000 rows)...

[cj]

This correctly returns all the 10,000 rows.

Results are returned into results.txt and shelled to so it loads in my default
.txt viewer notepad. 
Please change the IP address and port on the slConnect line.

Added a counter RowCount to physically count the lines returned
and it will be displayed at the end of the results.


Hope this is of some help.

Code Select Expand


#INCLUDE "\sql\inc\sqlitening.inc"                 'sqlite routines
#INCLUDE "win32api.inc"
$OutputFile = "results.txt"
FUNCTION PBMAIN () AS LONG  'testsample.bas
  LOCAL COLUMN,LastColumn AS LONG,sData AS STRING  'define variables
  LOCAL RowCount AS LONG
  slConnect "192.168.1.2",51234                   'connect remote
  OPEN $OutputFile FOR OUTPUT AS #99
  'slConnect "192.168.1.2",51234                 'connect remote server on port 51234
  slOpen "sample.db3","C"                        'Open database or create if not exists
  slsel  "select * from parts;
  LastColumn = slGetColumnCount                    'number of columns
  RowCount = 0
  DO WHILE slGetRow                                'while row exists loop
    INCR RowCount
    sData = ""
    FOR COLUMN  = 1 TO LastColumn                  '  column loop
      sData = sData + slf(COLUMN) + $TAB           '    combine columns
    NEXT                                           '  loop
    sData = LEFT$(sData,(LEN(sData)-1))
    PRINT #99, sData
  LOOP                                             'row loop
  PRINT #99, "RowCount";RowCount
  CLOSE #99
  SLEEP 1000 'give system some time to complete
  Sheller
END FUNCTION
FUNCTION Sheller AS LONG
  LOCAL zText AS ASCIIZ * 256
  zText = $OutputFile
  ShellExecute (%NULL, "OPEN", zText, BYVAL %NULL, CURDIR$, %SW_SHOWNORMAL)
END FUNCTION



slsel "SELECT * FROM sqlite_master

Code Select Expand


table parts parts 2 CREATE TABLE [parts] (
  [MANUF] TEXT NOT NULL,
  [REDREF] TEXT,
  [PRODUCT] TEXT,
  [LANGUAGE] TEXT,
  [CPU_OS] TEXT,
  [MEDIA] TEXT,
  [TYPE] TEXT,
  [PGROUP] TEXT,
  [PRICE] NUMERIC)
index MANUF parts 3 CREATE INDEX [MANUF] ON [parts] ([MANUF])
RowCount 2


[cyberazor]

Thanks for your code cj, I compiled it under PB 10.03, but the result is just like before.