Is there any better way to upload recordset with JSON or something else?

Question

I have firebird database and need to upload one of database tables to remote mysql server on web. There are thousands records and I dont know how to upload these records. I can upload records one by one with JSON. I'm using POST method.

How can I upload all records in one time or in parts?

Update 1: This codes is working for one by one data update. But it's like such a flood attack. I want to upload all data that I've selected in one time.

Delphi 7 Side HTTP Post Method

function PostURLAsString(aURL: string; code:string): string;
var
  lHTTP: TIdHTTP;
  lStream: TStringStream;
  parameters: TStringList;

begin
  lHTTP := TIdHTTP.Create(nil);
  lHTTP.Request.ContentType := 'application/x-www-form-urlencoded';
  lStream := TStringStream.Create(Result);
  try
    Parameters := TStringList.Create;
    parameters.Add('code=' + code);
    lHTTP.Post(aURL, parameters,lStream);
    lStream.Position := 0;
    Result := lStream.ReadString(lStream.Size);
  finally
    FreeAndNil(lHTTP);
    FreeAndNil(lStream);
  end;
end;

Upload Records One by One:

procedure TForm1.Button1Click(Sender: TObject);
var
  js:TlkJSONobject;
  jb: TlkJSONbase;
  s: String;
  code:string;
begin
  IBQuery1.First;
  with IBQuery1 do
  while not Eof do
  begin
    code :=   VarToStr(FieldValues['code']);
    s := PostURLAsString('http://www.domain.com/uitems.php', code);

    js := TlkJSON.ParseText(s) as TlkJSONobject;
    jb := js.Field['items'];

    if VarToStr(jb.Child[0].Field['status'].Value) = '1' then
      ListBox1.Items.Add(code + ' is inserted')
    else
      ListBox1.Items.Add(code + ' is not inserted');

    Application.ProcessMessages;

    js.Free;
    Next;
  end;
end;

PHP Side uitems.php

<?php
  include_once dirname(__FILE__) .'/DBConnect.php';

  function update($code){

    $db = new DbConnect();

    // array for json response
    $response = array();
    $response["items"] = array();   

    $sqlstr = "INSERT INTO items (`code`) VALUES ('$code')";

    $result = mysql_query($sqlstr);



    $tmp = array();

    if ($result) {
        // successfully updated
        $tmp["status"] = 1; //inserted
    } else {
        $tmp["status"] = 0; //not inserted
    } 

    array_push($response["items"], $tmp);

    header("Content-Type: application/json; charset=utf-8", true);      
    // echoing json result
    echo json_encode($response, JSON_UNESCAPED_UNICODE);

  }

  update($_POST["code"]);

?>

See http://stackoverflow.com/questions/19871854/export-firebird-database-to-sql — eggyal, Mar 21 '15 at 23:13
I have a software project somewhere which ports an IB database to Oracle. You are welcome to use it if you want - you would need to rewrite a portion of it to support MySql — Hugh Jones, Mar 22 '15 at 11:14
@eggyal I know how to export as Sql. But I asked different thing. — RedLEON, Mar 23 '15 at 12:31
@RedLEON: Your question title asks "Is there a better way...with JSON **or something else**?" — eggyal, Mar 23 '15 at 12:35
@eggyal You are right. So you said better way is upload database with SQL codes. Than how to upload sql file and query them programeticaly. — RedLEON, Mar 23 '15 at 12:37

score 2 · Answer 1 · answered Mar 23 '15 at 12:59

2

You can send arrays of data using JSON. Just prepare that array on the Delphi side, send it over to your script and execute the query for every item of your array. You can return an array also, which includes the success messages for every array item you uploaded.

answered Mar 23 '15 at 12:59

Markus Müller

2,611
1
17
25

Thanks for your answer. Can you give me an example or a link of sample code? I'm using Delphi 7 and I don't know how to send array or json text to php server. – RedLEON Mar 23 '15 at 17:46
@RedLEON Use SuperObject, I'm pretty sure it's compatible with Delphi 7. – Jerry Dodge Mar 24 '15 at 03:52

Is there any better way to upload recordset with JSON or something else?

1 Answers1