Full-Path vs FileName - sqlite returns different

[sundale]

While I am playing with dbman v1.0, wirtten by "Marcio Cracel Potomac MD 12/18/2008" encounted strange result that some tables are missing in report.

==============================================
filename=f:\RunBasic\public\orm\Project\Project001-39835.orm
type name tbl_name rootpage
table invoices invoices 2
index sqlite_autoindex_invoices_1 invoices 3
table invoiceopt invoiceopt 4
table fisher fisher 5
index sqlite_autoindex_fisher_1 fisher 6
----------------------------------------------------------------------------------
Inform Database name[file select] [confitm]
filename=Project001-39835.orm
type name tbl_name rootpage
table fisher fisher 2
index sqlite_autoindex_fisher_1 fisher 3
---------


So, I tried code bellow which turn out different query result as above
Any one know the reason?

    cssClass "table", "{ text-align:left; border-style:solid; border-width:1px; border-color:#000000;}"
    cssClass "tr", "{ background:#FFFACD; padding:3px }"

    global #invdb, invfile$, invdata$
    invfile$ = "f:\RunBasic\public\orm\Project\Project001-39835.orm"
    print "filename=";invfile$
    sqliteconnect #invdb, invfile$
    #invdb execute("SELECT type,name,tbl_name,rootpage FROM sqlite_master")
    if #invdb hasanswer() then
        render #invdb
    end if
    #invdb disconnect()
    html "<hr>"
    print "Inform Database name";
    html "<input type='file' id='file' name ='file' value='c:\rbp101b3\public\SQLite.db' />" 
    button #a,"Confirm",[dbase]: print
    wait

[dbase]
    dbase$ = #request get$("file")
    print "filename=";dbase$
    sqliteconnect  #sql,dbase$ 
    #sql execute("SELECT type,name,tbl_name,rootpage FROM sqlite_master")
    if #sql hasanswer() then
        render #sql
    end if
    #sql disconnect()

[sundale]

BTW, How do I add images on my post?
Want to show the screen output by image.

[kokenge]

Not exactly sure what dbman does.
However, I've never ever had any problems with a SELECT * FROM sqlite_master. So I assume it is correct.

Are both files Project001-39835.orm and SQLite.db exactly the same.
If not you will get different results.

[sundale]

Hi kokenge,

It is EXACTLY the SAME file. One is given full name as constant, and the other is selected by windows File selection UI. just like upload command, so maybe the current directory is moved to relevant position. The table fisher is generated lastly. Same file different result.

Sorry, dbman was my saved name. original topic was "User friendly database manager": runbasic.proboards.com/index.cgi?action=display&board=code&thread=890&page=1

I find your comment also, in the thread.

[kokenge]

Is it possible to post the DB schema.
Hard to tell what is going on without seeing the schema.

[sundale]

I can send you the database file, let me know how to send it.

[kokenge]

All I need to see is the schema. Can't be too big.. Just post it here
Sending the file won't help...

Dan

[sundale]

I am not sure what does it mean DB schema in sqlite, but I guess this is what you want:
(result by changing the query to '*" from the previous code, and the copy of screen - copy/paste)

Exit full screen mode
filename=f:\RunBasic\public\orm\Project\Project001-39835.orm
type name tbl_name rootpage sql
table invoices invoices 2 CREATE TABLE invoices (code TEXT primary key, desc TEXT, count TEXT, unit CHAR(4), opt CHAR(1), price TEXT, list TEXT, cost TEXT, shop TEXT, amtlist TEXT, amtprice TEXT)
index sqlite_autoindex_invoices_1 invoices 3 NULL
table invoiceopt invoiceopt 4 CREATE TABLE invoiceopt (code TEXT, content TEXT)
table fisher fisher 5 CREATE TABLE fisher (code TEXT primary key, desc TEXT, idesc TEXT, count TEXT, unit CHAR(4), price TEXT, cost TEXT, P TEXT)
index sqlite_autoindex_fisher_1 fisher 6 NULL
Inform Database name [select file] [confirm]
filename=Project001-39835.orm
type name tbl_name rootpage sql
table fisher fisher 2 CREATE TABLE fisher (code TEXT primary key, desc TEXT, idesc TEXT, count TEXT, unit CHAR(4), price TEXT, cost TEXT, P TEXT)
index sqlite_autoindex_fisher_1 fisher 3 NULL

[kokenge]

I took your table layout and pasted it into a SQLite database manager.
Then turned around and dumped the schema.
To dump the schema it selects information from the "sqlite_manager".
So it has to be working.

The only thing I can think of is that when you load your table it is looking at a older version of your DB:

Here is the schema the DB manager printed:

# --------------------------------------------------------
# SQLite Dump
# Generation Time:Feb 5, 2010 05:41:20
# SQLite Version:1.18
# Database:
# --------------------------------------------------------

# --------------------------------------------------------
# Table structure for: fisher   8 fields
# --------------------------------------------------------
CREATE TABLE 	fisher (
code		TEXT NULL,
desc		TEXT,
idesc		TEXT,
count		TEXT,
unit		CHAR(4),
price		TEXT,
cost		TEXT,
P		TEXT)
;
CREATE UNIQUE INDEX fisher_code ON fisher( code);

# --------------------------------------------------------
# Table structure for: invoiceopt   2 fields
# --------------------------------------------------------
CREATE TABLE 	invoiceopt (
code		TEXT,
content		TEXT)
;

# --------------------------------------------------------
# Table structure for: invoices   11 fields
# --------------------------------------------------------
CREATE TABLE 	invoices (
code		TEXT NULL,
desc		TEXT,
count		TEXT,
unit		CHAR(4),
opt		CHAR(1),
price		TEXT,
list		TEXT,
cost		TEXT,
shop		TEXT,
amtlist		TEXT,
amtprice	TEXT)
;
CREATE UNIQUE INDEX invoices_code ON invoices( code);

You should be able to cut this schema and past it into almost any DB manager and it should create your database.

[sundale]

Hello Dan,

Thanks for your explanation about sqlite. But, I am novice to sqlite and RB.
I have learned RB a month and half, and referencing lots of code from this forum,
and I have just finished Invoicing System project which works well.
Therefore, I had no time to study sqlite yet, just using RB interface only.

To me, there is no difference in using sqlite: stand alone or through RB.
My point is, why reading the same sqlite database file gives different result depending on accessing the file - giving full-path or use windows file accessing interface.

In my project, I always used full-path. So, no chance to notice any reading error.
Today, I have moved the file to RB default directory, then everything seems OK - can read all tables. I do not know what version of sqlite is used in RB v1.0.1 Build 2.44.

I have been programming since 1972, but never faced this kind of problem.
This is why I am reporting it as a bug for RB's shake, and for RB's future development.

Think it this way.
I am a SW tester, do not know anything about the sqlite and RB. But, while testing the program, found this behavior, so I report to bug tracking system.

Thanks.