Import / Export PIPE delimited text <> SQLite3 64Bit Database
Hi,
I am currently editing text to add into a greek-english pair SQLite3 DB.
Database ENTRY is laborious in my Unicode DB Dual Editor app.
OR if I have to go back and edit the base text file because of spelling mistakes etc that too can be time consuming with
subsequent DATA CONVERSIONS plural
The pipe delimited text file from Textpad8 then goes into MS ACCESS 2019 >>> DB5.mdb
then it does into >>> Database NET Pro 5 - Data MIGRATION NET 64 Bit TOOL>>> Uni_Dict.db3 SQLite3 database.
So, if I want to start adding entries for Ancient Greek to my 32 000 Greek-English Pairs database I have to first make the entries in a TEXT file delimited by pipes '|' or, do slow data entry into my Unicode DB Dual Editor app which also has a Database Manager for new records.
It would be quicker to import/export PIPE-delimited text into/from the database for editing and additions.

The only software I can see either does not support PIPE '|' delimiting or only supports other NON-SQLite db software.

YES, I AM TALKING ABOUT EXPORTING / IMPORTING entire DB.
Any help is appreciated
I have Delphi 11.1 , Win 10 Pro 64bit
 

You are the second Lajos I have ever met, over the internet in about 3 weeks too.
Thanks, he is a computer programmer too.

Hello,
It has been a long time since I worked a lot with Delphi IDE.
I seem to recall in Delphi 5 there was a way to TOGGLE MAINFORM SIZE when at designtime in the IDE to make the MAINFORM larger.
The small area in the IDE workspace does not let me conceptualise/plan properly RUN-TIME size of my Project/APP.
How do I make my project appear the same size at design-time as run-time, BUT MUCH BIGGER and toggle to access IDE CONTROLS ?
Any help is appreciated
 

Well,
Some good news.

I had some help and now I have two projects up and running in 64-bit on Win-10 (64-bit) computer.
1) SQLite3
which was pretty simple to set up.
and,
2) My Dictionary Firebird3 project is now running with DATA populating the BDGrid.

I have open and close BitBtns for the FDConnection and FDQuery.

Another useful thing/s I discovered
A) in the Project file make sure the Datamodule statement is before the form statement
B) When fiddling around with ODBC you need to make TWO Firebird3 USER DSN's with similar names, just have one end with 32 and the other end with 64 or something to tell them apart. This means you have to make a 32-Bit Firebird User DSN in the 32-Bit ODBC editor ( type ODBC in the Windows RUN window and it should appear). Then, do the same in the 64-Bit ODBC Editor for a Firebird ODBC Driver.
So now , when 32-bit FireDAC calls the 32-bit ODBC Editor for FDConnection, BOTH drivers, 32-bit and 64-bit Firebird drivers can be seen and accessed.
Got this from Devart website.

 

THANKS Roger and Stano,
I do agree there are some nightmares for 64bit users mainly DB users.
THE ODBC for 64bit DB cannot be accessed from FIREDAC in Delphi 11.1 ONLY the 32 bit version is called.
So, giving ODBC as an option for 64  bit platform is like dangling a carrot in front of a donkey, sorry, no wish to offend.
It would be nice if Embarcadero gave the programmer a choice of 32 bit FIREDAC or 64 bit FIREDAC.


All iPhones, androids, iPads, iWatches use 64bit. So, to answer your question STAN, the envoronment for FMX 64 bit needs FireDAC 64 bit, but, I think you already knew that.
Patience is a virtue. If I am given any powers in the future to help make these sorts of corrections then rest assured 64Bit FireDAC will at the TOP OF THE LIST.

Hi again,
Serge_G I tried those settings in the FireDAC Connections settings editor and it does not work for my 64 Bit VCL APP.
Error
[FireDAC][Phys][FB]l/O error during "CreateFile (open)" operation for file "FB3_TRANSLIT.FDB" Error while trying to open file
The system cannot find the file specified..
I am guessing your APP is 32 Bit.. Am I right ?

Thanks Serge_G
Posting in French creates more problems for me. French Delphi vs English Delphi Software mismatch.
Thanks for your kind help though.
I notice when looking at the Params for FDConnection that while my project is 64 Bit the FireDAC platform is 32 Bit ( Strange that the Embarcadero people did not fix this in Delphi11.1 ).
This kind of mismatch alone is a nightmare IMHO.
Maybe it means my Version of Delphi 11.1 is a DUDD when it comes to Firebird 64 Bit. ???
As for some of the other params. I will need english equivalents eg.
what is english version of :-

UserName:=parametres.utilisateur; ? params.user ?
Password:=parametres.motdepasse ? params.password ?
Server:=IP; // Ip addresse I know that one
Database:=Parametres.nombase; ? params.databasename ?
I could guess but as a newbie I would need exact param names .....
What are the params for ? FDConnection, TFDPhysFBDriverLink1

 where does this code go >>> ? as an event ?, as a procedure ?
 
 is this a good replica of what you are asking me to do in an English version of Delphi 11.1 ?
....
  with DataModule1.FDac_Con1.Params as TFDPhysFBDriverLink1
      begin
      Database=FB3_TRANSLIT.FDB
      User_Name=SYSDBA
      Password=masterkey
      Server:=IP; // IP address
      CharacterSet=UTF8
      DriverID=FB
      SQLDialect:=3;
    end;
      try
       Datamodule1.FDac_Con1.Connected:=True;
      result:=true;
    except
    result:=false;
  end;

###############################################
Forgive my ignorance but I do not know what "value of parameter" means ?
nor do I know the significance of  "xxx"  ?

It sounds simple, but, I have never come across it before.

FDQUery1.Close;
// no need to change SQL Text every time
FDquery1.ParamByName('Index2').asInteger:=xxx  // value of parameter
fdquery1.Open;

I found this today which looks helpful

https://soonsantos.medium.com/connect-fdquery-to-dbgrid-programatically-delphi-4aab394c6d51

Hello,
Can somebody please tell me
1) How to open Firebird 3 database in FireDAC Delphi 11 (eg Database.name "FB3TRANSLT.FDB" >>table.name "Dictionary" ?

2) what should the FDQuery.Strings property look like? Select*From "Dictionary" does not work apparently like it did for my Paradox 7project.

cheers
thanks in advance

if I put this in FDQuery.SQL.Strings

SELECT
  "Index2",
  "French",
  "FrenchUTF",
  "FrenchMod",
  "English",
  "Latin",
  "Greek",
  "Spanish",
  "German",
  "Chinese",
  "Arabic",
  "Russian",
  "Italian",
  "Pasigraphie",
  "PasiImage",
  "Notes"
 FROM "dictionary"
 WHERE "Index2" = :INDEX2

I found a TIP
Datamodule must be formed before MainForm
goto Project>>>view Source

 Application.CreateForm(TDataModule1, DataModule1); {DM1 created FIRST}
  Application.CreateForm(TForm1, Form1);   {MainForm created after DM1}

Can someone help implement the correct changes and tell me where to add the changes ....
kind regards
in advance

When is someone going to make a UNIVERSAL FIREBIRD DB SERVER ? and is Platform independent.
A DB Server that takes a moment/s to create a NEW TARGET DB CLONE FOR SERVICE from the SOURCE database... whatever or however old it is... that can determine the software platform being used eg, linux, OSX, winXP, win95, win98, iPhone, Android, iPad, Tablet etc  
A Universal DB SERVER that can automatically convert old ANSI DB into UNICODE. With the help of AI
old programs can be made to run again. Like a museum of time  and computer evolution.
Creation of a Universal Language SysTem - based on Greek is where we are headed if my intuition is right.

It would be like creating one Automobile Engine Analyzer that can fit diesel, gas, petrol, water-resonance, hydrogen, electric, hybrid etc
Sorry, but Stanley Meyer and Daniel Dingel did create water-resonance fuel sources that will re-emerge. When Fossil fuels die out alternatives shall emerge.

But yes, thinking about something novel like a Universal Database Server, such as a Universal Firebird DB Server, pre-empts its DEBUT.
In a world without corruption these Databases should be safeguarded and virtual databases should have greater access by the Public.
Then there will be more time...To Know Thy Self.

On 11/10/2022 at 7:02 PM, tgbs said:

For Interbase we use TIBServerProperties from IBX package.

     List.Add (IBSrvProp.licenseInfo.LicensedUsers.ToString);
     List.Add (IBSrvProp.DatabaseInfo.NoOfAttachments.ToString);
     List.Add (IBSrvProp.VersionInfo.ServerImplementation);
     List.Add (IBSrvProp.VersionInfo.ServiceVersion.ToString);
     List.Add (IBSrvProp.ConfigParams.SecurityDatabaseLocation);
 

 

Can you get 32bit/ 64bit calls  as well as the version of Firebird database being used eg 1.5, 2,3,4 etc and compatibility with current server ? eg Firebird 3 or Firebird 4.
It's a shame Firebird server does not come  with its own Datapump to streamline all these incompatibility issues.

15 hours ago, Stano said:

Good. So, for the last time 

• If you send me your DB FB v.4 and csv file so:
• I'll get it into the DB
• you can build a new unicode application
• when it's working, I'll make you a new import

From this perspective it is a one time action. I would rather spend that time on application development 
Let me know.

Note: I own a very high quality DB manager DB Workbench v. 6. I appreciate it very much.

Thanks for the KIND OFFER Stan.
But, I am  not about speed, learning and teaching is where I am at, at the moment.
Thanks
B

On 8/11/2023 at 4:42 PM, Serge_G said:

No, no demo project, it's very easy to reproduce with only the picture as guideline.  The connections and query parameters are too depending of your needs

Database projects in Unicode have never been easy for me since Delphi 5 ANSI. that's about 25 years ago..
think of me as a time traveller.... with wrinkles.

1 hour ago, Stano said:

What did you create the DB in? It doesn't have an import?
Interesting that you can't answer 2 simple questions  I wanted to save you the trouble 

PARADOX 7
DOES NOT WORK IN WIN 10
I LIKE TO LEARN AND SHARE
MAKING A CUSTOMISED DATAPUMP USING FDBatchmove seems like my best solution.

I also found FlameRobin server with GUI for FireBird databases upto version 4 I think
scroll to bottom of page ASSETs for win32 and win64 downloads

https://github.com/mariuz/flamerobin/releases/tag/0.9.7

found this Java DB csv  import DATAPUMP as well

https://sourceforge.net/projects/dbcsvimport/

7 hours ago, Stano said:

1. What about csv?
2. So a one-off action?

I still use my old ANSI - DB-TEXT EDITOR IN DELPHI-5 ( NOT COMPILEABLE ON DELPHI 11) FOR ADDING FRENCH-ENGLISH PAIRS.
SO, UNTIL I GET THE UNICODE VERSION WORKING IT IS NOT A ONE-OFF

7 minutes ago, Stano said:

I didn't know about that. Just out of curiosity two things. If you know. Does have it:

1. Visual Query Builder
2. Import data

I saw a datapump feature. I could not get it to work with my database (Firebird) maybe my version is corrupt or too early ? I originally used MigrationNET to convert .mdb to .fdb so that might explain why my .fdb file version does not work.

FireBird DATABASE Administration TOOL

My Initial Project will contain one Table - Dictionary
My Final Project will contain two Tables - Dictionary; Words. (words will be populated by a DELPHI COMPONENT,
it's a TEXT SEARCH ENGINE COMPONENT called HREF Rubicon v4.
I have used the Rubicon2 in my old ANSI text DB project with Delphi-5 and JWrite ( specially customised for my project) text window component. It's a DUAL Text Editor for Translators. eg French >>> English
JWrite has not moved to Unicode Version. So, I am hoping TRichView [which is Unicode] Text window Delphi Component will be an adequate replacement for JWrite, for the task.

I found this video to get started with FDBatchMove:-
Question: If this fellow uses MS SQL SERVER in his demo, Does that mean I need to obtain a copy of MS SQL SERVER ?
I was hoping to do things on the cheap.
I was trying to replicate everything he did on my system,... looks like that,  MSSQL, is not going to work on my system.
Maybe I will have to try again with FireBird 3.
Are there any free GUI's for Firebird 3 ?

Import Data From Text To Database and Vice Versa by FireDAC Delphi English

https://www.youtube.com/watch?v=4qdmECIsOUM

Hi,
I have a Paradox 7 Database table with about 82 000 entries.
I can open the Paradox Table in Delphi 11 project and I would like to transfer data from Paradox to Firebird.

I still use the old Paradox 7 - Delphi 5 program and hence the need to update the Firebird database.
Until I make the final jump to Firebird for good and finish making a UNICODE version I will need to be able to do DATA TRANSFER.

I wish to empty ( with a button click) my target Firebird table either Firebird 3 or Firebird 4
and then I wish to transfer /batchmove 82000 entries from source Paradox 7 database  table to target Firebird database,
any pointers ?

This might seem like a silly question but can you run Firebird 3 (32 bit ) and Firebird 4 ( 32 bit) at the same time
on windows 10 ?

On 11/22/2021 at 10:19 PM, Serge_G said:

As other alternatives

Step by Step

 - using fdbatchmove  (during design time that's work, using fdbatchmove context menu)

 - extracting data in SQL insert statements 

 

 

Have you got a DEMO PROJECT for this to see the code / properties data?
 

Hello again,
I changed Mode=Read; to  Mode=ReadWrite; ( in the ODBC ADVANCED property of FD Connection Object)

Then I recompiled and added a new entry French field "Eureka" and saved and refreshed it.
I clicked last entry and waited a while... then I clicked add new entry "+" button on the DBNavigator bar.
Voir-La it accepts new entries/rows.
So, I noticed AUTOINC is working OK.

Hello,
It is Thursday 10th August 2023.
I finally got the Paradox 7 database ( ~ 120 Mb) to open in a DBGrid in Delphi 11.
HIP HIP HOORAY.
1) I used the Embarcadero BDE installation. Control Panel then search "BDE" for BDE Admin
When using BDE Admin "USE AS ADMINISTRATOR"
2) Navigate Configuration>Drivers>Native>Paradox and  then goto Net Dir and Change it to where
you keep your Paradox 7 Database Table files eg .db/.md/.px
Then click "APPLY EDITS"
close and re-open BDE Admin to verify change.
3) Open ODBC Data Sources (32 bit version for 32 bit APP)
If you already have a Paradox DSN edit it by clicking "Configure"
Change folder directory to where you store your Paradox 7 database table eg .db
4) Once the above is done open "CONNECTION POOLING" TAB
and change mdb to pooled
and change both Paradox .db to pooled.
I only have the last one as I deleted the first one (.db driver) from REGISTRY. ( to avoid overlap)
5) I also changed permissions for my BDE folder to read-write
then I added string in ODBC ADVANCED in my Connection object on the Datamodule
to something like... no spaces or linebreaks

Provider=MSDASQL;Persist Security Info=False;Mode=Read;Extended Properties='DSN=Paradox;DBQ=C:\Delphi_DB_Projects\Paradox_7_ODBC_DICTIONARY\tables;DefaultDir=C:\Delphi_DB_Projects\Paradox_7_ODBC_DICTIONARY\tables;DriverId=538;FIL=Paradox 7.X;MaxBufferSize=2048;PageTimeout=600;';Initial Catalog=C:\Delphi_DB_Projects\Paradox_5_ODBC_DICT5\tables;

6) I would like to mention that I copied a PDOXUSRS.NET (13kB) to the folder containing the Paradox 7 database table.
then I compiled [F9] with debugging after
going to connection object and turning off by unchecking "CONNECTED" and
query object property "Active" to FALSE

VOIR-LA
it works
Mission Impossible,.. well at least it felt like it
Enjoy

I did find this article.. that claimed some success, but, I am doubtful it will work on my system as MY SYSTEM seems to baulk and stutter whenever I add MS Access Drivers... I expect that once I create a UNICODE Firebird or .mdb version TO REPLACE  my old ANSI DB text editor THEN I CAN PUT THIS ISSUE TO REST TOO.

https://stackoverflow.com/questions/49629776/how-to-read-and-write-to-a-paradox-7-x-database-from-net-app

AND THIS WAS LINKED TO IT ALSO....

https://social.technet.microsoft.com/Forums/lync/en-US/b31f8ed5-7f53-4d1d-901f-93f1dd5e13ae/windows-7-error-unexpected-error-from-external-database-driver-11265?forum=officesetupdeploylegacy

For completeness. This project only runs on Paradox 5 structure tables and not Paradox 7 which is where I am headed.
Does anyone know a fix for this ?
Do we need a odbcjt32.dll for Paradox 7 ? If so where can one find it ? Or is there another work around ?
Does this file (odbcjt32.dll) need to be in c:\windows\system32 or c:\windows\WinSxS ?