Can I view and edit Table ( design/sql code ) as sql like Query in LibreOffice Base? i found while i was searching " Execute SQL statement " but it is not what i am searching for.
Edit in SQl view option /query section
Hello,
Basic answer is yes. In most cases you enter this from menu on main Base screen Tools->SQL....
What and how to use is completely dependent upon the database you are using. Refer to the appropriate documentation for your database.
Edit 2019-11-06:
If using SQL to create/modify tables use Tools->SQL....
If you want to view the result with a Select use the Query section. You can store the original SQL in the Query by surrounding it with comment indicators. /* and */ will set a block as a comment. // or -- are to comment a single line. Example:
I think this gives you what you want.
hello , in tools>sql for now all i found an sql command which only shows the content or structure of the Table like for example " ,PUBLIC,emp,ID,4,INTEGER,4,10,0,4,1,NO,1,1,) not like viewing (forget the editing part) option in Queries which displays the sql code. so it is possible if i entered the right sql command ? ( i am just creating basic simple databases for now)
Hello,
" ,PUBLIC,emp,ID,4,INTEGER,,4,,10,0,,,4,,,1,NO,,,,,1,1,)
This by itself makes no sense. You have not stated what database you are using, exactly what you expect to display or the actual SQL you have used. Also not certain why you are not using the Table Wizards to create & modify tables.
1- HSQLDB database
2- I am creating Tables Queries etc manually and by the wizard but i just want to view the SQL code of Tables after design for learning purpose also copying it after design to somewhere else for other reasons etc.
3- The output i typed is part of the output of this command SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME = 'tablename' ORDER BY ORDINAL_POSITION.
Please see edit in answer.
I don’t know of a command in HSQLDB embedded to generate the SQL used to create the table. This can be obtained with many other databases and external tools.
@Ratslinger Sorry for the delay This is a comment on your Edit: 1- when you typed " If using SQL to create/modify tables use Tools->SQL" so you mean i can create Tables using/by Tools>sql ? i tried that but i got error Unexpected token: “ in statement [CREATE TABLE ] but after seeing your new comment you are saying you do not know a command and i was intend to ask you about valid command example to test it.
when you typed " If you want to view the result with a Select use the Query section" are you saying i can view it using the select statement i tried that using the statement i typed before and i got this error syntax error in sql statement
I don’t believe you understand what is stated. Yes you can create/modify tables with Tools->SQL. The Create statement in my answer is what I actually used there. This will work for HSQLDB embedded:
CREATE TABLE "phonebook" (
"id_pn" INTEGER generated by default as identity primary key,
"phone_number" VARCHAR(16),
"name" VARCHAR(128),
"date" DATE,
"note" VARCHAR(256),
"flag" VARCHAR(2)
);
Would need to see your complete statement as to why you got the error. Syntax error is most likely.
As for the Select statement, it simply presents a better method to view the current table structure. You can see by my example it is nearly identical to yours (table name is different) and works without a problem. I just copied your statement and changed the table name.
The comment about not knowing a command was a command to generate SQL directly from the database. This, as stated, can be done with other databases.
First for the part of creating Table using Tools>sql i just tested that it worked it might was a mistake at the code i used beside even when i changed it i messed the part of refreshing tables from view menu to see it. so this point closed thanks. plus for the Syntax error i guess it was a mistake at table name i entered when i was using The select statement in query section.
For the Main part : viewing Table created by wizard or design view as SQL using SQL command in libreOffice Base that can be done with other databases you mean types or other tools or softwares.
I am thinking at making final answer To this question which : This is a missing feature and not exist in LibreOffice Base for now and you can not just view The Table as sql. what do you think? because i think it is just a missing feature and can be added by adding the view Table as SQL like query option.
I don’t believe you can state this is a missing feature as this is based upon the database and third party tools available. You need to understand that this HSQLDB embedded is VERY old (v1.8) and the attempt is to replace with Firebird embedded. There are numerous other databases available and I personally use PostgreSQL server for my regular databases.
If this answers your question please tick the 
(upper left area of answer). It helps others to know there was an accepted answer.
I Know i am a beginner, but This option or feature seems direct and it is about the software more Than the databse tybe (which it does not matter) i will explain For example when you create “HSQLDB database” and you use wizard or design view to create a table The program/computer coding for you so the option " view as sql " is just displaying the code used to create the table which this option already exist in query section … so for now i just can’t named it something else it is a missing feature it is even direct and simple to make you think that the software developers just forgot to add it.
If you want, you can file a request here → Bugzilla. Personally think it is a waste of time. You would be asking for functionality for one database. Base is just a front end to databases and can connect to a variety of DB’s. Should this then be implemented for each and every database Base can connect to? As stated this version is very old and, in my opinion, is only good for answering questions here. For portability I use the Firebird embedded DB and it is more up-to-date with standards and the direction LO has chosen.
You can stick with HSQLDB and upgrade by splitting the DB. See → [Wizard] Create a new ‘split’ HSQL 2.x database.
For certain, almost any other DB is better than this HSQLDB embedded (v1.8 in Base).
First I do not want to stick with HSQLDB as i mentioned i am beginner so i am just at discovering level -basic,Thanks for mentioning that i just installed Libreoffice-sdbc-firebird.
but I am now thinking to file a request, we still stuck at same point lets say you created Firebird database which i already did and i want to view the table as the code it is written in like query what should i do ? it is the same circle and even prove that is is just a simple direct missing feature. it is all about the software,so for now i think it is just the suitable answer for this question… because who is searching for this option and reading this now what is the result for him and how to he view the Table?
It depends on the expectations that you have of such a tool. Currently, a very limited UI is available via Tools > SQL, but this won’t do pretty formatting or DDL function highlighting.
As you have found out for yourself, the SQL statement execution tool, which does provide code highlighting (but not formatting), only allows you to execute DML statements and not DDL statements. My personal opinion would be that Lo doesn’t provide an adequate UI tool suitable for data definition language statements.
The expectations is just to view the sql code starting with ( create table etc ) Data Definition Language (DDL) , so you are saying There is no command to view it using Tools>sql ? and i can’t see for now any option in Base like view in sql to view it so it just can’t be done.
Unfortunately you can not View Table in sql view like Query direct and simple option, i think it is a missing feature unless someone proves it is not.
This might not the ideal answer, but This the answer So far I am convinced that it is the answer to my question…
But however you can read the older answers and the comments on it which i personally learned something new
even a close thing like creating Table using Tools>sql and others. Thanks @ratslinger @iplaw67
I might update This if i reached something new or anyway if someone replied with the opposite / commented etc. also closing it if nothing new was added or it became dead.