(Q: Question, A: Answer)
Subject: sqlplus?
Q: Is anyone successful is starting sqlplus, the
oraenv script runs but then
nothing, no new path or env vars or anything. I was just
wondering if I
might not have permissions.
A: Make sure you put the '. ' before the 'oraenv'
when you run the
script. The '. ' tells the shell to run the script in the
environment of the shell.
Q: What’s the alternative to using Dynamic SQL? In
particular how we handle dynamic
:conditionals in the WHERE clause. (something about using: LIKE '%')
A: What I said was that for the various conjunctive
clauses in the books query, you could use the LIKE clause
to avoid using dynamic SQL.
The user can specify Author, Title, Price, etc. or leave
them
blank -- in which case, you want to ignore that condition.
So, what you can do is to put a check in your code: if the
user enters a string for Author, then your SQL should read:
WHERE author
LIKE 'Stephen King' AND ....
If the user leaves author blank, then your SQL should read:
WHERE author
LIKE '%' AND ....
so that Oracle will match all authors.
In Pro*C, your code would read:
WHERE author
LIKE :in_author AND ...
and your C code above it would populate the host variable
'in_author'
with '%' if the user left the author field blank.
For non-string data types (like quantity), if the user
leaves
the value blank, then your C code should replace it with the
maximum or minimum value allowed for that data type -- so
that
it would read:
WHERE qty
<= :max_qty AND qty >= :min_qty
and your code would set min_qty = 0 and max_qty to the
maximum
allowed integer value allowed by Oracle (or the
corresponding data type)
if the user left the values blank.
Q: We lost 10 points because we didn't have a
"document discription" and the
part of "Identifying tasks". (?) What are these
two things and we don't
have any clue what these mean. We have followed the class
notes very
closely and we are unclear on these things. Could you please
give us some
help.
A: The DB methodology distinguishes between the analysis
and specification phases -- the analysis phase is where
you analyze the real-world information that is needed
by the application. That is, you identify all the documents
and tasks that are necessary for the correct operation
of your system.
The specification phase is where you understand the
relationships between the different tasks and documents,
and then represent this understanding using ER-diagrams
and task forms.
That is why in the requirements for phase 1, identifying
the documents and tasks in the analysis phase were given
some points and the task forms in the specification phase
were given some points.
Q: When I do a "Sales by Specific Clerk"
and enter the clerk name or id,
why do i need to print the name/id of the clerk on each line
of output?
Can't I just mention it once at the top and then enlist all
the
sales-qty and revenue?
A: 'All' the sales-qty and revenue? From what you
have written,
it seems to me that this is for a specific clerk. Therefore,
this means that there would only be ONE line in the output.
The reason that the output contains both ID and name is
that if the query was on the clerk's ID then the result
should show the clerk's name. If the query was on the
clerk's
name, then you need to show the clerk's ID.
Q: The document allows multiple books to be put on an
order. When you query
for orders, if we enter book information, will it return ALL
orders that
contain the conjoined info that is provided? Or have we been
misled by the examples to believe one order contains
different books? Does each order focus on one particular
ISBN?
A: "A book may appear on several orders and an
order may
include several books. However, a given order will go to
only one publisher." -- this means that an order can
only
contain books from the same publisher (but it can contain
multiple books).
When you query orders, you might want to have a
column for the Order numbers that match the query. And yes,
all orders that match the query conditions must be returned.
You might want to only show those orders that have not yet
been received...
I will confirm the format of the query result with the
professors (I agree that it is a little inconsistent) and
I will post how the final output should look later on.
Continued:
Now that Phase 1 is over, I wanted to clarify the Query
Order
task a little more.
** When you query for a specific ISBN, you should list out:
1. Book Information:
ISBN
Author
Title
Publisher
2. Orders containing the book:
Order# Price
Qty Name Phone
So, each order that is printed out should only contain the
price
and quantity for the book you are looking for (there may be
other
books in that order, but you don't have to display them).
** When you query for Author/Publisher/Customer Name/Phone
number,
you will be doing the conjunction of all these conditions.
Therefore, every line in the result will contain Orders that
are for that customer (if specified) and that contain books
for that publisher or author. This means that if you don't
enter an author, books by all authors must match. If you
don't give a publisher, orders containing books by any
publisher
will match.
The output must contain:
Order# ISBN
Author Title Publisher Price Qty Name
Phone
The customer Name and Phone might be empty for regular
orders.
Once again, every line will contain only those orders (and
books
& customers) that match all the query conditions.
Hope this helps. If you have any requests for presenting
this
data differently, get it cleared with the TA for your group.
Q: For applications such as "Change a
Book", "Query Book Info",
"Query Order Info", "Place Order", and
"Sell Books" can we
assume that values(strings) *must* be entered in all fields,
or should we handle situations where only a subset of the
fields
could provide correct functionality?
Example: "Query Order Info" - If only ISBN was
entered,
the number of matching items could very likely be much
larger
than the number of matching items for a query specifying
all fields.
I hope that your answer is that all fields must be
specified. The case
of a "Query Order Info",
as an example, might then be split into
"Query Regular Order Info" and "Query Special
Order Info".
What if we wanted to query books by subject alone?
This could easily be remedied in a similar fashion with
additional control menus to add specification.
A: Unfortunately, I don't agree completely -- when
you
change a book, you are most likely correcting only one
attribute of the book. So, you should allow the user to
enter a new value for all the attributes, but if the new
value is blank, the update should default to the original
value.
There is a problem with the Query Order menu and I will
be addressing that problem after Phase 1 (we will make
it much simpler). So don't worry too much about that one
specifically...
Take each task/document individually and evaluate
whether or not all fields are necessary to perform the
task. If a field is not necessary, then make a decision
about whether you want to make a control decision for
it or you want to let the user simply leave that field
blank.
Subject: Eds
Q: In the input field of the Task Forms, what exactly does EDs mean?
A: External Documents
Q: The slides say that we need to use the same UoM
throughout the tasks.
Does this mean that I represent the statistic: Add 4 new
Clerks per year
as: 0.011/day since my UoM is /day??? It seems absurd.
A: You can say < 1/day -- anything less than your
lowest unit
of measurement should be considered to be negligible.
You could consider increasing the number of times clerks
are added :) Think of all the temporary hires that are
done during Christmas...
Q: When modeling the relationship between Book and
Order, is it redundant
to have ISBN, QTY Ordered and Order # as attributes of the
relationship. Such
as:
Book-Order
Order#, ISBN, Qty
From my understanding, there will be a huge list of all the
books ever
ordered. Is there a
better way of doing this? Could you
create a
separate table for each order number which lists all of the
books on
that particular order?
A: It could potentially be a large list -- but that's
why
you want to use a DBMS :)
As far as your idea for creating a separate table for
each order and then listing books for that order: the
downside
is that the number of tables would become so large that
maintaining the table meta-information would be a high
overhead.
On the other hand, the DBMS is designed to handle lots of
tuples in one table rather than few tuples in a large number
of tables.
You are correct that individually there are duplicates of
Order# and ISBN in the relation (though when taken together
there are no duplicates). But to take an example, if an
order
contains 10 books on average and there are around 30 orders
per day -- that is 300 tuples per day or
300 * 365 = ~100000 tuples/year
This isn't much for a DBMS like Oracle. Also, most DBMSs
will
archive old information on a regular basis to reduce storage
costs
Q: Do we have to write actual SQL code for the constraints (like the stuff in the
slides (slide: 51)), or just in psuedocode.
A: Preferably you should write SQL. In Phase 3, you
will need
to write SQL scripts to create your tables. So the
constraints
you define now should be easily taken forward to phase 3.
(Note that defining constraints is optional but recommended for Phase 2)
Q: 1.] The project says "... that satisfy the
conjunction of items
specified above in tabular format"
So do we need to AND all the conditions together or OR them
together
2.] On Slide 52 of DB Methodology, what happens if the first
select
statement returns "NOT FOUND". According to the
code, it will goto
endloop: and at endloop: there is the SQL statement for
closing the
cursor... but since a cursor wasn't opened to begin with,
how can we
close it?
Shouldn't the code me modified somewhat to reflect
"where" the NOT-FOUND
was encountered?
A: Yes, it should be the conjunction of the terms:
author = "someone" AND subject = "some
subject" etc.
The code first declares a cursor for the specified select
statement. Just declaring the cursor will not actually
detect the
NOT FOUND condition.
The first fetch occurs only when the cursor is opened
and the first fetch on that cursor is performed.
When 'endloop' is reached, the FETCH should have
already been executed at least once.
Q: Are we supposed to identify all functional
dependencies including
those that are implied with primary keys? if so, there would be
many of them...IF A is a primary key and B is a field, is
this a valid
dependency that needs listing, or is it implied?
Or, are we supposed to document those dependencies that are
inherent
in our db that can't be changed, i.e. that a ssn
functionally
determines a first name...etc.
A: You should identify all the functional dependencies that
you can conceive of -- including the FDs on the primary
keys.
The reason for this is that -- especially when you
have a composite primary key -- you might find a FD
from an attribute on ONE attribute of the composite
primary key. The FD on singular primary keys might be
trivial, but its also so easy to express:
{PK} -> {A1, A2,
A3, ... }
Frankly, if you follow the ER to relational conversion
algorithm, you should have very few normalization problems
(and very few FDs to worry about!), but we want
you to understand the issues involved in normalization.
Q: Do we have to create a make file for our books r
us database?
Also, I created the table directly at the SQL> prompt in
acme. Is this
the way we are supposed to do this?
A: Makefile is available at: http://www.cc.gatech.edu/classes/AY2000/cs4400_fall/proc.mk
You can create all the tables by just putting all the create
table
commands in a file called tables.sql and then run the whole
file by typing
START tables.sql at the SQL> prompt.
Subject: Other "make" people...
Q: OK, I've used and manipulated make files before,
but the "make"
on AcmeX is being troublesome. I'd prefer to define my own
make file based on the proc.mk in the $ORACLE_HOME
directory,
but Sun's make does not seem to follow dependencies properly.
To simplify to try to try to find the problem, I used the
following (yes, it's ridiculous, but it does well for
test purposes).
#-------------------------------------------
# START
all: ok
echo
"Done."
.SUFFIXES:
.SUFFIXES: .o .c .pc
.c.o:
echo
"Making .o from .c."
cp $(*).c
$(*).o
.pc.c:
echo
"Making .c from .pc."
cp $(*).pc
$(*).c
ok: ok.o
echo
"Making OK."
cp ok.o OK
#END
#------------------------------------
Now, with this Makefile, with a dummy ok.pc file created, if
I
type "make" I get the following message:
make: Fatal error: Don't know how to make target `ok.o'
If I do the same with the same file under Linux it works
just fine.
Yes, I know I could use a ".pc.o" rule to solve
this, but this is
the type of brain-dead behavior make is supposed to allow
you to
avoid, and it can be very useful to have the .c file
available to
track down problems.
Now, does the Sun "make" just do poor
dependency checking, or am I missing a switch
somewhere? I
did a quick sweep of the man pages, but have not found
anything yet.
A: one of the students had a problem running the sample
program
that I have put up on the web page. The program was
dumping core when executing the first SQL statement.
The C compiler turned out to be the problem.
Pro*C programs should only be compiled using the
SUNWspro C compiler. Do not try to use GNU C or /usr/ucb/cc!
You should see the following when you do:
bash$ which
cc
/opt/SUNWspro/bin/cc
If it shows you some other version of cc, then make sure you
do the following every time you login:
bash$ export PATH=/opt/SUNWspro/bin:$PATH
Q: I'm having trouble with the makefile. I get
Fatal error in
reader: proc.mk, line 197: Unexpected end of line seen
I copied it off of the web and got all of he kinks out of it
except this
one. Any ideas?
195 .y.c:
196 $(SLAX)
-p -d -v $*.y
197
198 SQLHOME= etc...
A: Don't copy-paste off the webpage. It adds
"invisible" control characters.
Save the file and transfer it to your acme account. I had the same problem.
Q: Other than have the line "EXEC SQL INCLUDE
SQLCA;" what do I need to do
to use the sqlca feature?
I'm getting:
"sales.c", line 477: undefined struct/union
member: SQLCODE
"sales.c", line 477: warning: left operand of
"." must be struct/union
object
"sales.c", line 646: undefined symbol: sqlca
I have the include line in each function I use.
A: You don't need the include line in each function.
You only
need it at the top of each .pc file.
If you still face a problem, post the snippet of code
at the line numbers in the .c file that are causing a
problem.
Take a look at the sample.pc file to see how sqlca.sqlcode
is
used.
Q1: Has anybody figured out how create domains? Here
is an example of what I tried:
CREATE DOMAIN ISBNNumber CHAR(13);
*
ERROR at line 1:
ORA-00901: invalid CREATE command
A: Oracle does not conform to the SQL standard
completely. It
only conforms to level 1 and some portions of level 2 and 3
of the standard. Basically, it doesn't support domains.
I would recommend taking a good look at the following
documentation available from the link:
http://aistweb.gtri.gatech.edu/oracle/DOC/server/doc/SQL73/toc.htm
1.
http://aistweb.gtri.gatech.edu/oracle/DOC/server/doc/SQL73/ch2.htm#toc015
(the section "Elements of Oracle7 SQL") and
2.
http://aistweb.gtri.gatech.edu/oracle/DOC/server/doc/SQL73/ch4a.htm#toc040
(the section "Commands")
as a more correct reference for the third phase -- the
slides
and textbook refer to the SQL-92 standard and since Oracle
supports some things differently, you shouldn't waste time
with
the wrong information...
Q: What is the main cause of core dumps, and how do i fix them?
A: Usually bugs in accessing memory -- for example,
you have
a character string:
char str[10];
and in your code, you do:
str[100] =
'\0';
This might dump core because the assignment is writing
memory that does not belong to 'str'.
You can use a debugger to locate the problem, but rather
than
learning to use a debugger at this stage, it might be easier
to
put printf statements in your code to locate at which
statement
your program is crashing. Then by printing values of the
data
being referenced by the statement you can figure out which
variable is invalid.
Q: I am not certain I'm doing my create schema
command correctly, because when I
later try to insert into the tables, I am told they don't
exist. Here is what
I'm doing:
CREATE SCHEMA Bookstore AUTHORIZATION publ
CREATE TABLE Bookstore.book
(
...
)
I have several questions. Should there be a ';' after the
last parenthesis?
Should I be using 'publ' as my authorization? I'm not
putting a ';' after my
last ) because when I do I get an error. Help! Yes, I am
looking at the online
documentation, but frankly, it's not that helpful on basic
syntax, which
appears to be the problem here.
A: Try the following:
SQL> CREATE SCHEMA authorization ops$gte916j
2 CREATE TABLE book(isbn char(11))
3 CREATE TABLE publisher(id integer)
4 ;
replacing ops$gte916j with your username (ops$gtxxxxx)
As you can see, you only need a semi-colon at the end
of the schema-creation statement. Apparently you can't
give names to your schemas in Oracle (if anyone knows
a way to do this, please post it).
The advantage of using the CREATE SCHEMA statement is
that all the objects are created in one transaction --
this allows you to define foreign keys into tables that
have not yet been created.
Q2: Thanks for the help on my previous post! I have a
new problem. Here is a
summary of my code:
CREATE SCHEMA AUTHORIZATION ops$gt5262b
CREATE TABLE Book
(ISBN
CHAR(13) NOT NULL,
...
)
CREATE TABLE Author
(ISBN
CHAR(13) NOT NULL,
...
FOREIGN KEY (ISBN)
REFERENCES
Book(ISBN)
);
I am getting this as an error:
----
REFERENCES Book(ISBN),
*
ERROR at line 8:
ORA-00942: table or view does not exist
------
That doesn't make sense to me - I am creating the Book table
immediately
before the Author table. So even if placement mattered (it
isn't
supposed to, when you create all of your tables in the
create schema
command), this reference should work.
A: Does it
say "Table created" for the book table?
If not, then that might be the reason why you can't create
the foreign key
in author. Because i've got very similar code as yours and
it works fine.
Q3: The only feedback I'm getting from my create
schema command is: '11' I
assume this refers to the number of tables I create - which
is 11.
That's all it tells me! What should I get, and why aren't I getting it?
A1: ok .. I am supposing that you are entering the
create schema/ table scripts
from a file using "START <script file>" from
the sqlplus interactive
screen.
If so, then after the file is run, try typing "desc
book" at the
interactive sqlplus screen.
It should give u a description of the books table looking
something like
this:
Name Null? Type
------------------------------- -------- ----
ISBN NOT NULL CHAR(10)
AUTHOR VARCHAR2(30)
TITLE
VARCHAR2(30)
SUBJECT VARCHAR2(30)
PRICE
NUMBER(6,2)
QUANTITY NUMBER(38)
PUBLISHER_NAME NOT NULL VARCHAR2(30)
If it says, "Object does not exist. " that means
that your book table was
not created. and that's why you cannot make the foreign key
isbn in your
author table.
A2: I think your problem is that you haven't defined
ISBN to be a primary key... I can't be sure about this,
but try defining ISBN to be the primary key for Book.
I would recommend first testing your SQL statements
without the CREATE SCHEMA statement (It might introduce
some unexpected errors).
Subject: Functionality Question...
Q1: The problem description document shown on the web
page explicitly states
the requirement of some function of our final product (IE,
Add Book,
Place Order, Reports), but it could be interpreted to imply
others.
For example, I would assume that we would need to be able to
tell
the difference between a special order that has been picked
up and
one that has not.
This implies a new screen. Is such
"added"
functionality required of our finished product, or just
those
functions described in the problem description document?
A: OK -- first point is that the project description
on
the web page is a simple one. It is sort of what a DB
designer
would be given at the time of analysis. It is NOT what your
finished product would definitely look like. So to make
things
work correctly, you should add any intermediate screens
that you need (within reason).
I don't see why you need a separate screen to distinguish
between a regular order and special order. Given an order
number, your schema should let you determine whether an
order is regular or special and based on that your code
should do the appropriate actions.
Q2: I was not saying that two different screens would
be required to receive
special and regular orders; however, while regular orders
are only
received, special orders are received and then picked up by
the customer.
Ideally, the database would show the difference between a
special order
that has ONLY BEEN RECEIVED, and a special order that has
been
RECEIVED AND PICKED UP by the customer. Since it makes sense to
increase the inventory apporpriately when receiving an
order, a seperate
action would make sense to decrement it at the time of the
pick up. While
we could only increase inventory for regular orders, this
solution would
still leaves us unable to tell the difference between a
special order
that has been picked up and one that has not. This seems distasteful,
it leaves the store vulnerable to an unscrupulous customer
attempting
multiple pick-ups.
If you can't tell the difference between a special
order that has been picked up and one that has not, you have
little
basis on which to argue the attempted second pick-up, or to even notice it.
A: I see your point. Feel free to add cool features if you think they are justified
-- just make sure not to miss out on the important (and more
basic) tasks already in
the requirements.
Subject: Order #s:
Q: How do i go about generating sequence numbers? For
eg, while placing an
order, a DBMS generated order number is returned which is
supposed to be
my Primary Key that I use as reference keys in other tables.
How do i
create this sequence number and once created how do i use it
as a
foreign key in other tables?
A: Say you create a sequence
The format is
create sequence <seq-name>
[increment by
integer]
[start with
integer]
[maxvalue
integer]
[cycle/nocycle]
For example:
create sequence custseq start with 1000;
you would
insert into customers
values
(custseq.nextval, 'Jones', '123 main', . . . . );
When createing the table you should declare it as type int
or number(x).
A2: (Following A1) That is correct -- you create the
sequence just like any
other database object in your .sql file. You can then
use nextval to get the next unique sequence number and
currval to get the current sequence number.
A3: If I understand your question, you want to know
how to use the same key
values in two tables.
Remember, you don't always have to use nextval;
there is always currval.
In other words use sequence.nextval for the
first table, and sequence.currval for the next table. If you really
need to get at the value of the sequence for debugging
purposes, you can
use
SELECT
sequence.currval
INTO
:variable
FROM
DUAL;
DUAL is a dummy table you can use to get to things like
sequence numbers
and SYSDATE that are not normally easily accessible for
assignment in C.
Q2: Ok.. so now I've created the sequence number. I
just realised that I somehow
I managed to increment the sequence number accidently. How
would I reset the
value of the sequence number to, say, 0?
A: Just drop the sequence and recreate it. I don't
think you
are allowed to reset the sequence numbers (for a very good
reason).
Q: I'm trying to create my tables. I have the drop statements at the top
of the file. They
were working fine and then I started to get these
errors (multiple times) pertaining to the first table I got
to work. Am
I making a mistake in dropping the table?
DROP TABLE PUBLISHER_INFO
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by
enabled foreign
keys
CREATE TABLE PUBLISHER_INFO
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
A1: try to drop table publisher _info cascade;
that'll get rid of all constraints that apply to that table.
A2: The syntax is:
DROP TABLE
publisher_info CASCADE CONSTRAINTS;
(refer to the Oracle documentation for syntax problems...)
A simple solution is to drop the tables that reference
publisher_info
before dropping publisher_info. So for example, if the table
'book'
references publisher_info, drop book first and then
publisher_info.
This works in most cases -- it will only fail if two tables
reference each other (circular references)
Subject: Cursor Problem...
Q: OK, I've created a cursor in ProC, but it does not
retreive the rows
it should. This
cursor is used for Query Order Information.
As a
test, I copied the SQL select statement out of the code and
into
a sqlplus session. I
then replaced any variables from the C with
their values for a given problem case (obtained by print
statements
run immediately before opening the cursor). While my compiled function
finds no rows, sqlplus finds the one I expect it to. Any suggestions?
A: Is sqlca.sqlcode indicating the 'no rows found'
error code? Or do
you have the WHENEVER NOT FOUND clause correctly specified?
If it is, then check the data types of the variables in
your where clause. If any one of them is a CHAR, then you
need to pad it with spaces. If any of them is a VARCHAR,
you need to set the "len" variable to the length
of the
value.
Also check the case of the data that you are querying:
Oracle
is case-sensitive.
(Post a snippet of the code if you are still facing a problem.)
Q: Any idea why I would get this error while
inserting the following data?
insert into book
values("0201357534","Oracle
Programming","CS","Sunderraman","Rajshekhar",35.00,50,10,1);
SQL> start initbook.sql
values("0201357534","Oracle
Programming","CS","Sunderraman","Rajshekhar",35.00,5
0,10,1)
*
ERROR at line 2:
ORA-00984: column not allowed here
A: Do you have "Oracle" and
"Programming" on different lines?
Try putting them on the same line.
Q: In the reports, if a book has not been sold but
carried in the store,
does it need to be in the books report? Likewise for a publisher, if
a book has not been sold from a specific publisher, does it
need to be
in the report.
A: Yes, even books that have not been sold should
show up in the
sales report.
Q: Has anybody figured out the syntax for JOINs on
Oracle?
I'm trying
SELECT stuff
FROM (Table1 NATURAL JOIN Table2) NATURAL JOIN Table3
and it's dying.
A: Some people have asked me about using Left outer
joins
on Oracle since the JOIN keywords in the notes don't work,
From the Oracle documentations:
"
Outer Joins
The outer join extends the result of a simple join. An outer
join returns all rows that satisfy the join condition and
those
rows from one table for which no rows from the other satisfy
the join condition. Such rows are not returned by a simple
join.
To write a query that performs an outer join of tables A and
B
and returns all rows from A, apply the outer join operator
(+) to
all columns of B in the join condition. For all rows in A
that
have no matching rows in B, Oracle7 returns NULL
for any select list expressions containing columns of B.
This is the basic syntax of an outer join of two tables:
....
"
The rest of it includes images so I am not showing it here.
But
take a look at the documentation for the SELECT statement
and
the sub-section on "Outer Joins".
Q: For the queries, how can we AND together only what
the user enters and
not the options that they didn't enter any data for?
We are thinking about using the LIKE clause, but how do we
search for
price > and price LIKE?
A1: Basically, if the user doesn't enter anything for
the prices (or qty),
assign them the max values. For example, if the user enters
nothing for
PriceGreaterThan, then make it PriceGreaterThan=0. If they
enter nothing
for PriceLessThan, then make it PriceLessThan=9999999999.99.
Or
something of that nature.
However, I have not determined how to see if the user enters
nothing.
If you use scanf to get user input, the users have to enter
something.
If you use fgets to get user input, the value input is assigned
as a
string. I don't know how to change a string to a float. (You
can use
stoi to convert a string to an int). Any thoughts?
A2: You can use sscanf to scan the float from the
string. Its
almost equivalent to scanf, but it lets you handle the
spaces in user input. You should do the gets, check that
the string contains some data and then try to sscanf it
into the float/integer variable.
Use man to find out more about sscanf.
Q: what's a good way to convert from signed char to
unsigned char? i ask
because the varchar field that holds the string is
unsigned. i can get
around it, but i get a warning for doing it my way.
gets((unsigned char)title.arr); gives warning for removing sign bit.
A: You should use (unsigned char *) and not (unsigned
char).
If it still gives you a warning ignore it, but it
shouldn't...
Q: When I am querying for a book, I am searching for
many different column
values. As stated in a previous post, if the user doesn't
enter any data for a
specific column, then that column is replaced with a %. For
example, if I ask
the user what is the Middle Name of the author that wrote
the book the user is
querying for, and the user just hits return, then the middle
name is replaced
with a % in the select value. ie, select * from book where
middlename='%'.
Here is my problem. This method works for every field which
is NOT null.
However, if middle name is null for one of my books, then
the search will
never find that book. % does not match null. How do I do
this search?
I tried having a space as my default value for middle name,
but that didn't
work. % does not match a space. Even if it did work, it is a
horrible hack
solution that I don't want to use.
Any ideas?
A1: I think
the easiest way is to modify your cursor to grab any row that
matches your 'LIKE' condition for the middle name and OR
that clause
(in parenthesis) with a clause selecting middle name if it
is null.
I would then add logic in you code to pitch rows with a null
middle
name if a middle name was specified. Be sure to use an indicator
variable to determine whether or not the middle name column
was null.
By the way, your space hack should work; try the following
query in
sqlplus:
select count(*) from dual where ' ' like '%';
I get back one row (as I should); if the like did not work,
I would
get back 0.
If you are like me, you added code to trim the white space
off the
beginning and end of user entries before using them with the
database
to make sure a user-entered ' DOG ' matches a user entered 'DOG'.
This code, if present, may be reducing your space to the
null string.
Last, I would not feel too bad about the space hack. A similar hack
is just now being phased out of a production Oracle product.
A2: Why not just insert an empty string rather than a
NULL
(middlename.len = 0). That should work. My suggestion is not
to stress out on solving this problem because the
specification
made the query more complicated than was originally
intended.
Secondly, in your posting you have said "where
middlename='%'"
which should be "where middlename like '%'". If
thats just
a type, then no big deal, but otherwise, that could be your
problem.
Q: Anybody out there on this Sunday afternoon? I'm getting stuck trying to
create my tables.
The script runs fine up till the 10th thru 13th table,
then at some point I get the error:
ORA-01536: space
quota exceeded for tablespace 'USERS'
I'm only creating 15 tables and they aren't what I imagine
to be large from
a RDMS standpoint.
Anyone know if there is some limit we need to adhere to
and what that may be?
On a related note, I'm wondering if in my trials I created
some test tables
that are still hanging around. Is there a command to view all objects in my
tablespace?
A1: to check all the tables owned by you try sthg
like:
select * from all_tables where owner='OPS$GTXXXX';
That should give you all ur tables and show you which ones
to delete.
You might want to check what that error code stands for
(from the oracle doc
pages -- the solutions are also listed there)
A2: I think we're all using the 'USERS' tablespace
(the default); I know I am.
It may be that there is not enough free space for all of us
to have our
tables out there at once, and there are currently enough
tables out there
to prevent you from adding more. You might want to keep trying until you
snag the space--of course this will come at someone else's
expense. In the
meantime, it'd be nice if some who are done would take down
their tables
so others can use the space. The faculty could also look into securing more
space for the 'USERS' tablespace, so this is no longer a
problem.
A3: You must have some old tables hanging around.
Do:
SELECT table_name FROM user_tables;
to get all the tables in your name.
A4: or you can do
select table_name from user_tables;
Q: Can anyone tell me why I'm getting this error?
SQL> desc PUBLISHER
Name Null? Type
------------------------------- -------- ----
PUBLISHER# NOT NULL NUMBER(38)
COMPANY_NAME NOT NULL VARCHAR2(20)
PHONE NOT NULL
VARCHAR2(15)
ADDRESS VARCHAR2(20)
CITY
VARCHAR2(15)
STATE CHAR(2)
ZIP_CODE NUMBER(38)
SQL> INSERT INTO PUBLISHER
2 VALUES (1, "Al's Books",
"8006661234", "1 Main St", "Peabody",
"NH", 33333)
;
VALUES (1, "Al's Books", "8006661234",
"1 Main St", "Peabody", "NH",
33333)
*
ERROR at line 2:
ORA-00984: column not allowed here
A1: I don't know if you did a typo, but you are
supposed to use 'single
quotes' and not "double quotes" to delimit
strings. That might be your
problem.
Q: the only way to permanently change the state of my
database is from the sql
prompt. when i'm using my program with embedded sql,
changes take effect
for as long as the
program is running, but then when the program exits, the
database reverts to
it's original state before i ran the program.
for
instance i run my
program and add a book. the book is in
the database as
long as the program
is running, but once i exit, it's gone because i didn't
add the book from the
sql prompt.
is that ok? it's good to have it that way for debugging
purposes, but i
don't want to loose
points if we are supposed to have it set so our program
can make permament
changes.
A: The changes you make in your program are supposed
to
be permanent. (That is the whole point of a database,
right!?)
You probably are not doing EXEC SQL COMMIT WORK at the
end of your functions.
Q: (Missing)
A: scanf() will only read up to the first space in
your string, which i'm
guessing is your problem.
if you use scanf() to read and type in a multiple
word string, it will read in up to the first space, and the
next scanf()
call will read up to the second space and so on... use gets() to read a
string with spaces in it.
Q2: Okay, now I'm using fgets (safer than gets). To
get an int, I use fgets
than stoi. How do I get a float? I'm using fgets even for numbers because I want to
terminate if someone doesn't enter a value and just hits
enter. scanf requires the
user enter something.
A: you have to fflush(stdin) in order for gets() to
work if you have used
scanf() before it, or else at your call to gets(), nothing will happen.