- Getting Started
- Creating the Database
- Building the Tables
- Deploying Via Ultralite
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Building the Tables
The simple sales force application we build will enable a user to access information on all products sold by our company as well as all customers of our company. The user can create/modify new sales orders and when all is complete, can sync modified data back to our organization's central server RDBMS. Our "starter" database schema is very simple (four tables), with the goal being to highlight SQL Anywhere Studio capabilities and mobile development techniques.
To complete the goals of the application, we need four tables in order to store sales and product information: CUSTOMER, PRODUCTS, OPPORTUNITIES, and SALES. Figures 8 through 11 show the formats used in these tables.
)
Figure 8
Creating the CUSTOMER table.
)
Figure 9
Creating the PRODUCTS table.
)
Figure 10
Creating the OPPORTUNITIES table.
)
Figure 11
Creating the SALES table.
The ASA SQL statements used to create these tables is as follows:
CREATE TABLE "DBA"."CUSTOMER"
(
"ID" integer NOT NULL,
"FIRST_NAME" varchar(50) NULL,
"LAST_NAME" varchar(50) NOT NULL,
"STREET_ADDRESS_NUMBER" integer NULL,
"STREET_NAME" varchar(50) NULL,
"CITY" varchar(50) NULL,
"STATE" varchar(2) NULL,
"ZIP" integer NULL,
"REMARKS" text NULL,
PRIMARY KEY ("ID")
)
CREATE TABLE "DBA"."PRODUCTS"
(
"ID" integer NOT NULL,
"NAME" varchar(200) NOT NULL,
"PRICE" money NOT NULL,
"PRODUCT_NUMBER" varchar(50) NOT NULL,
"QUANTITY_IN_STOCK" integer NOT NULL,
"REMARKS" text NULL,
PRIMARY KEY ("ID")
)
CREATE TABLE "DBA"."OPPORTUNITIES"
(
"ID" integer NOT NULL,
"CUSTOMER_ID" integer NOT NULL,
"PRODUCT_ID" integer NOT NULL,
"CONTACT_DATE" datetime NOT NULL,
"REMARKS" text NULL,
PRIMARY KEY ("ID")
)
CREATE TABLE "DBA"."SALES"
(
"ID" integer NOT NULL,
"CUSTOMER_ID" integer NOT NULL,
"PRODUCT_ID" integer NOT NULL,
"QUANTITY" integer NOT NULL,
"TOTAL_PRICE" money NULL,
"REMARKS" text NULL,
PRIMARY KEY ("ID")
)
As a final step, we need to prepare our database for "real-world" (or something approximating the real world) usage by creating a user account in the database. This, too, can be accomplished via the Sybase Central interface. For starters, I created a user named "salesperson" with a password of "sales" in our starter database. This user can log into the database (via Sybase's included dbisql query tool or via our own app) using this username/password combination.