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

longge007
Member
Registered: 2010-06-22
Posts: 107

how to Get one table's Recordcount in client.

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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.

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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.

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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)

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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.

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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;

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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.

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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)

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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);

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

Use "get" instead of "put" here

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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;

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

[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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

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

Offline

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

longge007
Member
Registered: 2010-06-22
Posts: 107

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

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,134
Website

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

wink

Offline

Board footer

Powered by FluxBB