• Welcome, Guest. Please login.
 
July 16, 2019, 12:21:49 pm

News:

Welcome to the SQLitening support forums!


Seemed data lost in remote mode?

Started by cyberazor, February 21, 2014, 05:24:45 am

Previous topic - Next topic

cyberazor

February 21, 2014, 05:24:45 am Last Edit: February 21, 2014, 05:26:37 am by 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
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

cyberazor

February 23, 2014, 02:15:33 am #2 Last Edit: February 23, 2014, 02:40:44 am by 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):
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

March 04, 2014, 05:55:54 am #7 Last Edit: March 06, 2014, 03:04:24 am by 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

March 04, 2014, 09:39:35 pm #9 Last Edit: March 04, 2014, 10:25:45 pm by cyberazor
QuoteMy guess is there is an issue with the column/row delimiter.

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

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

QuoteIf 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

March 06, 2014, 01:58:00 am #10 Last Edit: March 06, 2014, 03:01:15 am by 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:

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

March 06, 2014, 02:45:13 am #11 Last Edit: March 06, 2014, 02:48:23 am by 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,
Quoteselect count(*) from parts;
returns 10000

Me too.

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

My result is 10000 rows just right.

But while I test:
select rowid,* from parts;
the result rows seems infinitely returned (more than 34700000 rows)...

cj

March 06, 2014, 03:27:45 am #13 Last Edit: March 06, 2014, 03:32:34 am by 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.


#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

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. :(