DTO - Setting column data types 1

[zemp]

I have been trying to set the datatype for a column to 'tinyint' without success. So far I have been able to set it as far as 'smallint'. Below is the DTO code for adding the column.

Set Column = New DtoColumn
With Column
.Decimal = 0
.Flags = dtoColumnDefault
.ISR = ""
.Name = "field4"
.Number = 3
.Size = 2 '2 = smallint, 4 = integer
.Type = dtoTypeInteger
End With
Table.Columns.Add Column

If I change the .size value to 4 I get an integer. However setting the .size value to 1 or zero will give me 'unknown' in the data type column.

Can I make the data type tinyint via DTO and if so how can it be done, what values do I need to change?

One more thing, is it possible to set a default field value for a column? If so how?
Thanks and Good Luck!

zemp

 

[mirtheil]

If I set the the .Size to 1, it creates a UTinyInt for me. What version of Pervasive are you using? I'm using Pervasive.SQL V8 SP1. There's no way to create a default value using DTO. info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[zemp]

I am using Pervasive 2000i SP4. I was also able to create a 'utinyint' data type, but not a 'tinyint'. What is the difference and do I need to worry about it? Thanks and Good Luck!

zemp

 

[mirtheil]

The difference is that a Utinyint is unsigned and a tinyint is signed. Does it make a difference? That depends on what you're storing in the table. Just out of curiosity, why use DTO? Why not use Create Table statements?
info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[zemp]

I am storing integer values from 0 to 20 in my tinyint data type. Some are flags and some are FK values.

I am using DTO because that is what has been recommended to me. I am open to better suggestions. Especially if I can set up the tables better (with default values). Thanks and Good Luck!

zemp

 

[mirtheil]

WHat interface is your application using to store the data? Is it ODBC/OLEDB? If so, issue Create Table SQL statements. You can set the default value on the Create Table statement. I would also suggest psql V8 rather than 2000i SP4 mainly because the ODBC and OLEDB are better in V8. info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[zemp]

Well I finally got it. I am posting this hoping it will help someone else as well. Here is what I did.

1. Created a database using the DTO objects. Make sure a reference to Pervasive Distributed Tuning Library 1.0 is made.

Dim result As dtoResult
Dim m_dtoDatabase As New DtoDatabase
Dim m_dtoTable As New DtoTable

With m_dtoDatabase
.DataPath = Trim(txtLocation.Text)
.DdfPath = Trim(txtLocation.Text)
.Name = txtName.Text
.Flags = dtoDbFlagCreateDDF
End With

result = m_dtoSession.Databases.add(m_dtoDatabase)

If result = Dto_Success Then
MsgBox "Database added correctly"
Command2.Enabled = False
Else
MsgBox "Error adding database. Error Code: " & result
End If
Set m_dtoDatabase = Nothing

2. Created an ODBC system DSN link to the database, also with the DTO.

Dim lResult As dtoResult
' we're already logged in. we can just start creating the DSN
Dim mDtoDSN As New DtoDSN

With mDtoDSN
.DBName = txtName.Text
.Description = "test"
.Name = "MyDSN"
.OpenMode = 0
End With
lResult = m_dtoSession.DSNs.add(mDtoDSN)
If lResult <> Dto_Success Then
MsgBox &quot;Error adding DSN. Error code: &quot; & CStr(lResult), vbCritical, &quot;Error&quot;
Else
MsgBox &quot;DSN Added.&quot;, vbInformation, &quot;DSN added.&quot;
End If

3. Created and ADO connection using the newly created DSN and added tables and indexes via Create SQL statements. This allowed me to use all available data types and add defaults values to any fields that needed them. The SQL statements needed for the entire database were stored in a resource file and called with the for loop.

Dim conn As ADODB.Connection
Dim i As Integer

Set conn = New ADODB.Connection
With conn
.ConnectionString = &quot;DSN=MyDSN&quot;
.CursorLocation = adUseServer
.Open
End With

For i = 1 To 17
conn.Execute LoadResString(i)
Next l_intCount
conn.Close
Set conn = Nothing

Here is an example of the SQL statement to create a Customer table and an index to ensure that the Customer Number remains unique and cannot be changed once in the database.

CREATE TABLE Customer(CustomerID IDENTITY DEFAULT '0' NOT NULL,CustomerNumber VARCHAR(20) NOT NULL,cuShortName VARCHAR(20),cuFullName VARCHAR(60),cuAddress1 VARCHAR(60),cuAddress2 VARCHAR(60),cuCity VARCHAR(30),cuProvState VARCHAR(30),cuPCZip VARCHAR(20),cuCountry VARCHAR(30),cuPhone VARCHAR(30),cuFax VARCHAR(30),cuContact VARCHAR(60),cuEmail1 VARCHAR(50),cuEmail2 VARCHAR(50),cuGroupCode VARCHAR(15),cuAccountSet VARCHAR(15),cuPriceList VARCHAR(15),cuTermsCode VARCHAR(15),cuTaxGroup VARCHAR(15),cuCustType TINYINT DEFAULT '0' NOT NULL,Exported TINYINT DEFAULT '0' NOT NULL)

CREATE UNIQUE NOT MODIFIABLE INDEX CN_CustomerNumber ON Customer(CustomerNumber ASC)

Thanks to mirtheil for all your help and advice. Thanks and Good Luck!

zemp