July 04, 2022, 02:09:36 AM

News:

IonicWind Snippit Manager 2.xx Released!  Install it on a memory stick and take it with you!  With or without IWBasic!


Creating a database and inserting a table.

Started by Rock Ridge Farm (Larry), May 09, 2006, 07:11:30 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Rock Ridge Farm (Larry)

I am having a problem. I can create a database but then when I try to build a table it fails.
I have tried many combinations of syntax I have found on the web - all give the same error.
Syntax error in create table.
The latest line of code is:
         hstmt = stock_db.ExecSQL("CREATE TABLE 'stock' ('id' counter(1,1),'symbol' varchar(10)," +
            "'status' varchar(40),'lstrec' varchar(40),'shares' int(10),'name' varchar(40)," +
            "'buydt' varchar(40),'bprc' float(9,2),'bcomm' float(9,2),'selldt' varchar(15)," +
            "'sprc' float(9,2),'scomm' float(9,2),'dtadd' varchar(16),'lstprc' float(9,2)," +
            "'pftlos' float(9,2),'posval' float(9,2),'cstbas' float(9,2),'tick' varchar(5)" +
            "PRIMARY KEY ('symbol'), UNIQUE KEY ('name')");
Anyone got an idea?

PS - I have tried it as one line, no ' around the field names and just a one field insert.
I am sure it is some simple mistake but I do not see it.
The same line of code works in 4GL.

Ionic Wind Support Team

See addressbook.src

         hStmt = m_db.ExecSQL("CREATE TABLE addresses (id counter(1,1),fname varchar(40),lname varchar(40),street varchar(255),street2 varchar(255) )");


Also the limit of a static text string in Aurora (enclosed in quotes) is 1024 characters...you may have exceeded that. If so use a string variable and concat it.

Don't look to other languages, look to Microsoft and MSDN since Aurora uses ODBC.
Ionic Wind Support Team

Ionic Wind Support Team

I guess I should ask what database driver your trying to use too ;)
Ionic Wind Support Team

Rock Ridge Farm (Larry)

The length is 414 chars.
If I remove the int and float vars it works.
Does Aurora only support varchar and counter?

Rock Ridge Farm (Larry)


Ionic Wind Support Team

Aurora simply passes the SQL string to ODBC.  Beyond that support is determined by the ODBC layer.

Anyway if I remember correctly for datatypes it would be 'integer' and not int, 'real' and not float.

I don't think you can specify a field width with integer and real types.  Since they are stored in binary form with Access databases.  What you are probably looking for are the NUMERIC and DECIMAL types.

Here is the microsoft reference on SQL data types:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp
Ionic Wind Support Team

Rock Ridge Farm (Larry)

integer works OK.
real, decimal, currency - all fail.
I am reading the reference to see if I can find out why.

Rock Ridge Farm (Larry)

As per the documentation this should work:
         hstmt = stock_db.ExecSQL("CREATE TABLE stock (id counter(1,1),symbol varchar(10)," +
            "status varchar(40),lstrec varchar(40),shares integer,name varchar(40)," +
            "buydt varchar(40),bprc decimal(9,2),bcomm decimal(9,2),selldt varchar(15))");
It does not unless I remove the decimal reference.
This also does not work:
         hstmt = stock_db.ExecSQL("CREATE TABLE stock (id counter(1,1),symbol varchar(10)," +
            "status varchar(40),lstrec varchar(40),shares integer,name varchar(40)," +
            "buydt varchar(40),bprc decimal,bcomm decimal,selldt varchar(15))");
What am I doing wrong?

Ionic Wind Support Team

The syntax is generalized for all databases.  Not all database drivers will support every datatype though.   Try type 'double precision' without any fields.

From the microsoft docs:

Quote
Important   The tables throughout this appendix are only guidelines and show commonly used names, ranges, and limits of SQL data types. A given data source might support only some of the listed data types, and the characteristics of the supported data types can differ from those listed.

It is kind of odd that a driver doesn't support the 'real' type.  But I am not all that familiar with an 'mdm' database.
Ionic Wind Support Team

Rock Ridge Farm (Larry)

In the database I used to test with the fields are defined as 'currency'.
It will not take that as a data type in the create statement.

Ionic Wind Support Team

define them as character and see what happens.  ODBC should convert it back and forth for you.
Ionic Wind Support Team

Rock Ridge Farm (Larry)

I actually got it to work - I used currency for all decimal entries and it worked.
Thanks for the help.

Ionic Wind Support Team

Well I am glad we could stumble through it together ;)
Ionic Wind Support Team

Vikki

Hi all!

I know I'm jumping in late but I just thought I'd add the following information to this thread just in case it might be helpful to someone else.

A lot of db's have different data types and you have to find out which data types go with which db. Not sure why Mr Bill didn't create the same data types for Access as for SQL but....

Access data types:

QuoteText
Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. Stores up to 255 characters. The FieldSize property controls the maximum number of characters that can be entered.

Memo
Use for lengthy text and numbers, such as notes or descriptions. Stores up to 63,999 characters.

Number
Use for data to be included in mathematical calculations, except calculations involving money (use Currency type). Stores 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The FieldSize property defines the specific Number type.

Date/Time
Use for dates and times. Stores 8 bytes.

Currency
Use for currency values and to prevent rounding off during calculations. Stores 8 bytes.

AutoNumber
Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. Stores 4 bytes; stores 16 bytes for Replication ID (GUID).

Yes/No
Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) values are not allowed. Stores 1 bit.

OLE Object
Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) protocol.
Stores up to 1 gigabyte (limited by disk space).

Hyperlink
Use for hyperlinks (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.). A hyperlink can be a UNC path (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) or a URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.).
Stores up to 2048 characters.

There is one more data type but it calls an Access wizard which I won't work I suppose unless you are building in Access. It's name is Lookup.

SQL Data Types....

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp

VikkiÂÃ,  :)

Bruce Peaslee

Quote from: Vikki on June 15, 2006, 06:02:29 PM

I know I'm jumping in late but I just thought I'd add the following information to this thread just in case it might be helpful to someone else.


It's not too late. I need the information now and here it is! Thanks.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles