SQLITE Query Successful command line faile RR

[Kevin]

This query is successful from the command line but fails with revdberr. Any suggestions?

SELECT strftime('%s','now','localtime');

[Kevin]

SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT datetime('now');
2009-10-30 20:26:17
sqlite> SELECT datetime('now', 'localtime');
2009-10-30 15:26:50
sqlite> SELECT strftime('%s','now','localtime');
1256916457
sqlite>

[Mark]

Dear Kevin,

Please post (the relevant part of) your Revolution script.

Best,

Mark

[Kevin]

on mouseUp

local _db
local _now

--
--In memory only no database file needed here
--
put revOpenDatabase("sqlite",,,,,,,) into _db
put revDataFromQuery(comma,return,"SELECT datetime('now');") into _now
revCloseDatabase _db

end mouseUp

[Mark]

Kevin,

In

revOpenDatabase("sqlite",,,,,,,)

you have to specify not only the type of database, but also the database name or file path and possibly a user name and password.

Note that the command line version and the Revolution version do not interact in any way. The two have nothing to do with each other, except that both of them were compiled using the same library.

Best,

Mark

[Kevin]

The SQLite creates a in memory only database when no file is specified. This is common with using the library.

[Kevin]

I have used this exact same behavior in C++, C, REALBasic and several other languages!

[Kevin]

C:\SQLite\sqlite-3_6_19>sqlite3.exe
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT datetime('now' 'utc');
SQL error: near "'utc'": syntax error
sqlite> SELECT datetime('now','utc');
2009-10-31 03:22:24
sqlite>

[Kevin]

Function LocalToUTC( ByVal As Date ) As Date

Dim oDb As New REALSQLDatabase
Dim iOffset As Integer
Dim oError As New Date

oError.TotalSeconds = 0

If False = oDb.Connect Then

Dim eNoTimeService As New RuntimeException
eNoTimeService.Message = "Missing SQL Time subsystem cannot initialize"

Raise eNoTimeService

End If

Dim sSQL As String

sSQL = "SELECT strftime(""%s&"", 'now', 'localtime') - strftime(""%s"", 'now')"

Dim oRs As Recordset

oRs = oDb.SQLSelect(sSQL)

If Nil <> oRs Then

iOffset = oRs.IdxField(1).IntegerValue

o.TotalSeconds = o.TotalSeconds - iOffset

Return o

End If


Return oError


End Function

[Kevin]

As shown in the examples there is no file used it is in memory. Therefore no need for userid or password since I only have the handle.

Thanks,
Kevin

[Mark]

Kevin,

I'm sorry that you are unhappy with the solution I give you, but it is either that or nothing.

Best,

Mark

[Kevin]

Just found it odd. I am not aware of the functionality Runtime Revolution implements or does not. I have used many forums in the past and have have been given misleading advise just wanted to ensure we we speaking of the same thing.

Kevin

[Kevin]

This is from the example (SQLite Sampler.rev) only altered the SQL. Can create tables and insert data. Just cannot see to execute any SQLite date time functions.

on mouseUp
local _db
local _now

put revOpenDatabase("sqlite","AppReg3.db",,,,,,) into _db
put revDataFromQuery(comma,return,"SELECT datetime('now','utc');") into _now
revCloseDatabase _db
end mouseUp

[Kevin]

put revDataFromQuery(comma,return,"SELECT datetime('now');") into _now

Forgot the handle to the database.

[Kevin]

on mouseUp
local _db
local _now

put revOpenDatabase("sqlite",,,,,,,) into _db
put revDataFromQuery(comma,return,_db,"SELECT datetime('now','utc');") into _now
revCloseDatabase _db

end mouseUp