[Openroad-users] Creating Variables Dynamically

88 views
Skip to first unread message

barathi

unread,
Jan 26, 2009, 12:52:57 AM1/26/09
to International OpenROAD Users

Hi,

     I am dynamically creating the insert script with values for the tables in MS SQL database and writing in to a text file. Name of the table and the column is select for the system table, so the number of column may vary from table to table.

I was able to build the Select script successfully to get the values of the table but need to find a way of storing the values in to a variable for building the insert script.

 

Something like,

                EXECUTE IMMEDIATE :Lv_query INTO :Lv_Select_values;

 

where Lv_Select_values will be the list of array build dynamically.

 

Is there a way to store the selected values In to an array  for later use. The values will depend based on the table selected so will have multiple column and rows.

I will be greatful if anyone can help.

 

 

Thanks,

 Barathy

          

Neil Warnock

unread,
Jan 26, 2009, 2:16:13 AM1/26/09
to International OpenROAD Users

Hi Barathi,

If you have a lot of these queries to write then it might be simpler and cheaper to licence the gateway!

Alternatively if you only have a handful of these queries could ADO/ODBC be a simpler solution? See UKIUA presentation "integration secrets revealed" for tips on this.

Rgds

Neil Warnock

Sent from my BlackBerry® wireless device

-----Original Message-----
From: "barathi" <bar...@myworkplace.com.au>

Date: Mon, 26 Jan 2009 11:22:57
To: 'International OpenROAD Users'<openroa...@peerlessit.com>
Subject: [Openroad-users] Creating Variables Dynamically


________________________________________________________________
OpenROAD-Users mailing list

You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users

To unsubscribe click on this link
mailto:openroad-user...@peerlessit.com&subject=unsubscribe

To subscribe click on this link
mailto:openroad-use...@peerlessit.com&subject=subscribe

________________________________________________________________
OpenROAD-Users mailing list

You can maintain your subscription here:
http://www.peerlessit.com/mailman/listinfo/openroad-users

To unsubscribe click on this link
mailto:openroad-user...@peerlessit.com&subject=unsubscribe

To subscribe click on this link
mailto:openroad-use...@peerlessit.com&subject=subscribe

Bodo Bergmann

unread,
Jan 26, 2009, 3:18:16 AM1/26/09
to International OpenROAD Users
As the count of result variables is uniknown until runtime I think you will have to use an SQLSelect object to do it,
so you can use a result array  which matches your query.
 
So, you would need to create one userclass per table or - alternatively -
you could retrieve all your data as varchar (if this is possible - don't know if you are using BLOBs)
and store them in an array of a UserClass that has an array of StringObject as "rowdata" attribute.
 
Bodo.
 

Bodo Bergmann | Sr. Software Engineer | Bodo.B...@ingres.com | Ingres Germany GmbH | Ohmstr. 12 | 63225 Langen | GERMANY | +49 6103 9881 0


From: openroad-us...@peerlessit.com [mailto:openroad-us...@peerlessit.com] On Behalf Of barathi
Sent: Monday, January 26, 2009 6:53 AM
To: 'International OpenROAD Users'

Subject: [Openroad-users] Creating Variables Dynamically

Tom Robinson

unread,
Jan 26, 2009, 3:26:05 AM1/26/09
to International OpenROAD Users
Hi Barathi,

Am I right in understanding that you are getting the data from Ingres
and putting it into MSSQL and you don't use the Enterprise Access
Gateway to MSSQL?

You could use the SQL COPY statement to write the data to a temp file,
e.g.

INITIALIZE()=
DECLARE
l_v_string = VARCHAR(2000) NOT NULL WITH DEFAULT,
ENDDECLARE
BEGIN
l_v_string = 'COPY mytable '
+ '( '
+ ' myint = C0COMMA, '
+ ' myvarchar = C0, '
+ ' NL = D0NL '
+ ') '
+ 'INTO ''c:\\temp\\mytable.csv'''

EXECUTE IMMEDIATE l_v_string;
END

(Build the COPY statement in a similar way to how you've built your SQL
statement)

You could then use a system call to MSSQL bcp command to bulk copy the
data in - probably have some file format issues. Or you could pull the
data back into OR using a STRINGOBJECT to read the file and then iterate
through it.

Hth,

Tom

Tom Robinson
Luminary - An Ingres Company
Tel: +44 (0) 845 371 4090
Email: Tom.Ro...@luminary.co.uk

For more information on Luminary go to http://www.luminary.co.uk
Luminary Solutions Limited Registered in England No 4854134 VAT Reg No.
829 3166 13
Registered Office: Lacon House, Theobald's Road, London, WC1X 8RW


________________________________

From: openroad-us...@peerlessit.com
[mailto:openroad-us...@peerlessit.com] On Behalf Of barathi
Sent: 26 January 2009 05:53
To: 'International OpenROAD Users'
Subject: [Openroad-users] Creating Variables Dynamically

Hi,

Something like,

Thanks,

Barathy


Pete Rabjohns

unread,
Jan 26, 2009, 5:26:16 AM1/26/09
to International OpenROAD Users
Does this do what you need?

<Code Below>

PROCEDURE pr_dump_select(
Query = StringObject,
TableName = VARCHAR (32) NOT NULL,
FileName = VARCHAR (255) NOT NULL,
filemode = VARCHAR (1) NOT NULL DEFAULT 'O',
clearall = INTEGER1 NOT NULL DEFAULT TRUE,
KeyCols = VARCHAR (400) NOT NULL,
UseTempTbl = INTEGER1 NOT NULL DEFAULT TRUE,
CreateTbl = INTEGER1 NOT NULL DEFAULT FALSE,
IgnoreDel = SMALLINT NOT NULL DEFAULT FALSE;
DBType = SMALLINT NOT NULL DEFAULT 1;
)=
DECLARE
i = INTEGER NOT NULL,
j = INTEGER NOT NULL,
flg = SMALLINT NOT NULL,
found = SMALLINT NOT NULL,
SS = SQLselect,
errortext = VARCHAR (2000) NOT NULL,
SQL = StringObject;
h_cols = StringObject;
strobj = StringObject;
h_keys = ARRAY OF STRINGOBJECT;
ValObj = OBJECT DEFAULT NULL;
RowCount = INTEGER NOT NULL;
h_data_type = VARCHAR (64) NOT NULL;
tblName = VARCHAR (64) NOT NULL;
token_count = INTEGER NOT NULL;
h_print_stmt = VARCHAR (20) NOT NULL;
h_eob = VARCHAR (10) NOT NULL;

h_GO = VarChar(2) NOT NULL DEFAULT 'GO';
h_PRINT = VarChar(10) NOT NULL DEFAULT 'PRINT';
h_tmpstr = VarChar(50) NOT NULL;
ReplaceStr = PROCEDURE;
ENDDECLARE
BEGIN
IF DBType = 2 /*ORACLE*/
THEN
h_GO = GV_EMPTY_STRING;
h_PRINT = 'PROMPT ';
ENDIF;

sql.Value = '';
IF filemode = 'O' THEN
sql.WriteToFile (filename = filename);
ENDIF;

h_tmpstr = ' Loading table ' + TableName;

sql.ConcatVarchar (text = HC_NEWLINE);
sql.ConcatVarchar (text = h_PRINT + +HC_QUOTE + h_tmpstr +
HC_QUOTE + HC_NEWLINE +
h_GO +
HC_NEWLINE);
sql.ConcatVarchar (text = HC_NEWLINE);

IF DBType = 1 THEN
sql.ConcatVarchar (text = 'SET NOCOUNT ON' + HC_NEWLINE
+
h_GO +
HC_NEWLINE);
sql.ConcatVarchar (text = HC_NEWLINE);
ELSEIF DBType = 2 THEN
sql.ConcatVarchar (text = 'SET FEED ON' + HC_NEWLINE);
sql.ConcatVarchar (text = h_GO + HC_NEWLINE);
ENDIF;

SS.Scope = CurProcedure.Scope;

SS.Query = Query;
IF SS.Query.value = '' then
MESSAGE 'No query was specified.';
RETURN 0;
ENDIF;

SS.QueryName = 'query_dump';

SS.SetCols();
IF SS.ErrorNo != ER_OK THEN
IF SS.IsDBerror != TRUE THEN
INQUIRE_SQL (errortext = errortext);
MESSAGE 'Unexpected error encountered while
parsing the query. ' +
'ErrorNo = ' +
VARCHAR(SS.ErrorNo) + hc_newline + errortext;
RETURN 0;
ENDIF;
ENDIF;

If CreateTbl = TRUE THEN
tblName = TableName;
useTempTbl = FALSE;
ELSE
tblName = 'temp_mps';
ENDIF;

IF UseTempTbl = TRUE OR CreateTbl = TRUE THEN
IF DBType = 1 THEN
sql.ConcatVarchar (text = 'IF EXISTS (SELECT *
FROM sysobjects WHERE id = object_id('+HC_QUOTE + tblName + HC_QUOTE +
') AND sysstat & 0xf = 3)' + HC_NEWLINE);
sql.ConcatVarchar (text = HC_TAB + 'DROP TABLE
'+tblName+ ';' + HC_NEWLINE );
sql.ConcatVarchar (text = h_GO + HC_NEWLINE +
HC_NEWLINE);
ELSEIF DBType = 2 THEN
sql.ConcatVarchar (text = 'DECLARE CC NUMBER' +
HC_NEWLINE);
sql.ConcatVarchar (text = 'BEGIN' + HC_NEWLINE);
sql.ConcatVarchar (text = HC_TAB + 'SELECT
COUNT(*) INTO CC FROM user_tables WHERE table_name = UPPER(' + HC_QUOTE
+ tblName + HC_QUOTE + ');' + HC_NEWLINE );
sql.ConcatVarchar (text = 'IF CC>0 THEN' +
HC_NEWLINE);
sql.ConcatVarchar (text = HC_TAB + 'DROP TABLE '
+tblName + ';' + HC_NEWLINE);
sql.ConcatVarchar (text = 'END IF;' + HC_NEWLINE
+ 'END;' + HC_NEWLINE + '/' + HC_NEWLINE);
ENDIF;

sql.ConcatVarchar (text = 'CREATE TABLE '+tblName+' (' +
HC_NEWLINE);
FOR i = 1 to SS.Columns.LastRow DO
IF SS.Columns[i].DataTypeCode = 20 THEN
h_data_Type = 'VARCHAR' + '(' +
c(SS.Columns[i].DataTypeLength) + ')';
ELSEIF SS.Columns[i].DataTypeCode = 21 THEN
h_data_type = 'VARCHAR2' + '(' +
c(SS.Columns[i].DataTypeLength) + ')';
ELSEIF SS.Columns[i].DataTypeCode = 30 THEN
IF DBType = 1 THEN
h_data_type = 'INTEGER';
ELSEIF DBType = 2 THEN
h_data_type = 'NUMBER';
ENDIF;
ELSE
CurProcedure.Trace (SS.Columns[i].AsName
+ ' typecode = '+c(SS.Columns[i].DataTypeCode) + ', length =
'+c(SS.Columns[i].DataTypeLength));
ENDIF;
h_data_type = h_data_type + ' NULL';
sql.ConcatVarchar (text = HC_TAB +
SS.Columns[i].AsName + ' ' + h_data_type);
IF i < SS.Columns.LastRow THEN
sql.ConcatVarchar (text = ',' +
HC_NEWLINE);
ENDIF;
ENDFOR;
sql.ConcatVarchar (text = ' );' + HC_NEWLINE +
h_GO +
HC_NEWLINE);
sql.ConcatVarchar (text = HC_NEWLINE);
ENDIF;

sql.AppendToFile (filename = Filename);
sql.Value = '';

SS.Scope = CurProcedure.Scope;

SS.Open(QueryMode = QY_CURSOR);
RowCount = 0;

IF clearall = TRUE AND IgnoreDel = FALSE AND UseTempTbl = FALSE
THEN
sql.ConcatVarchar (text = 'DELETE FROM ' + TableName +
';' + HC_NEWLINE +
h_GO +
HC_NEWLINE +

HC_NEWLINE);
ENDIF;

WHILE SS.NextRow() = ER_OK DO
SS.Load();
sql.ConcatVarchar (text = 'INSERT INTO '+tblName + ' (');

FOR i = 1 to SS.Columns.LastRow DO
IF i > 1 THEN
sql.ConcatVarchar (text = ',');
ENDIF;
sql.ConcatVarchar (text = SS.Columns[i].AsName);
ENDFOR;
sql.ConcatVarchar (text = ') VALUES (');
FOR i = 1 to SS.Columns.LastRow DO
IF i > 1 THEN
sql.ConcatVarchar (text = ',');
ENDIF;
sql.ConcatVarchar (text =
SS.Columns[i].ColumnName);
ValObj = SS.Columns[i].Value;

IF ValObj IS NULL THEN
sql.ConcatVarchar (text = 'NULL');
ELSEIF ValObj.IsA (class = STRINGOBJECT) = TRUE
THEN
strobj = STRINGOBJECT(ValObj);
CallProc ReplaceStr (string = BYREF
(strobj), match = HC_QUOTE, replacestr = HC_QUOTE+HC_QUOTE);
CallProc ReplaceStr (string = BYREF
(strobj), match = X'0D'+X'0A', replacestr =
HC_QUOTE+'+CHAR(10)+'+HC_QUOTE);
CallProc ReplaceStr (string = BYREF
(strobj), match = X'0A', replacestr = HC_QUOTE+'+CHAR(10)+'+HC_QUOTE);
CallProc ReplaceStr (string = BYREF
(strobj), match = X'0D', replacestr = HC_QUOTE+'+CHAR(13)+'+HC_QUOTE);
CallProc ReplaceStr (string = BYREF
(strobj), match = HC_NEWLINE, replacestr =
HC_QUOTE+'+CHAR(10)+'+HC_QUOTE);

sql.ConcatVarchar (text = HC_QUOTE);
sql.ConcatString (string = strobj);
sql.ConcatVarchar (text = HC_QUOTE);

/*CALLPROC pr_StrReplace(pi_string =
BYREF(STRINGOBJECT(ValObj).Value), pi_searchstr = HC_NEWLINE,
pi_replacewith = CHAR(10));*/
ELSEIF ValObj.IsA (class = INTEGEROBJECT) = TRUE
then
sql.ConcatVarchar (text = VARCHAR
(INTEGEROBJECT (ValObj).Value));
ELSEIF ValObj.IsA (class = DATEOBJECT)= TRUE
then
sql.ConcatVarchar (text = HC_QUOTE +
VARCHAR (DATEOBJECT (ValObj).Value) + HC_QUOTE);
ELSEIF ValObj.IsA (class = MONEYOBJECT)= TRUE
then
sql.ConcatVarchar (text = VARCHAR
(MONEYOBJECT (ValObj).Value));
ELSEIF ValObj.IsA (class = FLOATOBJECT)= TRUE
then
sql.ConcatVarchar (text = VARCHAR
(FLOATOBJECT (ValObj).Value));
ENDIF;
ENDFOR;
sql.ConcatVarchar (text = ');' + HC_NEWLINE);
IF sql.Length > 60000 THEN
sql.ConcatVarchar (text = h_GO + HC_NEWLINE +

HC_NEWLINE);
sql.AppendToFile (filename = Filename);
sql.Value = '';
ENDIF;
RowCount = RowCount + 1;
ENDWHILE;
sql.ConcatVarchar (text = h_GO + HC_NEWLINE +
HC_NEWLINE);
sql.AppendToFile (filename = Filename);
sql.Value = '';
SS.Close();
commit;

FOR i = 1 to SS.Columns.LastRow DO
h_cols.ConcatVarchar (text = HC_TAB +
SS.Columns[i].AsName);
IF i < SS.Columns.LastRow THEN
h_cols.ConcatVarchar(text = ',' + HC_NEWLINE);
ENDIF;
ENDFOR;

h_keys.Clear();
WHILE KeyCols <> '' DO
i = LOCATE(KeyCols, ',');
h_keys[h_keys.LastRow + 1].Value = LEFT(KeyCols, i - 1);
KeyCols = SHIFT(KeyCols, 0 - i);
ENDWHILE;

IF clearall = FALSE AND UseTempTbl = TRUE THEN
sql.ConcatVarchar (text = 'UPDATE' + HC_TAB + TableName
+ HC_NEWLINE);
sql.ConcatVarchar (text = 'SET');
flg = FALSE;
FOR i = 1 TO SS.Columns.LastRow DO
found = FALSE;
FOR j = 1 TO h_keys.LastRow DO
IF h_keys[j].Value =
SS.Columns[i].AsName THEN
found = TRUE;
ENDLOOP;
ENDIF;
ENDFOR;
IF found = FALSE THEN
IF flg = TRUE THEN
sql.ConcatVarchar (text = ',' +
HC_NEWLINE);
ENDIF;
sql.ConcatVarchar (text = HC_TAB +
SS.Columns[i].AsName + ' = temp_mps.' + SS.Columns[i].AsName);
flg = TRUE;
ENDIF;
ENDFOR;
sql.ConcatVarchar (text = HC_NEWLINE);
sql.ConcatVarchar (text = 'FROM' + HC_TAB + 'temp_mps' +
HC_NEWLINE);
sql.ConcatVarchar (text = 'WHERE');
FOR i = 1 TO h_keys.LastRow() DO
sql.ConcatVarchar (text = HC_TAB + TableName +
'.' + h_keys[i].Value + ' = temp_mps.' + h_keys[i].Value + HC_NEWLINE);
IF i < h_keys.LastRow() THEN
sql.ConcatVarchar (text = 'AND ' +
HC_TAB);
ENDIF;
ENDFOR;
sql.ConcatVarchar (text = h_GO + HC_NEWLINE);
sql.ConcatVarchar (text = HC_NEWLINE);
ENDIF;

IF UseTempTbl = TRUE THEN
sql.ConcatVarchar (text = 'INSERT INTO ' + TableName + '
(' + HC_NEWLINE);
sql.ConcatString (string = h_cols);
sql.ConcatVarchar (text = ') ' + HC_NEWLINE +

'SELECT');
sql.ConcatString (string = h_cols);
sql.ConcatVarchar (text = HC_NEWLINE);
sql.ConcatVarchar (text = 'FROM temp_mps; ' +
HC_NEWLINE);
IF clearall = FALSE THEN
sql.ConcatVarchar (text = 'WHERE NOT EXISTS (' +
HC_NEWLINE +
HC_TAB + 'SELECT * FROM ' + TableName +
HC_NEWLINE +
HC_TAB + 'WHERE ');
FOR i = 1 TO h_keys.LastRow() DO
sql.ConcatVarchar (text = TableName +
'.' + h_keys[i].Value + ' = temp_mps.' + h_keys[i].Value);
IF i < h_keys.LastRow() THEN
sql.ConcatVarchar (text =
HC_NEWLINE + HC_TAB + 'AND ');
ENDIF;
ENDFOR;
sql.ConcatVarchar (text = ');' + HC_NEWLINE);
ENDIF;
ENDIF;
sql.ConcatVarchar (text = h_GO + HC_NEWLINE +
HC_NEWLINE);

IF CreateTbl = TRUE
THEN
sql.ConcatVarchar(text = 'GRANT ALL ON ' +
TableName + ' TO PUBLIC' + ';' + HC_NEWLINE);
sql.ConcatVarchar(text = h_GO
+ HC_NEWLINE + HC_NEWLINE);
ENDIF;

IF UseTempTbl = TRUE THEN
sql.ConcatVarchar (text = 'DROP TABLE temp_mps;' +
HC_NEWLINE);
sql.ConcatVarchar (text = h_GO + HC_NEWLINE +

HC_NEWLINE);
ENDIF;

IF DBType = 1 THEN
sql.ConcatVarchar (text = 'SET NOCOUNT
OFF'+HC_NEWLINE+h_GO+HC_NEWLINE);
sql.AppendToFile (filename = Filename);
sql.Value = '';
ELSEIF DBType = 2 THEN
sql.ConcatVarchar (text = 'SET FEED ON' + HC_NEWLINE);
sql.AppendToFile (filename = Filename);
sql.Value = '';
ENDIF;

RETURN RowCount;
END;


PROCEDURE ReplaceStr (
string = STRINGOBJECT DEFAULT NULL,
match = VARCHAR (200) NOT NULL,
replacestr = VARCHAR (200) NOT NULL
) =
DECLARE
i = INTEGER NOT NULL;
string1 = STRINGOBJECT DEFAULT NULL;
ENDDECLARE
BEGIN
i = string.LocateString(match = match, startposition = 1);
WHILE i > 0 DO
string1 = string.ExtractString (startposition = 1,
length = i-1);
string.LeftTruncate (endposition = i+LENGTH(match)-1);
string1.ConcatVarchar (text = replacestr);
string = string1.ConcatString(string = string);
i = string.LocateString(match = match, startposition =
i+LENGTH(replacestr));
ENDWHILE;
END;

Tom Robinson

unread,
Jan 26, 2009, 6:56:52 AM1/26/09
to barathi, International OpenROAD Users
Pete, Bodo and Neil have given you other ideas so I'll stick with my
previous suggestion. You'd have to do it the other way around:

1. CALL SYSTEM
bcp_command_to_copy_data_out_to_flat_file_in_a_nice_format
2. Use an SQL COPY ... FROM command to copy the data into the target
table.

If you can't import directly into the target table then import into a
session temporary table and then insert from there.

I've never done this before with MSSQL although I have done similar
stuff to move data between different Ingres servers from within OpenROAD
and I don't think that it would be too hard.

The advantage of this over most other methods would be that it is
bulk/set based rather than row-at-a-time - so it should be faster.

The difficult bit would be making sure that the BCP output and the
Ingres COPY could work to the same format of file which might be a bit
tricky if this is to be generic across different tables. However, BCP
allows you to extract the results of a query and has pretty good control
over the formatting if I recall correctly.

If you try it this way then I'd be interested in knowing if you get it
working.

Cheers,

Tom

Tom Robinson
Luminary - An Ingres Company
Tel: +44 (0) 845 371 4090
Email: Tom.Ro...@luminary.co.uk

For more information on Luminary go to http://www.luminary.co.uk
Luminary Solutions Limited Registered in England No 4854134 VAT Reg No.
829 3166 13
Registered Office: Lacon House, Theobald's Road, London, WC1X 8RW


-----Original Message-----
From: barathi [mailto:bar...@myworkplace.com.au]
Sent: 26 January 2009 10:51
To: Tom Robinson
Subject: RE: [Openroad-users] Creating Variables Dynamically

Hi Tom,
I am actually trying to get the data from MS SQL database and
write in to a text file as an insert script.
Suppose table ta_emp has 2 columns(emp_no,emp_name) with 2 records. Then
the text file should have

Insert into ta_emp(emp_no, emp_name)values(1,'xxxx') Insert into
ta_emp(emp_no, emp_name)values(2,'xxxx')

Since I am selecting the entire table of the database so can't say the
number of columns each table will have. So in that Case the variable
have to be created dynamically.

EXECUTE IMMEDIATE :Lv_query INTO :Lv_Select_values;

So it has to fill in to some 2 dimensional array where the number of
column and the row will vary


Has to be like

Thanks
Barathy

Pete Rabjohns

unread,
Jan 26, 2009, 11:04:12 AM1/26/09
to Pete Rabjohns, International OpenROAD Users, barathi
not sure if the last attempt got through...

-----Original Message-----
From: Pete Rabjohns
Sent: 26 January 2009 12:18
To: 'International OpenROAD Users'; barathi
Subject: RE: [Openroad-users] Creating Variables Dynamically

In the words of Blue Peter... "Here is one I created earlier". The frame
is used as a utlity for transferring all sorts of stuff, so you may get
some DB errors if you select the wrong option. However, there is an
option to transfer data, and I seem to recall that it worked, although
it was a long time ago. It builds the COPY statement dynaically based
upon the table name and then forms the correct BCP statement to copy the
data back into a SQL Server database. I have not checked it for it's
reliance on anything else in the library, so if you are missing any
components that you can't work out how to replace (or can't just remove
the offending code to get it to do the bits you want) then let me know.

Sorry, no doco, comments - the code is very much 'as-is', at it was ony
ever used for one of my own little projects to copy data from am Ingres
DB into SQL Server, and I seem to recall there may hev been issues with
it. I think you may also need a corresponding utility that trims the
trailing spaces from all VARCHAR fields once the data base been loaded.

HTH

Pete

sqlscripts.zip
Reply all
Reply to author
Forward
0 new messages