UniSQL

[Believer]

Hopefully an easy question...

How do I write the following code for VB.NET in a way that it will work on multiple platforms? I've studied the documentation and online forums but I keep missing something. I use these examples because the data types between MS SQL and Oracle are different. I want to code universal SQL that will work with both, as well as other platforms we may use in the future.

1. Create a table called tblAccount with these fields:
RecID: integer
AccountNo: text up to 255 characters
OpenDate: date only
LastViewed: date and time (hh:mm:ss)
LastUpdate: date and time, time to include fractions of a second

2. Insert a record with these values:
RecID: 1
AccountNo: "1234A"
OpenDate: 10/21/2008
LastViewed: 10/27/2008 18:27:32
LastUpdate: 10/25/2008 13:22:15.456

I've tried various combinations of code, but all of them had shortcomings so I decided not to put any of it here.

Thanks!

[AndreyR]

Unfortunately, your question is out of Devart Support's scope.

[Believer]

I apologize, maybe I should have been more clear about the aim of my question. You see, I'm looking for confirmation from Devart that your UniDirect will truly meet the needs of a large project I've been assigned to which specifically requires multiplatform support. I find the documentation on UniDirect's universal SQL syntax to be very sparse. Also, I thought I had read other posts in the forum where the support staff has specifically offered to help with forming the SQL statements for UniDirect.

The goal of my original question was to know whether or not I can code a universal SQL statement for creating fields of various types. For example, what "universal" syntax would I use to define a field of varchar(50)? I know in MSSQL it would simply be varchar(50), but according to the documentation I found regarding Oracle, they recommend defining the field as varchar2(50). Would UniDirect automatically take this into account when executing the Create Table on an Oracle platform?

I added the date fields in for the same purpose: to find out whether UniDirect's universal SQL could translate different sizes of date fields for different platforms. If so, then what data types do I use for each of them?

Thank you in advance for your time.

[AndreyR]

I have created two tables, one in MS SQL Server (script

Code: Select all

create table tblAccount(RecId int, AccountNo varchar(255), OpenDate datetime, LastViewed datetime, LastUpdate datetime);

), second in Oracle (script

Code: Select all

create table tblAccount(RecID int, AccountNo varchar2(255),
OpenDate date, LastViewed timestamp, LastUpdate timestamp)

).
After that I created UniCommand

Code: Select all

      this.uniCommand1.CommandText = "insert into tblAccount values(:RecId, :AccountNo, :OpenDate, :LastViewed, :LastUpdate)";
      this.uniCommand1.Connection = this.uniConnection1;
      this.uniCommand1.Name = "uniCommand1";
      this.uniCommand1.Parameters.Add(new CoreLab.UniDirect.UniParameter("RecId", CoreLab.UniDirect.UniDbType.Int, 0, System.Data.ParameterDirection.Input, false, 0, 0, "", System.Data.DataRowVersion.Current, "1"));
      this.uniCommand1.Parameters.Add(new CoreLab.UniDirect.UniParameter("AccountNo", CoreLab.UniDirect.UniDbType.VarChar, 0, System.Data.ParameterDirection.Input, false, 0, 0, "", System.Data.DataRowVersion.Current, "aaa"));
      this.uniCommand1.Parameters.Add(new CoreLab.UniDirect.UniParameter("OpenDate", CoreLab.UniDirect.UniDbType.DateTime, 0, System.Data.ParameterDirection.Input, false, 0, 0, "", System.Data.DataRowVersion.Current, "1/1/1"));
      this.uniCommand1.Parameters.Add(new CoreLab.UniDirect.UniParameter("LastViewed", CoreLab.UniDirect.UniDbType.DateTime, 0, System.Data.ParameterDirection.Input, false, 0, 0, "", System.Data.DataRowVersion.Current, "1/1/1 1:1:1"));
      this.uniCommand1.Parameters.Add(new CoreLab.UniDirect.UniParameter("LastUpdate", CoreLab.UniDirect.UniDbType.TimeStamp, 0, System.Data.ParameterDirection.Input, false, 0, 0, "", System.Data.DataRowVersion.Current, "5/11/2008 16:20:00.11111"));

and executed it twice - using UniConnection1 to MS SQL Server and using UniConnection2 to Oracle.
Everything succeeded.

[Believer]

Thanks for the code examples! I have been studying what you wrote and it appears to answer all the questions I had about how to accomplish the universal data typing. I appreciate the time you put into it... thanks again!