UniSQL

UniSQL

Postby Believer » Fri 31 Oct 2008 15:11

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!
Believer
 
Posts: 4
Joined: Tue 28 Oct 2008 12:38

Postby AndreyR » Mon 03 Nov 2008 15:28

Unfortunately, your question is out of Devart Support's scope.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Believer » Mon 03 Nov 2008 20:02

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.
Believer
 
Posts: 4
Joined: Tue 28 Oct 2008 12:38

Postby AndreyR » Wed 05 Nov 2008 14:38

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Believer » Fri 07 Nov 2008 14:04

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!
Believer
 
Posts: 4
Joined: Tue 28 Oct 2008 12:38


Return to dotConnect Universal