how to Get one table's Recordcount in client.

longge007 · 2010-07-06 08:12:03

in the Client,how to get the recordcount of one database[SampleRecord]? i looking and looking in the SQLite ,can't find one methord to realize it.
whether or not to use Select query Parameter.

Last edited by longge007 (2010-07-06 08:17:02)

ab · 2010-07-06 12:28:11

You can use the following SQL statement:

SELECT Count(*) FROM TableName;

I've added a dedicated method in SQLite3Commons unit:

function TSQLRest.TableRowCount(Table: TSQLRecordClass): integer;
var T: TSQLTableJSON;
begin
  if (self=nil) or (Table=nil) then
    T := nil else
    T := InternalListJSON(Table,'SELECT Count(*) FROM '+Table.SQLTableName);
  if T<>nil then
  try
    Result := T.GetAsInteger(1,0);
  finally
    T.Free;
  end else
    Result := -1;
end;

The Source code Repository has been updated for this method.

longge007 · 2010-07-07 06:28:17

thanks a lot ,i have used it my pro.
suddenly ,i have one suggestion, our FrameWork should have an interface like SQL RecordSet .

ab · 2010-07-07 07:19:24

As far as I understand the RecordSet feature, it's more or less the same as a TSQLTableJSON class in our Framework.

There is even one unique feature: you can use the TSQLRestClientURI.UpdateFromServer() method to refresh the content of a TSQLTableJSON.

Of course, one big difference is that the TSQLTableJSON is read-only. You can't modify data in its set, but I think our ORM approach, via the Retrieve() and Update() methods, is much more productive, when you are dealing with updating values.

longge007 · 2010-07-07 08:38:04

i want to realize this feature like SQL
select A1,A2 from Table1 where ... or select A1 from table1,table2 where Table1.A1Table2.A1.
in our Framework
i don't know what's structure in our return Data,Can you See detail. or tell me Which function or procedure can be used.
or our Framework have one interface that can Execute SQL pamameter directly and return tabaleJson type DATA.

otherwise , I spend much time in study your source code of Framewok.

Last edited by longge007 (2010-07-07 08:41:25)

longge007 · 2010-07-07 08:49:24

get multi field values from one Table , use this function

function TSQLRest.MultiFieldValue(Table: TSQLRecordClass;
const FieldName: array of RawUTF8; var FieldValue: array of RawUTF8;
const WhereClause: RawUTF8): boolean;

Last edited by longge007 (2010-07-07 08:52:56)

ab · 2010-07-07 08:55:40

Use ExecuteList() method on the Client side to create a TSQLTableJSON from a SQL statement. It'll be like a RecordSet.

MultiFieldValue() use this method internaly, and expect only one field request.

longge007 · 2010-07-07 09:15:32

ab wrote:

Use ExecuteList() method on the Client side to create a TSQLTableJSON from a SQL statement. It'll be like a RecordSet.
MultiFieldValue() use this method internaly, and expect only one field request.

Multi tables Query realize in Framework
short thems:
a=adduser, b=order ,c=orderdetail.

select a.name,b.time,c.* from a,b,c
where a.userid=b.userid and b.orderid=c.orderid

where is ExecuteList() ,i can't find In Framework,Client side should use which SQLclass. please write detail. thanks a lot

var
   cli:TSQLRestClientURI;
   SQLstr:string;
   aTables: array of TClass;
   ResultJson:TSQLTableJSON;
begin
    setLength(aTables,3)
    aTables[0]:=TSQLadduser; aTables[1]:=TSQLorder;aTables[3]:=TSQLorderdetail;
    SQLstr:='select a.name,b.time,c.* from a,b,c where a.userid=b.userid and b.orderid=c.orderid';
    ResultJson:=Cli.ExecuteList(aTables,stringtoUTF8(SQLstr));
end;

function TSQLRestClientURI.ExecuteList(const Tables: array of TClass;
const SQL: RawUTF8): TSQLTableJSON;

function TSQLRestClientURI.List(const Tables: array of TClass;
const SQLSelect, SQLWhere: RawUTF8): TSQLTableJSON;

These two functions can get the same results,aren't them?

Last edited by longge007 (2010-07-07 13:35:22)

ab · 2010-07-07 18:12:31

You can write with shorter code:

ResultJson := Cli.ExecuteList([TSQLadduser,TSQLOrder,TSQLorderdetail],
  'select a.name,b.time,c.* from a,b,c where a.userid=b.userid and b.orderid=c.orderid');

But I'm not sure your a,b,c shortcut is correct, you have to assign them explicitely to adduser, order, and orderdetail.

ab · 2010-07-08 09:47:29

And don't forget to use a try...finally block to release the ResultJson memory:

ResultJson := Cli.ExecuteList([TSQLadduser,TSQLOrder,TSQLorderdetail], ....
if ResultJson<>nil then // not nil if succeeded, nil if error during running the SQL query
try
  // do something with ResultJson
finally
  ResultJson.Free;
end;

longge007 · 2010-07-08 11:55:35

thanks a lot
Sir: i want to know whether or not our SQLite3HTTpServer can be used as an CGIServer,
or thus:
TFastCgiServer.Creat()

ab · 2010-07-09 07:46:02

SQLite3HTTpServer is a stand alone server: it does'nt need any additional http server (like IIS, Apache).

TFastCGIServer is a Fast-CGI server so it can't be used directly as a CGI Server, just as Fast-CGI. Note that most http servers do support the FastCgi protocol (you may have to install the corresponding module).

Our fastcgi implementation is not 100% tested. It was mostly a proof of concept. If you need it, I'll debug it.

longge007 · 2010-07-09 07:59:06

Sir : I don't know how to use TSQLRestClientURI.ExecuteList(); check my steps:
Step1: Creat Httpclient.

Database:= TSQLite3HttpClient.Create(edit1.Text,'8080',Form1.Model);

this works fine.
Stpe2. GetJson procedure

  var
   cli:TSQLRestClientURI;
   SQLstr:string;
   ResultJson:TSQLTableJSON;
begin
//My table1 is TSQLSampleRecord ,My table2 is TSQLInspectRecord
//table1  SampleRecord
//*id                  time                  name    question
//*1         2010-07-09T10:40:29     a         test
//*2         2010-07-09T10:40:31     b         test
//*3         2010-07-09T10:40:34     c         test
//*4         2010-07-09T10:40:40     d         test
//*********************

//table2  InspectRecord
//*id      IR_ID              time                       Ms_Code    IR_Value
//*1        1             2010-07-09T10:40:29       1000001         100.0
//*2        2             2010-07-09T10:40:33       1000003          20.0
//*3        3             2010-07-09T10:40:34       1000004          30.50
//*4        4             2010-07-09T10:40:40       2000001          40.66
//*5        5             2010-07-09T10:40:43       2000003          80.66
//*********************

    SQLstr:='select SampleRecord.name,Inspectrecord.IR_Value from SampleRecord,Inspectrecord where SampleRecord.Time=Inspectrecord.IR_Time';
    ResultJson:=Cli.ExecuteList([TSQLSampleRecord,TSQLInspectRecord],stringtoUTF8(SQLstr));
    if ResultJson<>nil then
    try
       edit3.Text:=inttostr(ResultJson.RowCount);
    finally
       ResultJson.Free;
    end;
end;

what's the Matter ? there is no resultJson and have Error.
First chance exception at $75569617. Exception class EAccessViolation with message 'Access violation at address 004859C7 in module 'Project04Client.exe'. Read of address B2D88B93'. Process Project04Client.exe (2652)

Last edited by longge007 (2010-07-09 08:02:54)

longge007 · 2010-07-09 09:07:13

ab wrote:

SQLite3HTTpServer is a stand alone server: it does'nt need any additional http server (like IIS, Apache).
TFastCGIServer is a Fast-CGI server so it can't be used directly as a CGI Server, just as Fast-CGI. Note that most http servers do support the FastCgi protocol (you may have to install the corresponding module).
Our fastcgi implementation is not 100% tested. It was mostly a proof of concept. If you need it, I'll debug it.

thanks a lot,
when i input ''192.168.1.101:8080/root/samplerecord/1' in IE address, a file saving is return. SQLhttpserver is running fine.
now I check it In Ajax request,

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<script language="javascript" src="js/zxml.js"></script>
<script> 
function Test(){
var oXmlHttp=zXmlHttp.createRequest(); //one Ajax Class,
var url='192.168.1.101:8080/root/samplerecord/1';
oXmlHttp.open("put",url,true);
oXmlHttp.setRequestHeader("Cache-Control","no-cache"); 
        oXmlHttp.setRequestHeader('Content-Type', 'application/Json');
        oXmlHttp.onreadystatechange=function(){
            if (oXmlHttp.readyState==4){
                if (oXmlHttp.status==200){
                    alert(oXmlHttp.responseBody);
                    
                }else{location.reload();}
            }
        }
        oXmlHttp.send(null);
}
</script>
</head>
<body>
<input type=button value='test' onClick='Test()'/>
</body>
</html>

Error info: rufuse visit in line 10:oXmlHttp.open("put",url,true);

ab · 2010-07-09 11:43:18

Use "get" instead of "put" here

ab · 2010-07-09 11:45:22

longge007 wrote:

what's the Matter ? there is no resultJson and have Error.
First chance exception at $75569617. Exception class EAccessViolation with message 'Access violation at address 004859C7 in module 'Project04Client.exe'. Read of address B2D88B93'. Process Project04Client.exe (2652)

I suspect you use Cli instead of Database, and Cli seems not initialized in your code....

longge007 · 2010-07-09 13:01:36

I have added these codes,Initialized the cli

    Cli:=TSQLRestClientURI.Create(Model);
    SQLstr:='select samplerecord.name,Inspectrecord.IR_time from samplerecord,Inspectrecord';
    ResultJson:=Cli.ExecuteList([TSQLSampleRecord,TSQLInspectRecord],SQLstr);
    if ResultJson<>nil then
    try
       edit3.Text:=inttostr(ResultJson.RowCount);
    finally
       ResultJson.Free;
    end;

i debug and trace it. **with URI(Model.Root,'GET',@Resp,nil,@SQL) do** here is the same Error [Abstrac Error]

Last edited by longge007 (2010-07-09 13:02:05)

ab · 2010-07-09 14:49:13

you have to use Database instead of this TSQLRestClientURIinstance which is pointing to nothing.

TSQLRestClientURI is an abstract class. You have to use a true client instance, like TSQLite3HttpClient.

So your code should be:

    ResultJson := Database.ExecuteList([TSQLSampleRecord,TSQLInspectRecord],
     'select samplerecord.name,Inspectrecord.IR_time from samplerecord,Inspectrecord');
    if ResultJson<>nil then
    try
       edit3.Text := inttostr(ResultJson.RowCount);
    finally
       ResultJson.Free;
    end;

longge007 · 2010-07-09 16:01:51

[DCC Error] Unit1.pas(128): E2003 Undeclared identifier: 'ExecuteList'
at first i want to use Database to EXecuteList.
Database does't support this functions,ExecueList is declared in childclass TSQLRestClientURI, In the Class of TSQLRest haven't this funcitons.

Last edited by longge007 (2010-07-09 16:13:04)

ab · 2010-07-10 07:36:50

TSQLite3HttpClient inherits from TSQLRestClientURI which has the ExecuteList method.

So you must have a problem about your Database declaration. You may have declared it as:

Database: TSQLRest;

But this is not a good idea: the TSQLRest class is so generic that you'll miss most useful properties and methods.

Please declare it as:

Database: TSQLRestClientURI;

Or even:

Database: TSQLite3HttpClient ;

Perhaps try to spend some time in your manual or best book about Delphi classes, inheritance and instances. It's worth looking into all these in details, it will spare you a lot of time during coding!
See for example http://www.delphibasics.co.uk/Article.asp?Name=OO

longge007 · 2010-07-10 11:59:47

thanks a lot, TSQLRest is a genaral Class, TSQLite3HttpClient and TSQLRestClientURI are subclasses.i only learn your example,not think more.
yeah,i need to learn more about classes.

ab · 2010-07-10 12:48:51

mORMot Open Source

#1 2010-07-06 08:12:03

how to Get one table's Recordcount in client.

#2 2010-07-06 12:28:11

Re: how to Get one table's Recordcount in client.

#3 2010-07-07 06:28:17

Re: how to Get one table's Recordcount in client.

#4 2010-07-07 07:19:24

Re: how to Get one table's Recordcount in client.

#5 2010-07-07 08:38:04

Re: how to Get one table's Recordcount in client.

#6 2010-07-07 08:49:24

Re: how to Get one table's Recordcount in client.

#7 2010-07-07 08:55:40

Re: how to Get one table's Recordcount in client.

#8 2010-07-07 09:15:32

Re: how to Get one table's Recordcount in client.

#9 2010-07-07 18:12:31

Re: how to Get one table's Recordcount in client.

#10 2010-07-08 09:47:29

Re: how to Get one table's Recordcount in client.

#11 2010-07-08 11:55:35

Re: how to Get one table's Recordcount in client.

#12 2010-07-09 07:46:02

Re: how to Get one table's Recordcount in client.

#13 2010-07-09 07:59:06

Re: how to Get one table's Recordcount in client.

#14 2010-07-09 09:07:13

Re: how to Get one table's Recordcount in client.

#15 2010-07-09 11:43:18

Re: how to Get one table's Recordcount in client.

#16 2010-07-09 11:45:22

Re: how to Get one table's Recordcount in client.

#17 2010-07-09 13:01:36

Re: how to Get one table's Recordcount in client.

#18 2010-07-09 14:49:13

Re: how to Get one table's Recordcount in client.

#19 2010-07-09 16:01:51

Re: how to Get one table's Recordcount in client.

#20 2010-07-10 07:36:50

Re: how to Get one table's Recordcount in client.

#21 2010-07-10 11:59:47

Re: how to Get one table's Recordcount in client.

#22 2010-07-10 12:48:51

Re: how to Get one table's Recordcount in client.

Board footer