SQL Anywhere database - ADO DataTypeEnum value for TIMESTAMP

Author

ADO DataTypeEnum value for TIMESTAMP

Anonymous

2005-07-14, 8:23 pm

Does anyone know what ADO DataTypeEnum value corresponds to
TIMESTAMP and/or DATETIME in ASA 9? Is it adDBTimeStamp?
Better yet would be a table that maps all ASA data types to
the correct DataTypeEnum value for ADO

Karel van der Walt

2005-07-15, 7:23 am

Best is to ask the dbms ?

I assume you are OK with WSH/jscript if you do ADO.

Here is a script that creates prepared statements for the tables you supply.

The connection parms are for MSSQL.
====================
====================
===
// createpreparedStatem
ents.js

var serverTarget = "theserver";

var catalogTarget = "thecatalog";

var conTarget;

conTarget = WScript.CreateObject("ADODB.Connection");
conTarget.Provider="SQLOLEDB.1" ;
conTarget.ConnectionString = "Data Source = "+ serverTarget +";" ;
conTarget.ConnectionString += "Initial Catalog = "+ catalogTarget +";" ;
conTarget.ConnectionString += "Integrated Security = SSPI;" ;
//conTarget.ConnectionString += "uid=sa; Password =;" ;
conTarget.ConnectionTimeout = 15; //default=15
conTarget.CommandTimeout = 40; //default=30
try {
conTarget.Open();
} catch (ex) {
WScript.Echo(ex.description);
conTarget = null;
WScript.Quit(1);
}

var catTarget;

try {
catTarget = WScript.CreateObject("ADOX.Catalog");

} catch (ex) {
WScript.Echo(ex.description);
catTarget = null;
WScript.Quit(1);
}

catTarget.ActiveConnection = conTarget;

var tables = new Array();

tables.push("DescriptiveProject");
//push more if you need....

var iSql = '"INSERT "+\r\n'+
'"INTO [#Table#] ([#Column#] ) "+\r\n'+
'"VALUES (#Value# ) ;";';

var sSql = '"SELECT [#Column#] "+\r\n'+
'"FROM [#Table#] AS #Alias# "+\r\n'+
'"WHERE [#Clause#] ;";';

var sAppendParm = 'Append( CreateParameter("#Column#", #Datatype#,
ParameterDirectionEn
um.adParamInput, #Size#));';

var fso = WScript.CreateObject("Scripting.FileSystemObject");
var file = fso.CreateTextFile(". \\preparedStatements
.js", true);

file.WriteLine('var '+catalogTarget+' = new Array();') ;

for (var i in tables) {

var sTable = tables[i]

file.WriteBlankLines(1) ;
file. WriteLine(catalogTar
get+'["'+sTable+'"] =
WScript.CreateObject("ADODB.Command");');
file.WriteBlankLines(1) ;

file.WriteLine('with ('+catalogTarget+'.'+sTable+') {') ;
file.WriteLine(' ActiveConnection = conTarget;') ;
file.WriteLine(' CommandType = CommandTypeEnum["adCmdText"];') ;
file.WriteLine(' Prepared = true;') ;
file.WriteBlankLines(1) ;

with (catTarget.Tables( sTable )) {

var is = iSql;
is = is.replace(/#Table#/g, sTable );

file.WriteBlankLines(1) ;
file.WriteLine(' with (Parameters) { //Parameters must be added in
positional order' ) ;

for (var e = new Enumerator(Columns);
!e.atEnd();e.moveNext()) {
col = e.item();

is = is.replace(/\[#Column#\]/g, "["+col.Name+'], [#Column#]');
is = is.replace(/#Value#/g, "?, #Value#");

var ps = sAppendParm;
ps = ps.replace(/#Column#/g, col.Name);
ps = ps.replace(/#Datatype#/g, col.Type);

ps = ps.replace(/, #Size#/g, (col.DefinedSize==0)?'':',
'+col.DefinedSize);
file.WriteLine( '\t\t'+ps ) ;

//WScript.Echo(col.Name +" : "+ col.Type +" size: "+
col.DefinedSize);

}

is = is.replace(/, \[#Column#\]/g, '');
is = is.replace(/, #Value#/g, '');

} //with

file.WriteLine(' }') ;
file. WriteLine('\tCommand
Text = '+is) ;
file.WriteLine('} ') ;

} //for

file.WriteBlankLines(1) ;
file.Close();

===============
hth
k

Anonymous

2005-07-15, 7:23 am

Actually, I'm using C++. I was hoping there was a table
somewhere that lists the ADO type that corresponds to each
Sybase ASA 9 data type, but really all I need is the one for
DATETIME or TIMESTAMP.

> Best is to ask the dbms ?
>
> I assume you are OK with WSH/jscript if you do ADO.
>
> Here is a script that creates prepared statements for the
> tables you supply.
>
> The connection parms are for MSSQL.
> ====================
====================
===
> // createpreparedStatem
ents.js
>
> var serverTarget = "theserver";
>
>
> var catalogTarget = "thecatalog";
>
>
> var conTarget;
>
> conTarget = WScript.CreateObject("ADODB.Connection");
> conTarget.Provider="SQLOLEDB.1" ;
> conTarget.ConnectionString = "Data Source = "+
> serverTarget +";" ; conTarget.ConnectionString += "Initial
> Catalog = "+ catalogTarget +";" ;
> conTarget.ConnectionString += "Integrated Security = SSPI
> ;" ; //conTarget.ConnectionString += "uid=sa; Password =;"
> ; conTarget.ConnectionTimeout = 15; //default=15
> conTarget.CommandTimeout = 40; //default=30
> try {
> conTarget.Open();
> } catch (ex) {
> WScript.Echo(ex.description);
> conTarget = null;
> WScript.Quit(1);
> }
>
> var catTarget;
>
> try {
> catTarget = WScript.CreateObject("ADOX.Catalog");
>
> } catch (ex) {
> WScript.Echo(ex.description);
> catTarget = null;
> WScript.Quit(1);
> }
>
> catTarget.ActiveConnection = conTarget;
>
>
> var tables = new Array();
>
> tables.push("DescriptiveProject");
> //push more if you need....
>
>
>
> var iSql = '"INSERT "+\r\n'+
> '"INTO [#Table#] ([#Column#] ) "+\r\n'+
> '"VALUES (#Value# ) ;";';
>
> var sSql = '"SELECT [#Column#] "+\r\n'+
> '"FROM [#Table#] AS #Alias# "+\r\n'+
> '"WHERE [#Clause#] ;";';
>
> var sAppendParm = 'Append( CreateParameter("#Column#",
> #Datatype#, ParameterDirectionEn
um.adParamInput, #Size#))
> ;';
>
>
> var fso =
> WScript.CreateObject("Scripting.FileSystemObject"); var
> file = fso.CreateTextFile(". \\preparedStatements
.js",
> true);
>
>
> file.WriteLine('var '+catalogTarget+' = new Array();') ;
>
> for (var i in tables) {
>
> var sTable = tables[i]
>
> file.WriteBlankLines(1) ;
> file. WriteLine(catalogTar
get+'["'+sTable+'"] =
> WScript.CreateObject("ADODB.Command");');
> file.WriteBlankLines(1) ;
>
> file.WriteLine('with ('+catalogTarget+'.'+sTable+') {') ;
> file.WriteLine(' ActiveConnection = conTarget;') ;
> file.WriteLine(' CommandType =
> CommandTypeEnum["adCmdText"];') ;
> file.WriteLine(' Prepared = true;') ;
> file.WriteBlankLines(1) ;
>
> with (catTarget.Tables( sTable )) {
>
> var is = iSql;
> is = is.replace(/#Table#/g, sTable );
>
>
> file.WriteBlankLines(1) ;
> file.WriteLine(' with (Parameters) { //Parameters must
> be added in positional order' ) ;
>
> for (var e = new Enumerator(Columns);
!e.atEnd()
> ;e.moveNext()) {
> col = e.item();
>
> is = is.replace(/\[#Column#\]/g, "["+col.Name+'],
> [#Column#]');
> is = is.replace(/#Value#/g, "?, #Value#");
>
> var ps = sAppendParm;
> ps = ps.replace(/#Column#/g, col.Name);
> ps = ps.replace(/#Datatype#/g, col.Type);
>
> ps = ps.replace(/, #Size#/g,
> (col.DefinedSize==0)?'':', '+col.DefinedSize);
> file.WriteLine( '\t\t'+ps ) ;
>
> //WScript.Echo(col.Name +" : "+ col.Type +"
> size: "+ col.DefinedSize);
>
> }
>
> is = is.replace(/, \[#Column#\]/g, '');
> is = is.replace(/, #Value#/g, '');
>
> } //with
>
>
> file.WriteLine(' }') ;
> file. WriteLine('\tCommand
Text = '+is) ;
> file.WriteLine('} ') ;
>
>
> } //for
>
> file.WriteBlankLines(1) ;
> file.Close();
>
> ===============
> hth
> k
>
>

Sponsored Links